Multiple Joins/Multiple Aggregations in Microsoft SSAS Tabular Model.

In Microsoft SSAS Tabular Model you can make multiple joins between fact and dimensions. The first join is shown as solid line. Any additional joins are shown as dash lines.

However in SQL Server 2012 Tabular Model is using only the first join for value aggregations. It is OK when the model in a simple star schema. However it will not be enough in conformed dimension.

For example: A fact order table has date_submitted and date_closed. both values joined to a Time Dimension table.
The goal is to aggregate data to report weekly, monthly aggregation based on date_submitted. But when attempt to count data based on date_closed the result are incorrect due to the tabular mode is using the first join only, but not the second join.

The solution is that in SSAS Tabular Model you can specify a aggregated value to use the second join. use the USERALTIONSHIP() expression to specify the join after the CALCULATE value.

Count of Date Closed:=CALCULATE(COUNTA('FactTableName'[ColumnName]),USERELATIONSHIP('FactTableName'[ColumnName],'DimensionTableName'[PK_Dim]))

      

Failed to create Timeline on Excel 2013 from SSAS 2012 Tabular Model: "We can't create a timeline for this report because it doesn't have a filed formatted as a Date"

Excel 2013 Pivot Table comes with Timeline feature which provide a visual date slicer to display data in a date range specified by users.



After a SSAS 2012 Tabular Model was built and deployed, which included a time dimension table, when you attempt to create a Timline but Excel failed the operation with the following error message:  "We can't create a timeline for this report because it doesn't have a filed formatted as a Date"

 

Although a time dimension was built and join within SSAS Tabular Model, the dimension table still need to be set as Date table.
1. Open the SSAS Tabular Model  in Visual Studio Development mode
2. Select the time dimension table, go to Table from menu bar.
3. Select Date, select mark as date table.

 
4. need to specify a primary key of the time table. then OK
5. Process, then redeploy the Tabular Model
6. Test it from Excel 2013 by create a Timeline.

 
For detail to how to multiselect dates in Excel 2013, please check out this web site:
http://thomasivarssonmalmo.wordpress.com/2013/03/03/multiselecting-dates-in-excel-2013-on-top-of-ssasbism-multidimensional/
End