November 11, 2005 at 4:54 am
Hello there.
I've got a simple question again.
But i tried a lot of ways to solve it, no chance. First the MDX-Expression:
WITH
MEMBER [Datum].[Range] AS
'sum([Datum].[2005-01-01]:[Datum].[2005-01-01])'
SELECT
PID_FID.children.count on 0
FROM IBIcube_LEP_AKZ1
WHERE ([Datum].[Range], [Stations_Gruppe].[M Chirurgie], [Geschlecht].[männlich])
I get the error message: "cannot convert expression to set"
Whats my problem?
tried also:
- "count({PID_FID.children})"
- used "with member", but there i got binding errors
Thanks for any help.
tobi
November 11, 2005 at 4:56 am
Sorry, Subject should be named as:
Count members of dimension
November 15, 2005 at 6:28 am
Hi Tobias,
Are you looking for a distinct count of the members within the dimension, when taking into account the filters that you've specified?
If so, you might want to try the NECJ (nonemptycrossjoin) approach to getting a distinct count. Basically you create a member that is the NECJ of the set(s) and then do a count of it's members using the .Count property.
Rather than writing it all out again, here's a link to a post on the msft newsgroups by Deepak that outlines the approach.
Cheers,
Steve.
November 16, 2005 at 1:46 am
Hi Steve,
with this query i get for each patientID (PID_FID - all members 60.000 entries) one column. The count on this columns with the above set filters return the correct value for this query. Why can't i solve this via a simple count?
I tried it with NECJ, but the request-time was for only one day about 30 seconds. Later i want to query ranges.
Greetings, Tobi
November 16, 2005 at 2:25 am
Hi again.
This query returns the same values like in the first thread(time is good, below 1 sec):
With
MEMBER [Datum].[Range] AS
'sum([Datum].[2005-01-01]:[Datum].[2005-01-01])'
SELECT
non empty nonemptycrossjoin([PID_FID].[PID_FID].members) on 0
FROM IBIcube_LEP_AKZ1
WHERE ([Datum].[Range], [Geschlecht].[weiblich], [Stations_Gruppe].[M Chirurgie], [Fremdstationaer].[Alle Fremdstationaer].[False], [Intervall].)
The NECJ returns also PID_FID with NULL, so i put a non empty in front of that. How do I count this result set. Table looks like this:
¦...111...¦...112...¦...113...¦...113...¦ <--- PID_FID
¦....64...¦.....8...¦....66...¦....24...¦ <--- counted rows
Correct Value would be 4. What do you think?
Greets, Tobi
November 16, 2005 at 3:53 pm
Hi Tobi,
Can you tell us how you'd like to see the output? ie <this> on row, <that> on columns, filtered by <maybe_this>.
Also, why do you need to add the range for Datum, can't you use the single member within the where clause?
Steve.
November 17, 2005 at 2:25 am
Hello again.
>> Can you tell us how you'd like to see the output?
>> ie on row, on columns, filtered by .
Okay. Now you get the actual status. First the query:
With
MEMBER[IBIdim_LEP_Date].[Range] AS
'sum([IBIdim_LEP_Date].[2005-01-01]:[IBIdim_LEP_Date].[2005-01-03])'
SELECT
{[IBIdim_LEP_sex].[female]} on 0,
non empty ({nonemptycrossjoin([IBIdim_LEP_PatientID].children)}) on 1
FROM IBIcube_LEP_AKZ1_sex
WHERE ([IBIdim_LEP_Date].[Range], [IBIdim_LEP_Stations_Grup].[M Chirurgie], [IBIdim_LEP_strIDSchicht].)
I just select the on rows all patients on columns which fit the WHERE-clause.
The result set is:
[PatientID]_________[female]
1058098.2640029___1
1104165.2632884___1
1104838.2637978___2---<> Also, why do you need to add the range for Datum,
>> can't you use the single member within the where clause?
Later we need special ranges for that project.
i.e. [IBIdim_LEP_Date].[2004-09-25]:[IBIdim_LEP_Date].[2005-01-03]
Perhapes I offer only monthly calculations and put it in the WHERE-Clause.
Thanks for your help.
Best wishes, tobi
November 17, 2005 at 3:07 am
Another Information:
I cannot even count the Members
count({[IBIdim_LEP_PatientID].members}) on 0
---->--- "cannot convert expression to set"
Must be a understanding problem on my side...
November 18, 2005 at 9:51 am
One step closer...
The COUNT-function works nearly fine now. The problem now is, the EXCLUDEEMPTY-flag excludes no PatientID, because the PatientID itselfs is not NULL -> empty PatientID-values should be excluded. With the NON EMPTY-function it worked fine, but i cant use it in the COUNT-function.
With
MEMBER [Date].[Range] AS
'sum([Datum].[2005-01-01]:[Datum].[2005-01-01])'
MEMBER [Measures].[COUNT1] AS
'COUNT({nonemptycrossjoin({[PatientID].children})}, excludeempty)'
SELECT
{([Gender].children), ([Measures].[COUNT1])} on 0
FROM IBIcube_LEP_AKZ1
WHERE ([Date].[Range], [Gender].[female], [Stations_Gruppe].[M Chirurgie], [Fremdstationaer].[Alle Fremdstationaer].[False], [Intervall].)
Where's the problem now?
Now I wish a nice weekend and thanks in advance for every answer.
Tobi
November 20, 2005 at 4:35 pm
Hey Tobi,
I think the problem lies in the NECJ, it's not actually crossjoining anything. If you CJ this to a measure, like the default measure that was used in your earlier example (that gave counts of events on a station), you should get the result you want.
So, modifying your last query a little:
With
MEMBER [Date].[Range] AS 'sum([Datum].[2005-01-01]:[Datum].[2005-01-01])'
MEMBER [Measures].[Distinct_Patient_Count] AS '({nonemptycrossjoin({[PatientID].children}, {[Measures].[Some_Count_Measure]})}, excludeempty).Count'
SELECT
{[Gender].children} on 0,
{[Measures].[Distinct_Patient_Count]} ON 1
FROM IBIcube_LEP_AKZ1
WHERE ([Date].[Range], [Gender].[female], [Stations_Gruppe].[M Chirurgie], [Fremdstationaer].[Alle Fremdstationaer].[False], [Intervall].)
Steve.
November 21, 2005 at 3:47 am
GREAT !!!
Now it works. Had to do some little changes. Here the corrected version.
WITH
MEMBER [Date].[Range] AS 'sum([Date].[2005-01-01]:[Date].[2005-01-03])'
MEMBER [Measures].[Distinct_Patient_Count] AS 'count({nonemptycrossjoin({[PatientID].children}, {[Measures].[Count]})}, excludeempty)'
SELECT
{[Gender].children} on 0,
{[Measures].[Distinct_Patient_Count]} ON 1
FROM IBIcube_LEP_AKZ1
WHERE ([Date].[Range],[Stations_Gruppe].[M Chirurgie], [Fremdstationaer].[Alle Fremdstationaer].[False], [Intervall].)
The returned values are correct, but the query-time convinces me not that much. Is there any other way to get this value and decrease the query-time?
Thanks for your help! Think now i understand the crossjoin-function.
At the weekend i bought the book "fast track to mdx". Hope to get more expirience by reading that.
Tobi
November 21, 2005 at 7:10 pm
One of the authers of fast track (Mosha) works on the design of AS and MDX especially within AS. I think Rob Zarre is a co-author who works for MS in the AS space also. Another good book (aout to go to a revised, second edition) is MDX solutions by Spofford (and now also Chris Webb, ex MSFT).
Steve.
November 22, 2005 at 3:50 am
Hi again.
I implemented the query in different variations to some StoredProcedures and increased the query-time from 30 seconds (OLTP) to 2 seconds (OLAP). 🙂
In another query I have to do a distinct count on three dimensions...
The query is:
WITH
MEMBER [Date].[Range] AS 'sum([Date].[2003-12-24]:[Date].[2003-12-24])'
MEMBER [Measures].[Distinct_Patient_Count] AS 'count({nonemptycrossjoin({[Measures].[Count]}, {generate({[Date].members}, {[PatientID].members})})}, excludeempty)'
SELECT
{[Measures].[Distinct_Patient_Count]} ON 0
FROM IBIcube_LEP_AKZ1
WHERE ([Date].[Range], [Stations_Gruppe].[M Chirurgie], [Fremdstationaer].[Alle Fremdstationaer].[False], [Intervall].[*])
Now i have a query-time about 1 minute and 30 seconds.
Is there any way to get a faster output?
Two NECJ are slow, too. And deliver wrong values.
Some Ideas?
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply