Dimension Design (Hiding Level / Apexing)
-
Anyone come across something similar?
We have a dimension whose levels are
Business Unit
State
City
where the State are not uniquely named within the level (ie BusUnit "Western Sales" has "CA" and "WA" as children, as does Eastern Sales).
This is a shared dimension, that we want to include in 2 separate cubes (Eastern and Western), and we want the highest level to be the State, so basically apexed on each state within (say) the Eastern Business Unit. So the user would get a dimension whose top level members are "CA", "WA" ....
We have tried doing this via Custom Dimension security, and also just hiding the Business Unit level in each of the cubes, but in both cases AS insists on having unique *names* for the first visible level.
Is this a design flaw (ie I would have thought unique keys would be acceptable as a requirement), or is there a way around this?
Look forward to some comments.
Steve.
Steve.
-
This was removed by the editor as SPAM
-
Hi stevefromOZ - hope you are well.
I assume at one time you had one cube and used the BusinessUnit level to provide a way to divide your data. But now you have 2 cubes and use the cubes to divide your data.
If that's the case, I'd eliminate the BusinessUnit level.
I have a few dimensions where the first level has repeated data. And they work fine.
Check your dimension in the dimension editor and be sure you allowed for names as not unique.
Jennifer
-
Hi Jen,
Doing well (Aus weather is kind, today is a balmy 30c/92f). Hope you're keeping well also.
We do split the data by cubes, actually by catalog is more precise. But we're running into mem issues, as the same (largeish) dimensions are being replicated within multiple catalogs. So, in an effort to have conforming dimensions, we wanted to use just one dimension (shared) which we could use for many cubes.
But, restrictions are, the client insists on *not* seeing the Business Unit level. We can't use private dimensions as we use Virtual cube (resultant cube has > 1 distinct count, so multi cubes in only solution) over the top.
Which leads me back to where we are now. If one sets the names to be not unique, but keys can be unique within dimension, when you try to suppress/hide the top level, you get an error stating that "Member names must be unique in first visible level of a dimension". This is what I am finding odd, keys being unique I can live with (I prefer
) but requiring unique *names* I think is a design flaw in AS2K. If you're interested, I have a little more detail on the MS olap newsgroup [microsoft.public.sqlserver.olap], and even have some intial correspondence from Msft on the issue.
Steve.
Steve.
-
I'll check out the news group info. Thanks.
How about making another shared dimension with just state & city. When I do this, keeping the duplications in both levels, it works just fine.
Think that might work? If it does not, can you put up more info about what the source table for your dimension looks like?
Jennifer
) but requiring unique *names* I think is a design flaw in AS2K. Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply