CODEWITHSUNDEEP
ssas
LukeP
LukeP

Reputation: 11

SSAS: creating a relation between my dimension table and existing time dimension

I have a dimension table that has two date fields. I need to create a relation to time dimension for both of these fields in order to filter the data by Year/Quarter/date (hierarchy that I defined in time dimension). I'm new at this and a bit lost, can someone please help me?

Upvotes: 1

Views: 1294

Answers (2)

samb0x
samb0x

Reputation: 185

Commonly I would establish this relationship in my Fact Table.

One option is to join the Date Dimension twice over (once for each of your date fields). To create the relationship the first option is to Create Foreign Key Relationships for each of the date fields so that the relationship is translated into your cube. Another option is within the DataSourceView of SQL Server Data Tools > Analysis Services, pull in the source table for your Date Dimension and source table for your Fact table, and create the Primary/Foreign key relationships there.

Another option could be to split this setup into multiple Fact tables each with a single reference to the Date dimension so that you could the plot separate measures to the same graph on the same date axis. See How to avoid Role Playing Dimension. This would also require that you create your Primary/Foreign key relationship in either the source tables or the DataSourceView as mentioned previously.

Upvotes: 0

Md. Shahnewaz Rasel
Md. Shahnewaz Rasel

Reputation: 11

Link both the columns of your dimension table to existing time dimension table in DSV. Create a measure group from that dimension table. Your existing time dimension will then act like a role playing dimension in SSAS cube. You can pick 2 dates from two time dimensions to filter your data.

You can also avoid role playing dimension by doing some changes in your design.

Upvotes: 1

Related Questions