How to Combine Two Fact Tables in SSAS...

In SQL Server Analysis Services Cube, there may be a situation where we need to combine two related fact tables.

This kind of a situation may occur due to the same fact having different dimensions. For an example if we consider two dimensions as Branch and Region, in some areas Sales may happen just only in Regions and sometimes it may go to the level of Branch.

In such a scenario in the Data warehouse we need to have two different tables for those two type of sales like dbo.FactSalesRegion and dbo.FactSalesBranch.

But for analysis purposes we should have all the sales as one. Due to that we need to create one Fact by combining those two fact tables in the data warehouse.

We can do it in the Cube by creating a new fact table using Named Query.

Open the Data Source View by double clicking it in the Solution Explorer. Then right click on the design area and then select New Named Query.

Then provide a name for the combined fact table like FactSales and enter the SQL Query to get the union of dbo.FactSalesRegion and dbo.FactSalesBranch tables.

The query will looks like as below.

SELECT RegionKey, NULL AS BranchKey, Sales

FROM dbo.FactSalesRegion

UNION

SELECT NULL AS RegionKey, BranchKey, Sales

FROM dbo.FactSalesBranch

In the above query we add the non existing columns for each table as NULL and Union those tables, since those two tables which we get the union should have same columns.

The above query provides a new fact table as FactSales which has total sales.

In some complex situations this scenario helps to design the data warehouse.

There are few modifications to be done for the Cube to load data, otherwise it gives errors while processing due to those null values.
We need to modify some properties in those dimensions (in this example Region and Branch dimensions) and also need to change some settings in the dimension usage.

Double click on one of above dimension (assume DimRegion) in the Analysis Services Project. Then select the key (RegionKey) from Attributes and go to properties. In that expand the KeyColumns property and change the NullProcessing property to UnknownMember. After that you will see Key is underline by red color. To solve that issue, select DimRegion from Attributes and go to properties. In the bottom of that property list you can find a property as UnknownMember. Change the value of that property to Visible.

Then you should change some settings in the Dimension Usage area of the Cube. Double click on the Cube in the Solution Explorer and click on Dimension Usage tab. In that you can see the facts and the dimension used for those facts.
Select the RegionKey and click the button in the Right side to get the Define Relationship window. Click on Advanced button and you will get the Measure Group Bindings window.
Under Relationship change the Null Processing of the RegionKey to UnknownMember. Need to do this for all the facts which uses the dimension.

After that you can process the cube and load data.

Hopes this will help you as well...