There will come a time when an additional fact table will be needed in a Data Mart. This is not the time to create a new tabular model. Adding fact and dimension tables to an existing tabular model is not a bad thing. There is some training for analysts that use the model, but that should not prevent the expansion of the Analysis Service database. The new fact table(s) can use the same dimension tables or may need new dimension tables. The common dimensions are called conformed dimensions. The additional feature called perspectives can assist with the training of end users

Conformed (Shared) Dimensions

Conformed dimensions exist for sharing the slicers and dicers in a model. In Figure 1, the Sales and Transaction fact tables both have relationships (foreign keys) to the Customer and Date dimensions. This gives the end users a way to display an attribute like Calendar Year from the Date dimension and display a measure from both fact table at the row or column level.

Figure 1 Multiple Fact Tables – Conformed Dimensions

Transaction Fact

The new table in our model will be the Transaction table. The measure created from this fact is Transaction Amount. This can give an end user an idea of the sales for a month and year while showing transaction amounts received from the same customer(s). Figure 2 gives an example from an Excel Pivot Table.

Figure 2 Sales and Transactions

First, the Transaction table must be imported and relationships created between the new fact table and the conformed dimensions Date and Customer. One way to do this is with the Tabular Model Explorer (Figure 3). After drilling into the Data Sources, right click the database connection and select Import New Tables. The Model menu choice from the main menu also has an Import from Data Source option for the existing connection.

Figure 3 Tabular Model Explorer

Along with the Date and Customer relationships, the transaction table has relationships (foreign keys) to dimensions Payment Method, Supplier and Transaction Type. These are not conformed to the sale facts. Figure 4 shows the import of the Transaction and other dimension tables.

Figure 4 Importing Transaction and Related Dimensions

Once imported, the fact and dimension tables need cleanup like Articles 5 and 6.

1. The columns Valid From, Valid To and Lineage Key need to be deleted from dimensions Payment Method, Transaction Type and Supplier
2. Rename the dimension tables without the word Dimension
3. The surrogate key needs to be hidden from client
4. All columns in the transaction fact table need to be hidden from client
5. Rename the fact table without the word Fact
6. The new measure Transaction Amount must be created

Transaction Amount := SUM([Total Including Tax])

The relationships between the Transaction table and Date and Customer need to be created like Figure 5. The relationships between the Transaction table and Transaction Type, Supplier and Payment Method will be automatically created because they were imported at the same time. There is an active relationship (Customer Key) and a non-active relationship (Bill To Customer Key) to the Customer table.

Figure 5 Relationships between Transaction Table and Dimensions

Once these changes are complete, Process the current model or deploy to a server.

Perspectives

Perspectives help with some of the confusing aspects of using multiple fact tables in a model. Since there are dimensions that do not have a relationship to both fact tables, all dimension attributes cannot be used with all measures. Figure 6 shows the Total Sales measure being sliced by Transaction Type.

Figure 6 Sales Total Amount Sliced by Transaction Type

It is sometimes difficult for the end user to understand they cannot drag Transaction Type into a Pivot Table or Power BI report with Total Sales. There is where a perspective can help. Perspectives are like views in a database. The view shows only the columns needed for a report for query. The Perspective only shows the tables and attributes needed for a specific reporting option.

There are two places to access perspectives, from the Model menu choice or right click the Perspective folder in the Tabular Model Explorer (Figures 7 & 8)

Figure 7 Perspectives from the Model Menu

Figure 8 Perspectives from the Tabular Model Explorer

The example used here will create two different perspectives: one for Sales and one for Transactions. Click New Perspective twice to add Sales and Transaction as perspective. The Sales perspective (Figure 9) has the Sales, City, Customer, Date, Employee and Stock Item tables. The Transaction perspective has the Transaction, Customer, Date, Transaction Type, Supplier and Payment Method tables. Notice both have Customer and Date. These are the conformed dimensions that have a relationship to both fact tables.

Figure 9 Sales and Transaction Perspectives

To check if perspectives are working properly, Analyze in Excel will prompt for a perspective to use (Figure 10). The main Model can always be used, but end users will need training on selecting the right perspective. An additional perspective could be created with the two fact tables and just the related conformed dimensions Date and Customer.

Figure 10 Selecting a Perspective

After selecting the Sales perspective, only those tables are available (Figure 11). Perspectives can also be used to deselect columns (attributes) from the tables as well as whole tables.

Figure 11 Excel Pivot Table with Sales Perspective

Summary

This article starts to expand the model beyond one fact table. Measures are created in facts and can be related to similar dimensions in a dimensional model. The use of side by side measures from different fact table shows the power of using a Tabular Model as a single source of truth. The Perspective is a nice touch to create an easy to use selection for end users.

Stairway to SSAS Tabular Level 8: Multiple Fact Tables and Perspectives

There will come a time when an additional fact table will be needed in a Data Mart. This is not the time to create a new tabular model. Adding fact and dimension tables to an existing tabular model is not a bad thing. There is some training for analysts that use the model, but that should not prevent the expansion of the Analysis Service database. The new fact table(s) can use the same dimension tables or may need new dimension tables. The common dimensions are called conformed dimensions. The additional feature called perspectives can assist with the training of end users

Conformed (Shared) Dimensions

Conformed dimensions exist for sharing the slicers and dicers in a model. In Figure 1, the Sales and Transaction fact tables both have relationships (foreign keys) to the Customer and Date dimensions. This gives the end users a way to display an attribute like Calendar Year from the Date dimension and display a measure from both fact table at the row or column level.

Figure 1 Multiple Fact Tables – Conformed Dimensions

Transaction Fact

The new table in our model will be the Transaction table. The measure created from this fact is Transaction Amount. This can give an end user an idea of the sales for a month and year while showing transaction amounts received from the same customer(s). Figure 2 gives an example from an Excel Pivot Table.

Figure 2 Sales and Transactions

First, the Transaction table must be imported and relationships created between the new fact table and the conformed dimensions Date and Customer. One way to do this is with the Tabular Model Explorer (Figure 3). After drilling into the Data Sources, right click the database connection and select Import New Tables. The Model menu choice from the main menu also has an Import from Data Source option for the existing connection.

Figure 3 Tabular Model Explorer

Along with the Date and Customer relationships, the transaction table has relationships (foreign keys) to dimensions Payment Method, Supplier and Transaction Type. These are not conformed to the sale facts. Figure 4 shows the import of the Transaction and other dimension tables.

Figure 4 Importing Transaction and Related Dimensions

Once imported, the fact and dimension tables need cleanup like Articles 5 and 6.

1. The columns Valid From, Valid To and Lineage Key need to be deleted from dimensions Payment Method, Transaction Type and Supplier
2. Rename the dimension tables without the word Dimension
3. The surrogate key needs to be hidden from client
4. All columns in the transaction fact table need to be hidden from client
5. Rename the fact table without the word Fact
6. The new measure Transaction Amount must be created

Transaction Amount := SUM([Total Including Tax])

The relationships between the Transaction table and Date and Customer need to be created like Figure 5. The relationships between the Transaction table and Transaction Type, Supplier and Payment Method will be automatically created because they were imported at the same time. There is an active relationship (Customer Key) and a non-active relationship (Bill To Customer Key) to the Customer table.

Figure 5 Relationships between Transaction Table and Dimensions

Once these changes are complete, Process the current model or deploy to a server.

Perspectives

Perspectives help with some of the confusing aspects of using multiple fact tables in a model. Since there are dimensions that do not have a relationship to both fact tables, all dimension attributes cannot be used with all measures. Figure 6 shows the Total Sales measure being sliced by Transaction Type.

Figure 6 Sales Total Amount Sliced by Transaction Type

It is sometimes difficult for the end user to understand they cannot drag Transaction Type into a Pivot Table or Power BI report with Total Sales. There is where a perspective can help. Perspectives are like views in a database. The view shows only the columns needed for a report for query. The Perspective only shows the tables and attributes needed for a specific reporting option.

There are two places to access perspectives, from the Model menu choice or right click the Perspective folder in the Tabular Model Explorer (Figures 7 & 8)

Figure 7 Perspectives from the Model Menu

Figure 8 Perspectives from the Tabular Model Explorer

The example used here will create two different perspectives: one for Sales and one for Transactions. Click New Perspective twice to add Sales and Transaction as perspective. The Sales perspective (Figure 9) has the Sales, City, Customer, Date, Employee and Stock Item tables. The Transaction perspective has the Transaction, Customer, Date, Transaction Type, Supplier and Payment Method tables. Notice both have Customer and Date. These are the conformed dimensions that have a relationship to both fact tables.

Figure 9 Sales and Transaction Perspectives

To check if perspectives are working properly, Analyze in Excel will prompt for a perspective to use (Figure 10). The main Model can always be used, but end users will need training on selecting the right perspective. An additional perspective could be created with the two fact tables and just the related conformed dimensions Date and Customer.

Figure 10 Selecting a Perspective

After selecting the Sales perspective, only those tables are available (Figure 11). Perspectives can also be used to deselect columns (attributes) from the tables as well as whole tables.

Figure 11 Excel Pivot Table with Sales Perspective

Summary

This article starts to expand the model beyond one fact table. Measures are created in facts and can be related to similar dimensions in a dimensional model. The use of side by side measures from different fact table shows the power of using a Tabular Model as a single source of truth. The Perspective is a nice touch to create an easy to use selection for end users.