-
incorrect result:help with MDX syntax
I have a SSAS cube with JOBS and PERSON as dimensions and a factless fact table COUNT_CUBE. 2 measures have been added to the cube using wizard as follows-
measure name=[ErrorCount Jobs] ;Usage= count of non-empty values ;Source table =JOBS; soiurce coulumn=Error Id
measure name=[ErrorCount Person] ;Usage= count of non-empty values ;Source table =PERSON; soiurce column=Error Id
Also each dimnsion PERSON and JOB has below attributes:-
[data rule name] which specifies the data rule on the dimension.
[Error Id] specifies the error id generated whenever the data rule fails .The same data rule can fail multiple times for a single employee or multiple employees.
[Empluyee Id]-The emolyee id for which the data rules are failing
I needed to join 2 dimesions and find the number of times a particular dat rule is failing
I have tried the below query:-
WITH MEMBER
[Measures].[Name Of failed Data rule] AS (
IIf
([Jobs].[Data Rule Name].currentmember IS [Jobs].[Data Rule Name].defaultmember,
"Person: " + [Person].[Data Rule Name].currentmember.name,
"Jobs: " + [Jobs].[Data Rule Name].currentmember.name
))
MEMBER [Measures].[Error Frequency] AS (
IIf
([Jobs].[Data Rule Name].currentmember IS [Jobs].[Data Rule Name].defaultmember,
[Measures].[ErrorCount Jobs]
,[Measures].[ErrorCount Person]
))
SELECT
{[Measures].[Name Of failed Data rule] ,[Measures].[Error Frequency] } on 0,
{
{
nonempty((except([Jobs].[Data Rule Name].members ,{[Jobs].[Data Rule Name].[all],[Jobs].[Data Rule Name].&}) * [Person].[Data Rule Name].defaultmember))},
{
nonempty([Jobs].[Data Rule Name].defaultmember * (Except([Person].[Data Rule Name].members,{[Person].[Data Rule Name].[all],[Person].[Data Rule Name].&})))}
}
ON 1
FROM [pp audit]
output
Was able to get it working partialy as in terms of my desired format...but the no of times a data rule fails ie [Measures].[Error Frequency] in a dimesion is shown incorrectly as sum of all datarules in that particular dimension and that too interchanges with other dimension.
Actual output:
Name Of failed Data rule Error Freq
Jobs:datarule1 3
Person:datarule2 200
Person:datarule3 200
200 actually is the error freq of Jobs:datarule1 and is the only datraule in JOBS dimnsion
3 is total error freq in PERSON dimnsion ,datarule2 fails 2 times and datarule3 fails 1 time.So 2+1 =3 times.
Result I belivw due to the cross hoin concept interchanges the values .
Expected output:
Name Of failed Data rule Error Freq
Jobs:datarule1 200
Personatrule 2 2
Personatrule 3 1
Plz help .
Thanks
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|