Luis Simões


How should i design my cube when for example my layout is all based in "company"

For example... i have a dimension "company". When i choose the company i just wanna see members of dimensions that belong to that company...

So all dimensions have a company atribute and the facts all mention the company obviously.

How can i design that so that it makes it all related and filtered

Should the company table be related only with the dimensions Should it be connected to the fact table only

Best Regards,





Re: When a dimension attribute resides in every dimension and fact table?

matt_yell


Hi,

If your fact is at the company granularity then just to the fact, unless there is some hidden business logic I'm not aware of. When you filter on company, it will filter out all the facts not related to that company.

Cheers

Matt






Re: When a dimension attribute resides in every dimension and fact table?

Luis Simoes

Hi matt,

Relating to fact data, i know it work that way... i was talking about dimension filtering...

Imagine you have a company dimension and a vendor dimension... when i choose a certain company... in the vendor dimension i would get a list of only the vendors in that company... If no company is selected then all vendors should be seen...

Regards







Re: When a dimension attribute resides in every dimension and fact table?

Matt Tolhurst

Hi,

I think you are talking about referenced dimensions. Although you could snowflake the dimension, someone was asking about that early: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1860887&SiteID=1

I would have a look at adventure works DW and have a look at the way they use the customer and the geography dimension. The geography table is used as a referenced dimension and a snowflake. Both for different purposes, the main reason it is included in the customer is so they can use it in the hierarchy.

Referring to you scenario, if company and vendor are on the fact and you filter by company, you will only have facts for vendors from that company left. I am not aware of filtering a dimension; in a result set you can hide empty cells. If you snowflake company onto vendor and filtered that by company you would still have a list of all vendors, some with values and lots with nulls. One way around that would be to build a hierarchy where company drilled down to vendor. It depends what the end-user wants really.

Did that help Or have I missed the point still

Matt





Re: When a dimension attribute resides in every dimension and fact table?

Luis Simoes

Hi Matt,

The hierarchy is the most close to the solution... but the problem is that i have lots of dimensions lying on the company attribute... so every dimension needed to be a hierarchy...

Regards,






Re: When a dimension attribute resides in every dimension and fact table?

Matt Tolhurst

Hi,

You probably have a couple of options.

1) Snowflake the company dimension onto every other dimension importing all the attributes from company.

2) Snowflake the company dimension onto every other dimension BUT only import the attributes you need for the hierarchy. Create a company dimension, it will contain all attributes, then reference that onto other dimensions via the company_key or just onto the fact.

The problem with 1 is that if you make a change to company table, you are going to probably have to update every dimension which has a reference to the company table.

The problem with 2 is you are going to end up with quite a few referenced dimension e.g. vendor company, product company, employee company (not the best examples).

It isn't possible for you to nest dimensions one after the other Giving you an option 3, e.g. Company dim nested before vendor dim in your cube browser. Less work as you only have to create one company dimension, no referenced dimensions and no snow flaking. It will give you the same results as the others and is a bit more flexible.

COMPANY DIM VENDOR DIM

Company, Company location | Vendor Name, Vendor Sales person

a&b London | AAA Fred

b&c NY | BBB Tom

b&c NY | BBB Harry

Final thought, it is a big overhead place company in every dimension and maintenance could become an issue, but for end user experience less confusing. The joys of cube building!!

Matt