CODEWITHSUNDEEP
sqlsql-serversql-server-2008ssasdata-warehouse
picmate 涅
picmate 涅

Reputation: 4259

Relationship Between Two Dimensions in SSAS

I am developing an ssas database and have snowflaked dimensions to which it has links. For example I have a customer dimension table, distributor dimension table and a territory dimension table in which there is a relationship to the latter from the other two. Therefore I can illustrate the relationships as follows:

Retailer <-- Territory Distributor <-- Territory

In a specific cube in the database, I have measures where all the three dimensions mentioned above have relationships to. As far as the measures are considered browsing across individual dimensions happen smoothly.

But the problem comes when I try to browse a related measure from two dimensions at the same time; eg: territory and distributor

All the distributors are shown under a given territory.

When I add the territory key attribute to the distributor dimension and that specific attribute is used from the distributor dimension it self the relationship is shown correctly. But when I try to go from the territory dimension in the cube this relationship does not get exposed as explained earlier.

Any help is deeply appreciated.

Upvotes: 0

Views: 4009

Answers (1)

Pondlife
Pondlife

Reputation: 16260

This may not answer your question directly, but if you have several dimensions that are closely related and often used together, you could consolidate them into a "mini-dimension" that has every possible combination of territory, distributor and retailer (see my answer to another question):

create table dbo.DIM_TerritorySalesChannels (
   TerritorySalesChannelID int not null primary key,
   TerritoryName nvarchar(100) not null,
   RetailerName nvarchar(100) not null,
   DistributorName nvarchar(100) not null,
   /* other attributes */
)

This might initially seem awkward, but it's actually quite easy to populate and manage and it avoids the complexity of relationships between dimensions, which often gets messy (as you've discovered). Obviously you end up with one very large dimension instead of three smaller ones, but as I mentioned in the other answer, we have several hundred thousand rows in one dimension and it's never been an issue for us.

Upvotes: 1

Related Questions