Analysis :: DAX For Distinct Count Of Related Column Without Returning Out Of Memory Error?
Aug 10, 2015
I am doing a distinct count on a related table's column, but get an out of memory error if I run it for the entire table (works great for just a few rows when filtered down).The error I get is: "We couldn't get data from the external source.The operation has been cancelled because there is not enough memory available for the application. If using 32-bit version of the product consider upgrading.
I know I can add a related column and that works fine...but that seems to me like I've defeated the purpose, I have a good and proper lookup table, and should be able to run my query against its relationship.Here is the query and details below *Note I supplied a scaled down sample, on my actual model I receive these errors, not in the sample as it has only a few rows
List Workers Distinct Project Customers:=CALCULATE(DISTINCTCOUNT(Projects[CustomerID]),'WorkersToProjects')
Other measure which returns no errors, but included for completeness:
List Workers Projects:=CALCULATE(DISTINCTCOUNT([ProjectID]),ISBLANK(WorkersToProjects[ProjectID])=FALSE())
My goal here is to allow the user to view the workers assigned to a project, but also get counts of the workers assigned to the CUSTOMER of a project. For example, suppose we lose a customer, we want to see how many workers would be impacted by that, so a count of projects per worker is not useful there, we need to see a count of workers per project's customer (owner of project whom project work is being done for)The question being: How can I accomplish this:
1. WITHOUT adding a calculated column to WorkersToProjects (of Projects.CustomerID)
2. WITH better performance?
There must be a better way to write this DAX to still get the correct answer?*Pic of pivot table, again, the numbers are accurate but the formula used to List Workers Distinct Project Customers measure does NOT scale :( 3 count for red , the number of Projects John has and 2 count for blue, the unique customers/owners of those projects "Veridian Dynamics" and "Massive Dynamic". URL....
i am currently trying to build a distinct count on my cube (mssql2005 analysis services).But after i added the discount count on the field i want to and start the processing, the following errors appear.Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect.
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
Background: I have a Huge fact dimension table(table has both measures and dimensions) that i am using to build a SSAS Cube.
The table didn't have a unique identifier, so the database team added ROW NUM as a column to the table which i am using as a PrimaryKey in my Cube build. I was able to create a cube successfully with it without any issues.
Problem: Now customers are asking for a 'Claim Count calculation' which shows the Distinct Claim Count.
Its defined as below :
Count(Distinct Claim_Number || Claim Year || Claim Month)
. All the 3 columns are available in table, but when i am trying to create this Count Distinct Object in the DSV the Cube processing time increased by 5 times, as now i have to use a GroupBy function in my SQL.(There are around 30 columns to group by).
Is there a better way to achieve this Count(Distinct Claim_Number || Claim Year || Claim Month) without using groupBy in DSV SQL logic? I cant seem to find any Count(Distinct) function in the Cube Calculation functions?
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1 Year2 ..... MDX what I have now MDX what I need
I have a table of users and date when they logged on to a system. I am trying to count how many distinct users logged on for each day of the week. The SQL below works when there's at least a user for each day. But when there is no user for a particular day such as Sunday, I still want it to return "SUN
0 "
I learned that you can use GROUP BY ALL and it works but the "ALL" is deprecated beyond SQL 2005.
------------------------------------ SELECT UPPER(LEFT(DATENAME(dw, StartTime), 3)) AS DayOfWeek, COUNT(DISTINCT UserID) AS NumberOfUser
FROM testUserLoginDuration WHERE Archived = 0 GROUP BY UPPER(LEFT(DATENAME(dw, StartTime), 3)) ORDER BY CASE WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'MON' THEN 1 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'TUE' THEN 2 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'WED' THEN 3 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'THU' THEN 4 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'FRI' THEN 5 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SAT' THEN 6 WHEN UPPER(LEFT(DATENAME(dw, StartTime), 3)) = 'SUN' THEN 7 END
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green pen 2 0 1 marker 0 1 0 pencil 1 3 0 highlighter 1 1 1
We tried Sum but not able to display in single row.
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
hello, i have a working stored procedure SELECT CommentID, UserName, PictureID, DateCommented, COUNT(CommentID) OVER (PARTITION BY PictureID) AS 'NrOfComments' FROM Comments WHERE PictureID = @PictureID witch returns among others the number of comments for a picture i need to select the number of distinct users who commented that user too, so i added this at SELECT statement, COUNT(DISTINCT UserName) AS 'Expr1' i get that error: "Colum 'Comments.CommentID' is invalig in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause." what should i do, to select the number of distinct users who commented on a specific picture? here are the table rows, if you need that CommentID --- UserName --- PictureID --- DateCommented please help me, thank you
We have a table of students with fields for the ID of the school district and their school name. For example:
DistrictID StudentName SchoolName
10001 John Smith Washington Elementary
10001 Jane Smith Lincoln Middle
10002 David White Hill High
... ... ...
I want is a listing with the school district ID, the student name, and the number of schools in the district.
I believe the following statement should give me what I want:
SELECT DistrictID, StudentName, COUNT(DISTINCT SchoolName) OVER (PARTITION BY DistrictID)
FROM StudentData
However, I get the error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'distinct'.
If I take out the DISTINCT it runs fine, however it counts all the rows as expected, which is not what I want. If I remove the OVER clause it runs fine, however, it counds the schools across all the districts, which is not what I want. I can remove the OVER clause and use GROUP BY, but not while also retrieving StudentName.
The SQL 2005 Online Books don't seem to indicate that my statement is invalid. Here are the links just for reference: Count: http://msdn2.microsoft.com/en-us/library/ms175997.aspx Over: http://msdn2.microsoft.com/en-us/library/ms189461.aspx
Anyone have an idea one what I'm doing wrong or how to go about getting this information? Thanks! Matt Penner
I have a SSIS package that reads data from a dump table, runs a custom script that takes date data and converts it to the correct format or nulls and formats amt fields to currency, then inserts it to a new table. The new table redirects insert errors. This process worked fine until about 3 weeks ago. I am processing just under 6 million rows, with 460,000 or so insert errors that did give error column and code.
Now, I am getting 1.5 million errors. and nothing has changed, to my knowledge. I receive the following information.
Error Code -1071607685 Error Column 0 Error Desc No status is available.
The only thing I can find for the above error code is
DTS_E_OLEDBDESTINATIONADAPTERSTATIC_UNAVAILABLE
To add to the confusion, I can not see any errors in the data written to the error table. It appears that after a certain point is reached in the processing, everything, or most records, error out.
I have two tables. In tblUsers is all userdata. In table tblMoreInfo is some info on a user (0-15 records).Now I want to select tblUsers.username and tblUsers.usercode and the matching top 1 row tblMoreInfo.schoolname (top 1 based on tblMoreInfo.createdate) IF IT EVENT EXISTS from tblMoreInfo. If no matching record exists I want the value NULL to be returned for the schoolname...In all cases tblUsers.username must be unique (and I cant use the distinct keyword as im already using the row_number() function.Any help?Thanks!
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
Plz help me out making a SELECT for my imagegallery. Table one: [albums] contains the fields [albumId], [albumTitle] and [albumDesc] - the name says it all. Table two: [images] contains the fields [imageId], [imagePath] and forign key [albumId} In my albumspage i would like to show all albums listed in [albums] (the easy part) AND for every album i would like to count how many images there's related to the particular album AND a random retrieve a random [imagePath] To extract the albums something like this works: SELECT * FROM [albums] ORDER BY [albumTitle] To count the images in a sertain album this works: SELECT COUNT(*) AS imagesCount WHERE [albumId] = ...the albumId from the album-select And to retrieve a random imagePath this works fine: SELECT TOP 1 [imagePath] FROM [images] WHERE [imageId] = NEWID() -But how do i combine them so that i can retrieve the data in one request? Suppose theres going to be some JOIN and group but i cant figure it out. Please help me out.
Hi, I don't use SQL much but I need to know how to form the following query against a SQL table. I have a single table with two columns - CurrentPage and LastPage.
I need to write SQL to perform a number of iterations of a particular insert into another table, depending upon the count from CurrentPage to LastPage i've put the following Pseudocode together, but need help implementing this.
Declare counter Set counter = currentpage If counter > lastpage Goto task exit loop Else perform task - (insert string into table) Increment counter and loop
I've been playing / reading up, but i'm getting nowhere fast -
DECLARE @Count int DECLARE @Current string DECLARE @Last string
SET @Current= dbSL.tblPaging_colCurrentPage SET @Count= dbSL.tblPaging._colPagingCount SET @Last= dbSL.tblPaging._colLastPage WHILE @Count <= dbSL.tblPaging._colLastPage
BEGIN PRINT 'Hello World' SET @Count = @Count +1 END
Hi,I was wondering if it was possible to build a query that will include acolumn that will provide a count related records from another table.Although there is a way to achieve this through programming in thefront end, I would like to know if it possible to achieve the samething through a SQL statement alone.For example, say you have two related tables, Invoices andInvoiceItems. InvoiceID is the primary key of Invoices.Invoices tableInvoiceID PO_Num CompanyID-------------------------------1 37989 32 87302 43 78942 3InvoiceItems tableItemID InvoiceID PartNo Qty---------------------------------------1 1 ABA 32 1 ASLKDJ 23 1 9LF 84 2 IEPOW 185 2 EIWPD 36 2 DSSIO 17 2 EIWP 58 2 DC93 49 3 85LS0 8Then a query that has the Invoices table plus a count of InvoiceItemsfor each InvoiceID would generate this:InvoiceID PO_Num CompanyID ItemCount-------------------------------------------------1 37989 3 32 87302 4 53 78942 3 1Does anyone have any ideas how this would be done?Thank you.
Hi I was not able to connect to SQL Server machine. On examining the Error log (which was huge 53MB), I found the following messages that filled 95% of the logfile. Is this something to do with memory allocation.
Someone, please let me know what is going on. After the server reboot everything works fine. I am worrired that this message may occur again.
Thanks Machilu
2004-11-30 20:15:03.64 logon Login failed for user 'NT AUTHORITYSYSTEM'
2004-12-01 08:15:03.77 logon Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
2004-12-01 00:47:25.28 spid70 WARNING: Failed to reserve contiguous memory of Size= 65536. 2004-12-01 00:47:25.31 spid70 Buffer Distribution: Stolen=127590 Free=4176 Procedures=182443 Inram=0 Dirty=14180 Kept=0 I/O=0, Latched=154, Other=10049 2004-12-01 00:47:25.31 spid70 Buffer Counts: Commited=338592 Target=338592 Hashed=24383 InternalReservation=357 ExternalReservation=0 Min Free=256 2004-12-01 00:47:25.31 spid70 Procedure Cache: TotalProcs=66212 TotalPages=182443 InUsePages=88547 2004-12-01 00:47:25.31 spid70 Dynamic Memory Manager: Stolen=310033 OS Reserved=38512 OS Committed=38457 OS In Use=38388 Query Plan=332158 Optimizer=0 General=15540 Utilities=8 Connection=473 2004-12-01 00:47:25.31 spid70 Global Memory Objects: Resource=10685 Locks=119 SQLCache=4540 Replication=2 LockBytes=2 ServerGlobal=45 Xact=201 2004-12-01 00:47:25.31 spid70 Query Memory Manager: Grants=0 Waiting=0 Maximum=92118 Available=92118 2004-12-01 00:50:04.10 logon Login failed for user 'NT AUTHORITYSYSTEM'. 2004-12-01 00:50:04.32 logon Login failed for user 'NT AUTHORITYSYSTEM'. 2004-12-01 00:51:08.78 spid70 WARNING: Failed to reserve contiguous memory of Size= 65536. 2004-12-01 00:51:08.82 spid70 Buffer Distribution: Stolen=138829 Free=5944 Procedures=169283 Inram=0 Dirty=14431 Kept=0 I/O=0, Latched=154, Other=9951 2004-12-01 00:51:08.82 spid70 Buffer Counts: Commited=338592 Target=338592 Hashed=24536 InternalReservation=360 ExternalReservation=0 Min Free=256 2004-12-01 00:51:08.82 spid70 Procedure Cache: TotalProcs=67783 TotalPages=169283 InUsePages=76116 2004-12-01 00:51:08.82 spid70 Dynamic Memory Manager: Stolen=308112 OS Reserved=38512 OS Committed=38457 OS In Use=38398 Query Plan=330249 Optimizer=0 General=15535 Utilities=8 Connection=476 2004-12-01 00:51:08.82 spid70 Global Memory Objects: Resource=10685 Locks=118 SQLCache=4540 Replication=2 LockBytes=2 ServerGlobal=45 Xact=202
One of my users is supposed to have full access to all other dimesions except for one and thus I have defined a customized role on one of 4 dimensions that I have.
When I test the role (from the front-end), I am getting a strange message in my front-end and could it be that I also have to define a role for this user on all other dimensions? Should I define the role on the server or the client??? As you can guess, I am a bit confused and will appreciate your help.
One of my users is supposed to have full access to all other dimesions except for one and thus I have defined a customized role on one of 4 dimensions that I have.
When I test the role (from the front-end), I am getting a strange message in my front-end :confused: and could it be that I also have to define a role for this user on all other dimensions? Should I define the role on the server or the client :confused: I am a bit confused and will appreciate your help.
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We cant really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didnt work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any ideas, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
I'm having trouble with cube processing. While processing a code I'm getting a "Invalid column name MessageType" error.
I unfolded the cube, then I opened "measure groups", my failing dimension (ServiceRequestDim) and the partition.
In the partition I opened the "Source" attribute so it now includes my column which was missing. But it didn't solve the issue.
If I get the query used during the process I'm getting this :
SELECT DISTINCT [ServiceRequestDim].[MessageType] AS [ServiceRequestDimMessageType0_0] FROM ( Select IsNull(IsDeleted, 0) as IsDeleted, [ServiceRequestDimKey], IsNull([Status_ServiceRequestStatusId], 0) as [Status_ServiceRequestStatusId],[Status],IsNull([TemplateId_ServiceRequestTemplateId], 0) as
[Code] ....
In the nested query which defines ServiceRequestDim the messagetype attribute is still missing. In my source datamart the ServiceRequestDim has the "MessageType" column.
So the question is where do I change the nested request that the dim process use to reflect the actual columns in my datamart .
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
i'm getting following exception when i try to execute stored procedure.
{"Ambiguous column name 'MemberID'. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }
I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting "Transaction count after EXECUTE indicates that......"
In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN
/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */
/* I have some logic here that will select rows into temporary table #TEMPRESULT */
UPDATE dbo.RebateInstanceItem SET ResubmitCreated = @dtmNewCreated FROM #TEMPRESULT tTempResult WHERE MemberID = tTempResult.MemberID AND RebateInstanceItem.IsResubmit = 'Y' AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated OR RebateInstanceItem.ResubmitCreated IS NULL)
IF @@ERROR<>0 GOTO ERR_HANDLER // when error it will goto error_handler that will rollback
DROP TABLE #TEMPRESULT IF @@ERROR<>0 GOTO ERR_HANDLER
sometimes, when tables are poorly joined, we have exactly same rows as a result.. so we use distinct.. but how can we count(*) the distinct rows?
select distinct d.docrefid from tbljobDocuments d left join tbljobitems j on j.docrefid=d.docrefid where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700'
will return me
docrefid DC01C06027
but when i try to count(*) only the distinct record, it count all..
select distinct d.docrefid, count(*) from tbljobDocuments d left join tbljobitems j on j.docrefid=d.docrefid where d.docrefid='DC01C06027' and j.itemid='39J1667-H86700' group by d.docrefid
select a.patientid, a.providerid from PatientDrugList a JOIN Admin..Loadinstance b on a.intLoadInstanceId = b.intLoadInstanceId where a.MailReasonTypeId = -1 and b.SubClientId = 22 and b.StatusTypeId > 0
Now how could I get distinct patientid count and distinct providerid count
I have a table which stores the shift information for employees. The table contains 10 columns as Employeename,Employeeno,month,year,shifttimings etc. If an employee works a day in a particular shift, then a row will be inserted in to the above table for that employee.
Now at the end of the month i wanted to calculate the shift details for each employee for a particular month of a given year like employeename,employeeno, noofdays(countof shiftdays).
How to right choose key column in"Mining Structure" for Microsoft Analysis Services?
I have table:
"Incoming goods"
Create table Income ( ID int not null identity(1, 1) [Date] datetime not null, GoodID int not null, PriceDeliver decimal(18, 2) not null, PriceSalse decimal(18, 2) not null, CONSTRAINT PK_ Income PRIMARY KEY CLUSTERED (ID), CONSTRAINT FK_IncomeGood foreign key (GoodID) references dbo.Goods ( ID ) )
I'm trying to build a relationship(regression) between “Price Sale” from Good and “Price Deliver”.But I do not know what column better choose as “key column”: ID or GoodID ?
We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
