Design Question: Dimension made up of several Tables, skip one Level in one Hierarchy

Greetings!

I wonder if someone can give a hint such a design task:

I have a dimension that consists of several tables each chained 1-n to the next one. "Country, City, Shop" maybe. Within this dimension I build a hierarchy down this path, and it works perfectly fine. My measures add up along this path, all good.

Now, say, each Shop has a property describing the location (lets name it LocationSurroundings), like "HighStreet" or "Mall" or "Suburb".

I'd like to get a 2nd look upon this dimension, with the levels being "Country, LocationSurroundings, Shop". I do want to skip City for this view, I want to able to see all Shops that are situated in Malls for a country.

If City was part of that view, I would always get totals to city levels, but not be able to compare shops from different citys in the same country. Is there a solution to this? I need the city property in some way to connect shop to country, but I do not want it to be visible or to generate subtotals at this level...

Thanks for your ideas!

Ralf

Hello. I am not sure about the problem here. If you are using SSAS2005 you have both attribute hierarchies, with each attribute not belonging to a hierarchy,and the user hierarchies, which are the ones you describe like Country-City-Shop.

Could you not simply add Locationsurrondings as an attribute(attribute dimension) in the dimension?

Regards

Thomas Ivarsson

|||

Hi Ralf,

Please expand a bit on your problem.

As I see it you can just make a second hierarchy and make city a hidden level. In the DIM designer, create the complete hierarchy (including city), choose the hierarchy, choose the level (city) right-mouse > properties > Visible - false

But when you are talking about a view, you mean a data source view or a SQL-view.

Kind regards,

Johan Blad (also somewhere in Europe)

|||

Thanks both of you, I got it working as I need.

What I meant to acomplish were really 2 goals:

a) A hierarchy with a level missing. This can be done by chaining together the attributes as they need to be, but on the hierarchy you just leave out the one you do not want. SSAS is smart enough to understand that. I now got 2 hierarchies in this dimension, one that includes all levels and another one that skips some levels.

b) Include another attribute (here: the shop area description) into the hierarchy. This is not possible, as it has no relations. I solved that by just making it an attribute, as suggested. Works fine!

That is solved. Thanks VERY MUCH.

But another question that now came up:

I have a self-referencing dimension table (a) that contains a hierarchy of levels. The lowest level items are NOT the ones that join to the fact table... the lowest level joins to another table (b) where a lot of attributes for a complete different type or real life object are listed, and this table finally joins the fact data (c).

What I mean: Imagine a company employee hierarchy down from manager to department leaders to regular employees (that is the hierarchy, a). Now each employee has several accounts to work with (stored in the account table, b). Finally, these accounts are referenced in the fact table c.

I would not want to make this one table, an employee is a complete different thing than an account with a different set of attributes.

Whatever I try, I cannot build a dimension that contains a self-referencing table AND a normal one... is that so? Is there a way to do that? How? What? Where?

|||

As far as I know, you cannot blend a parent-child and a normal dimension.

One option would be to use a referenced dimension relationship (from "fact table c" to account to employee).

Another option would be creating a fact-less fact table that shows the relationships between employees and accounts. So employee and accounts are related to new fact EmployeeAccounts. Then create a many-to-many dimension relationship from your "fact table c" to Employees.

Third (and simplest) option - just add both dimension keys to the fact.

None of these will give you a single dimension representing Employees and accounts. But, if they are two different entities, is that really what you want to do?