I am facing one problem for cubes. Eventhough I have NULL for certain measures in cube it gets populated as 0 in Cube. Has anyone faced this kind of problem. How can I solve this
eg In Database
D1 D2 Measure1 Measure2
A B NULL 10
C D 10 NULL
Expected in Cube
D1 D2 Measure1 Measure2
A B 10
C D 10
Currently getting
D1 D2 Measure1 Measure2
A B 0 10
C D 10 0
If anyone can provide some tips on this issue it would be of great help to me.
Any tip will be greatly appreciated
Thanks,
This is the default behaviour of Analysis Services, however in Analysis Services 2005 you can set a measure's NullProcessing property (select the measure in the Cube Editor and then expand the Source property in the properties pane) to Preserve.
HTH,
Chris
|||Hi Chris,
Thanks for reply. If I am not mistaken it means its not possible in AS2000. Can you suggest any workaround in AS2000 if you have come across any. It would be of great help
Regards,
SP
|||I don't think there is a workaround. Why do you need these null values?
Chris
|||Hi Chris,
I am using a tool for viewing cube which can hide row or column if it is entirely null
e.g Time
T1 T2 T3
Group Cap Measures
G1 C1 M1 NULL NULL NULL
M2 10 20 21
G2 C2 M1 32 23 24
M2 NULL NULL NULL
G3 C3 M1 12 13 12
M2 11 21 22
Now measure M1 is not valid for G1 C1 so its inserted as NULL in database
Similarly M2 is not valid for G2 C2 so its entered as NULL in database for G2 C2
For G3C3 both M1 and M2 has values and are valid. Similarly it goes on continuing for each and every group.
So now user wants to see in report only measures valid for that level. If I am able to keep NULL in database as NULL in cube then I can hide row entirely if its NULL and my report will look like
Time
T1 T2 T3
Group Cap Measures
G1 C1 M2 10 20 21
G2 C2 M1 32 23 24
G3 C3 M1 12 13 12
M2 11 21 22
Hope you have got what I am trying to explain. So I need to keep NULLS. Can this be done
Thanks
SP
|||So, to be clear, some measures aren't relevant for certain members on your dimension? This sounds like a fact table modelling issue to me - M1 and M2 should be split into separate fact tables (avoiding the need for these null rows) which you then build separate cubes from. You can then use a virtual cube to join these two cubes together and get the result you're looking for, with nulls appearing for invalid combinations in the way you want.
Chris
|||Hi Chris,
This solution sounds to be interesting to me. I think I will definitely try this but with this I will have to split my cube into more than 20 cubes and then use virtual cube to connect all these cubes. This will make my cube structure really complicated. But its definitely worth giving a try. What is your opinion about performance in such a case. Will I be getting performance as good as single cube?
Is there any other way worth giving a try for this issue
Thanks,
SP
|||If you'll have 20 cubes then it will certainly make things complicated, but I doubt it will hurt performance - if anything it will probably perform better than putting everything in one fact table/cube.
Do you really have 20 different groups of measures with different dimensionality?
|||Hi Chris,
I am afraid but I am really having more than 20 measures with different dimensionality.
Can you suggest something else?
Thanks,
|||Well, if you really do, then yes it's going to be complicated having twenty cubes but it's the best approach to take in my opinion.
Out of interest, can you give us some details of what's in these twenty (potential) fact tables? That's a lot of different data types you must be integrating.
Chris
|||Do you need the data type of facts that I am using?
Currently I am using facts which are all float except 4 which are of type string.
its basically related to operation of certain machinery and plants to get different info like inputs, outputs, capability etc. basically giving all info of these machines. So certain info is not valid at particular levels.
|||No, I don't think the data types are all that relevant here. I was just asking to find out if you really did need twenty cubes, but it sounds like you do if you have twenty different machines/plants/etc. Although AS2005 handles this kind of problem much more elegantly, it's definitely possible to achieve the results you want with AS2K. Let me know if I can be any more help! Oh, and if you haven't already, check out the VALIDMEASURE MDX function, I have a feeling you'll be needing it soon...
Chris
|||Hi Chris,
Thanks for your help. Atleast I could convey to user that how complex it will make the design for preserving the NULLS with this option.
You said Although AS2005 handles this kind of problem much more elegantly, it's definitely possible to achieve the results you want with AS2K.
So were you talking about any other possible ways in AS2k or its the same we discussed initially.
Ya definetly I will look into Validmeasure function and check if it can provide me some help.
Please let me know in future if you could find any other way out for my problem
Thanks a lot,
SP
|||Sorry, just to be clear: putting data into separate fact tables is the only approach in AS2K that I can recommend. I'm sure you could probably make it work with your single fact table and some clever MDX, but it would be extremely complex and perform poorly.
Chris
No comments:
Post a Comment