Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SSAS - How do I handle dimensions with valid duplicates? Expand / Collapse
Author
Message
Posted Tuesday, September 17, 2013 9:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
Given that you already have a parent-child table, did you try setting it up that way in SSAS? It actually does so more naturally than in an OLTP design. If there are tens of thousands of entries, there may be a performance issue, but it should be worth a try.

If you didn't or for some reason are not allowed, let me know, and I'll look at the first table as if it's a standard dimension. Definitely looks like it should be possible, however. You could even consider setting it up as a ragged dimension if necessary. That might give you a more natural appearance. Read about that before your next post to see if that helps.



Post #1495560
Posted Tuesday, September 17, 2013 9:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
Yes, I did, and I face the same problem: in a parent-child dimension a given child can have only one parent. In my case, Account 2 and Account 3 have two parents: Group 3 and Group 5. This gives two duplicate key errors that don't let me process the dimension. If I ignore these errors and process anyway, then Account 2 and Account 3 only appear as children of the first parent, in this case Group 3, just like with the flattened hierarchy.

I've read about ragged hierarchies, and if I'm not mistaken my flattened hierarchy is already ragged. As said in the first post I'm using the HideMemberIf property and it works just fine.
Post #1495570
Posted Tuesday, September 17, 2013 11:49 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
OK, I see the real issue better. Let me think on this a day and I'll get back with you.


Post #1495614
Posted Wednesday, September 18, 2013 6:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
It seems odd that the amount essentially gets counted twice. Assuming that makes sense, however, I would recommend something along the line of the previous suggestion. I wouldn't necessarily use the surrogate key, but I would create an artificial key if there is not a natural key to use that would be different for all the accounts, even those of the same name. The ETL process would take the single value for the repeating accounts and ensure that "both" accounts are populated with the proper single value. SSAS would see two accounts, but it would still function as you've described.

What do you think?



Post #1495906
Posted Wednesday, September 18, 2013 9:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
That would work if this dimension was the only one present in the cube. Imagine I duplicate the values for Account 2 and 3 in the fact table, each with its own unique key to the dimension, and create a regular relationship between them. This is easy and works beautifully, but if I add the time dimension when filtering by date the sum would be more than the real sum because there are more fact records than before. I could tell the client something like "hey, you have this account 3 times in the dimension, so it's normal that when filtering by date you get values as if the account was triplicated", but I don't think this is a viable solution. Another one would be doing two cubes, one with only this dimension and one with the rest, but this is ugly as well.

I can't believe something like this can't be properly or directly addressed by SSAS. Dimensions with duplicates are very common in all kinds of business. I understand the duplicate key issue, but I don't understand how SSAS doesn't do something internally to handle this and consider each duplicate as unique. I've been trying the many-to-many relationship and the damn thing doesn't support unary operators (another requirement) properly, so this solution is out too.
Post #1495977
Posted Wednesday, September 18, 2013 9:34 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
Dimensions with duplicates are very common in all kinds of business.


I don't know. Obviously I'm still missing something trying to help you. I've seen duplicates in the sense of repeated names. The city of Columbus exists in Ohio and Georgia and probably others. But they are different cities sharing the same name. In this case, it's the same accounts appearing in two different levels of the same hierarchy.

doesn't support unary operators


What's not working that you need? I've also used these before, but again, not with the same account at different levels.



Post #1495986
Posted Wednesday, September 18, 2013 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
I don't know. Obviously I'm still missing something trying to help you. I've seen duplicates in the sense of repeated names. The city of Columbus exists in Ohio and Georgia and probably others. But they are different cities sharing the same name. In this case, it's the same accounts appearing in two different levels of the same hierarchy.

Not really the same thing. What do you do if you have a single record in your fact table like "Date 2008, City Columbus, Value 10"? How do you link this single value to each Columbus city, each with its own unique key? That would be an one-to-many relationship, but I imagine that if you have 3 distinct cities you'd have 3 distinct records in the fact table, because each city in itself is unique and that makes sense. Here the account is one and the same, with only one value, the user just wants it adding in one group and substracting in another one.

What's not working that you need? I've also used these before, but again, not with the same account at different levels.

Read here for many-to-many and unary operator issues. The exact same thing happens here. The proposed workaround is the one already discussed here, duplicate the recods in the fact table, which is not really a solution, as we have already discussed. The last comment in that blog wonders about the same unwanted duplication. If you google "many to many unary operator" it seems it's a well known problem with no solution.
Post #1496014
Posted Wednesday, September 18, 2013 11:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
the user just wants it adding in one group and substracting in another one.


I think this was what I wasn't understanding. I did have a similar experience once with our technicians and escalations. Some escalations were good, and some not so good, but the tech category had to go under both. I ended up with naming one group Technician(+) and the other Technician(-). Can you do something similar by calling one account Account n (+) and the other Account n (-), with the ETL making the appropriate entries to turn the second one into a negative number?



Post #1496045
Posted Wednesday, September 18, 2013 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, August 14, 2014 5:04 AM
Points: 12, Visits: 62
I could, but it doesn't address the issue: what if the user decides to put Account n adding in 3 groups, but substracting in 1? Again results would be off when slicing by date or any other dimension related to the fact table.
Post #1496075
Posted Wednesday, September 18, 2013 1:06 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:41 PM
Points: 815, Visits: 2,020
Again results would be off when slicing by date or any other dimension related to the fact table.


We may be at the limits of what we can do without something actually in front of us, but I don't understand why these accounts wouldn't be exactly the same regarding their fact table entries EXCEPT for the different account number. That would put them in all the same slices and dices. Can you clarify this?

Thanks,



Post #1496092
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse