Hello Analysis Server Users!
I wonder if someone can help me with the setup a dimension that is a parent-child hierarchy first, but the leaves are not joined to the fact table, but instead to another table containing a list of elements for this leaf, and from there fianlly to the facts. Hmm... bad wording and description, sorry.
Maybe an example helps: say I got a list of 5 elements a-e that are in a simple list dimension and act as hosts for fact data. Just simple straight list a, b, c, d and e. This list is often used and must be kept, it is stored in a simple straight table in the database.
I would now like a group in addition to that, as an alternative, say I want to see grouped together b, c and e.
I could do that by adding an attribute "my group" - but that would automatically result in another group "not my group", containing a and d - It would look like this:
(all)
-mygroup
--b
--c
--e
-other group
--a
--d
In my world this group "other" and the data aggregated on it is nonsense, the business does not use the data in that combination.
So what I am looking for is something that looks like this:
(all)
-a
-d
-mygroup
--b
--c
--e
I know how to build that, I did do parent child dimensions before, but the special twist here is that I cannot add the mygroup name to the list of a - e, since than it appears as a new item in the list. Remember, I early said I want to keep the straight list a - e and only want the hierarchy in addition to it.
So, the hierarchy elements "mygroup" needs to be put into another table, and that is when I am stuck... Can I have a self refercing table as top level, then another table below it with the elements, and that finally leads to the 3rd table with the fact data?
Hello,
I think, you can use the parent-child hierarchy as you have build it, and for getting the straight list, you can reference it in MDX with Descendants([All], <last level number>, LEAVES), so for example if the last level is 3 and the top Level is [All]: Descendants ([All], 3, LEAVES), for getting a list a ... e
Hans
|||I think I understand what you are trying to say, but I admit I cannot see how you "add" the MDX to the dimension.
Do you mean to build one or two dimensions? One, I guess, parent child type.
Where can I put the MDX to make the dimension get the two "apperances", to get the straight list "minus" (filtered out) those elements that form the hierarchy in the other view? Is that a property or?
|||Hi,
No, I think you make one parent-child dimension with all the grouping you need. Than you make a calculated member under "Calculations" in visual studio with that MDX. Because the "list-view" is only "another view" with that dimension for retrieving data in that way. I think you didn't build this in a dimension, because it's only 2 different ways of viewing at the data. It's the same as in the relational world: you have one table and a lot of different querys fullfilling your nees. Here you have your dimensions and fact-tables an a lot of MDX queries (perhaps encapsulated as calculated members) which bring the data as you need it.
Hans
|||I am not fully aware of what you would like to do here but visually it looks to me as a problem that dimension writeback can solve.
With dimension writeback you can create members in a parent-child dimension that do not exist in the source table. You can also move existing members in a dimension tree.
I think that you will need the SSAS2005 Enterprise edition for this feature.
Regards
Thomas Ivarsson
No comments:
Post a Comment