September 18, 2013 at 9:34 am
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.
September 18, 2013 at 10:39 am
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.
September 18, 2013 at 11:38 am
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?
September 18, 2013 at 12:43 pm
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.
September 18, 2013 at 1:06 pm
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,
September 19, 2013 at 6:07 am
Remember the original fact table:
- Account 1: DatePK 20130901, AccountPK 1, Value 10
- Account 2: DatePK 20130901, AccountPK 2, Value 20
- Account 3: DatePK 20130901, AccountPK 3, Value 30
When slicing by date, the total for 20130901 would be 60. Now the new fact table in the case I described:
- Account 1: DatePK 20130901, AccountPK 1, Value 10
- Account 2 +: DatePK 20130901, AccountPK 21, Value 20
- Account 2 +: DatePK 20130901, AccountPK 22, Value 20
- Account 2 +: DatePK 20130901, AccountPK 23, Value 20
- Account 2 -: DatePK 20130901, AccountPK 24, Value -20
- Account 3: DatePK 20130901, AccountPK 3, Value 30
With this fact table, if we view the cube from the Groups-Accounts dimension, it would be somewhat correct (new names for the Accounts, but that's acceptable as they're intuitive). But if you slice by date, now the total would be 80.
I don't see this working if we modify the fact table, because that directly affects the other dimensions.
September 19, 2013 at 6:47 am
- Account 2 +: DatePK 20130901, AccountPK 22, Value 20
- Account 2 +: DatePK 20130901, AccountPK 23, Value 20
Why doesn't this account have the same PK? This adds an extra 20, which would account for the difference between 60 and 80.
September 19, 2013 at 9:45 am
Because if you don't use separate keys, only one of the three occurrences of the positive account (21, 22 or 23, the one you choose) in the Groups-Accounts dimension would have a value. Remember each occurrence in the dimension has its own unique key. This is the problem of using the regular relationship.
September 19, 2013 at 11:30 am
Sorry, but I'm not following that at all. As I've said, we might be at the limits of being able to help without actually seeing the item and there could still be something I'm missing. But I have never seen in an SCD1 dimension the same dimension name having multiple surrogate keys. I'm assuming it's an SCD1 as you haven't mentioned anything about the need to provide history. It this is a fact table keying issue, you will need to have the fact table produce its own otherwise meaningless key.
October 22, 2013 at 8:13 pm
strangely, I think I am having the same issue. situation is with financial GL cube. There are accounts that roll up nicely into their income statement buckets. Then, there is a section called EBITDA, which includes many of those accounts again, or a second time. In order to put these in some sort of hierarchy in SSAS is brutal. I'm still not sure the best way to proceed.
Viewing 10 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply