Sql-server – SSAS cube fact and dimension relationship

olapsql serverssastabular-model

I have a cube that all the tables has one row per ID and have measure in all of them except for the calendar dimension.

When I view the measures in excel from Table 1 with attributes from table 2, I have no issues, but when I view the measures from Table 2 with attributes from Table 1 it returns the same number for each value of the attributes:

BillingCycle        HCD Accounts For Review
7                     4067
14                    4067
30                    4067

How can I overcome this issue? Does this have anything to do with the direction of the relationship between the tables?

Best Answer

What you are seeing is a typical example of a missing dimension relationship.

Yes it has something to do with the relations in your dsv but easy to solve with dimension relationships.

If you add a dimension to a measure group/cube/fact table the dimension relations are automatically generated based on the relations in the data source view, if a relation isn't present a dimension relation has to be created manually.

So in your example you need to go to the cube where the measure group is residing and select dimension relationships and define the relation between your dimension key and the fact table.

See defining dimension relationships for details on how to do exactly that.

It's actually fairly obvious why SSAS would behave like that. If there is no relation between axis and figures, there is no way a slicer can have impact on the figures.

If you are using a Tabular model, unless it lets you add relations in both directions I fear you'll be out of luck and need to design your data warehouse better using actual fact tables and dimension tables.

Related Solutions

SSAS – Inferred Dimension Attributes – are they necessary

I'm not sure I understand your question fully, so please clarify if necessary.

If you want to query the cube to get persons by state, you should add a Referenced relationship between Person and State, using Case as the Intermediate dimension.

Select the empty box at the intersection of Person and State and click on the button
Select Referenced from the relationship type dropdown
Select Case as the Intermediate dimension
Selecting the matching attributes in the two attribute dropdowns
Click OK.

Deploy and process the cube, and it should work the way you want. Then you can get rid of the (State - Person) dimension.

SSAS -> New cube contains two fact tables. How to relate these so I can use measures from both at the same time

You're focusing on facts and forgetting about dimensions.

Years of service is a measurable attribute of a Driver, so, you should have a Driver dimension associated with the FactDeliveryDriver fact.

driverSid is a surrogate key for the Driver dimension, and not for the fact table. For the fact table it's a lookup column.

Also, deliveries are (probably) made by a Driver, so you should have the Driver dimension associated with the FactDeliveries fact as well.

There should also exist a Delivery dimension, that is associated with the FactDeliveries.

A simple schema should be something like:

Driver(sk_driver, name)
Delivery(sk_delivery, address, date_delivered)
FactDeliveryDriver(fk_driver, val_years_of_service)
FactDeliveries(fk_driver, fk_delivery, val_miles)

Since the Driver dimension is common to both fact tables, you can use the Driver dimension to relate measures from both of them.

Best Answer

Related Solutions

SSAS – Inferred Dimension Attributes – are they necessary

SSAS -> New cube contains two fact tables. How to relate these so I can use measures from both at the same time

Related Question