CODEWITHSUNDEEP
one-to-manydata-warehouse
kevmull
kevmull

Reputation: 103

Data warehouse one to many dim tables

I'm creating a Data warehouse for the first time and not sure how to go about with my second 'many' table. Where it's a dim, fact or even factless table.

So far I have two tables, Employee (one) and EmployeeAppLogin (many). Both are SCD2 type.

dim_Employee is your typical HR employee dim table and EmployeeAppLogin which I think should store EmployeeAppKey (SK), EmployeeID (NK), loginName. Or should it the employee_key (SK) from din_staff instead of EmployeeID (NK) from dim_Staff.

The EmployeeAppLogin table is simply a look up table and holds no transactions. It will be used to cross ref and identify an employee via the loginName on third party apps. I haven't got access yet to the transaction tables from these third party apps.

So not sure where t fits in the DW.

Many thanks

Upvotes: 1

Views: 581

Answers (1)

David Rushton
David Rushton

Reputation: 5030

One approach is to create separate dimension tables for Employee and Login. Report writers will be free to filter\aggregate by employee, login or both.

Sample schema

Data warehouses have a massive advantage over traditional databases. The contents of every cell, row and column is under your direct control. You are free to transform the source data in any way you want. For this reason, I design my dbs to be as easy to use a possible. Because every single record passes through my ETL process, I can enforce relationships there. The schema does not need to do this.

You will probably need a bridging table, to populate this structure. But this can be hidden in your staging area.

The key benefit of this design is it provides you with a simpler, flatter structure. I find this reduces the number of complex queries I have to write (which improves my productivity). Flatter structures work better with data visualisation tools, like Power Bi and ad-hoc query tools, like Analysis Services.

Upvotes: 2

Related Questions