Search code examples
ssasdata-modelingdata-warehousedimensional-modelingfact-table

Data Warehouse - Multidimensional Model - Fact Table is Smaller than Dimension Table

I am working on data warehouse project where customer dimension table is larger than a fact table. Dimension and Fact tables are created from CRM system.

The fact table monitors activities such as letter is sent to a customer or customer calls for assistance. Half of customers have no activities and remaining customers have very few activities; most of customers who have activities have a single activity.

I am not sure if star schema is the best solution for project. Have you worked on similar projects & what was the solution.

Solution

  • If many of the dimension members are not related to the facts at all. I would sugest to filter unused dimension members during the ETL process.

    So you do a

         SELECT Customer_ID, Name FROM DIL.Customers 
       WHERE Customer_ID IS IN 
       (SELECT Customer_ID FROM DIL.Calls)