January 3, 2012 at 3:07 am
I have created a cube with Snowflake schema.
This includes a Fact table (Provider) which is linked to a dimension (provider_type) through the column 'prov_id'. The Provider_type dimension has a composite key (prov_id & prov_type_id).When I let the wizard create the cube, and then browse ,all the provider type data are shown against every other dimension i.e. the data is not filtered according to provider type.
To avoid this, we generally use modify dimension usage tab and set up the relationship. I chose the relationship type as 'Regular', Granularity attribute as 'prov_id' but for mapping of relationships both 'prov_id' and 'prov_type_id' are shown.The Measure group column against 'prov_id' will be 'prov_id' but what should be given against 'prov_type_id' ?
Also, is this the right approach or is there a different way when it comes to handling composite key in one of the tables?
January 4, 2012 at 7:52 am
Why did you opt for a snowflake schema? There are limited uses for this in an OLAP design, but most designs are properly down using the star schema.
As for the composite key, you need to eliminate it in favor of a single field generated identity key. You can unique key the two fields if that's a true business key and any updates to the other fields will simply get replace. Once you've done this, you won't have any trouble making the joins in SSAS.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply