Data Warehouse Architecture Modeling

Question

I'm trying to Architecture creating a data warehouse in the Star Schema model... any idea would be appreciated.

Any idea what I should do to create a Star Schema? Some day that I should have a linking table with DimProjects going to the fact tables. What about Project hours? What is the right approach to this or do I need other tables to link? Employee's can work on multiple projects, projects require man hours... etc.

What is the best approach on modeling?

So far I have tables:

[CODE]

    Dimension Tables    Measure Tables
    ----------------    --------------
    DimEmployee           FactCRM
    DimProjects           FactTargets
    DimSalesDetails       FactRevenue
    DimAccounts
    DimTerritories
    DimDate
    DimTime

[/CODE]

Himanshu Ahuja Himanshu Ahuja · Accepted Answer · 2020-04-07T16:32:57

Dimensions in a schema of a datewarehouse means independent entities like for say

 Dim_Employee
Empid(pk) 
Name
Address etc likewise all other 
dimensions

With each dimension keys linked to your fact like in above case

   FactCRM would include only crm 
   related measures and would be linled
  To their specific dimensions depending 
   upon the requirements

Without knowing the columns noone would be able to tell what you want in actual. Also remember linking a dimension to a fact is obviously a partial star schema itself so that doesnt lead to any issues. The only thing is if your dimensions are itself normalized in a schema then it becomes snowflake.

Another thing about fact related if you want to perform manipulation of othwr facts based on somw existing facts then you have to link fact table as well with a unique factid. This is called fact constellation. Then the schema would become star/snowflake schema with facy constellation

Data Warehouse Architecture Modeling

1 Answers