CODEWITHSUNDEEP
sql-serverssasdimensional-modelingstar-schema
Andy C
Andy C

Reputation: 1

Dimensional Modelling help needed - flat tables to star schema

I’ve been reading The Data Warehouse Toolkit 2nd Edition and have recently completed a SQL Server Analysis Services course.

The aim of this post is help me with a design issue I’m experiencing

I’m trying to design a star schema which will be implemented in SSAS and form the base of a reporting platform

The transaction based system we use has the following tables / fields (with sample dummy data)

“policy fact table”

  • Row Name          Sample Data
  • Policy id            112
  • Policy status      X
  • Inception Date    7th April 2013
  • Renewal date     6th April 2014
  • Policy adjustment count     1

This table contains the latest policy information for each policy, so the “policy id” is unique The inception date is also unique The other information is duplicated from the “policy transactions fact table” using a key of “Policy ID” & “Policy adjustment count” The renewal date is updated each time the policy renews (meaning for historic I can extract the “end date” from the “policy transaction fact table” and add 1 day to it)

“policy transaction fact table”

  • Row Name      Sample Data
  • Policy id        112
  • Policy adjustment count       1
  • Start date       6th April 2013
  • End date       5th April 2014
  • Transaction number       4491
  • Transaction Type       402
  • Policy Status       C
  • Gross_Premium       12345.67
  • Insurance premium tax amount       26.78
  • Scheme_id       STDF015N
  • etc

This table contains all the adjustments performed at policy level.

“people on policy transactions fact table”

  • Row Name        Sample Data
  • Policy id        112
  • Driver id        2000000084
  • Transaction number        4491
  • Start date        6th April 2003
  • Date joined       12th December 1937
  • Exit date       9th February 2014
  • individual premium       1234.56
  • etc

This table contains everyone who is on the policy and affected by the transaction

One record from the “policy transaction fact table” will link to one or more records in “people on policy transactions fact table”

I’m struggling to link these 2 tables together in the star schema

I’m pretty sure I need a bridge table to link the fact tables.

If I look specifically at “policy transaction fact table” and “people on policy transactions fact table”

Using the key of “policy id” and “transaction number” from the “policy transaction fact table” and joining it to “people on policy transactions fact table” I can see all the people on the policy

Some guidance would be much appreciated

Thank you for taking the time to read this.

Thank you for the 1st answer below : Thank you, much appreciated. there are metrics attached to the "policy transactions" & "people on policy tables", in the policy transactions

I currently have to set clauses in SQL to count the number of "active" policies in a specific time frame, each policy has a premium, ipt (insurance premium tax), rebate amount attached to it

the "people on policy table" :

as it has all the people on the policy, I can aggregate the driver_id to generate a count of people on the policy, the source system also breaks down their individual premiums, which I can hopefully used to extract "premiums earned"

there are also policy indicators which show the type of policy that they have, its encoded as : MODP500A - so this will be linked to a dimension table which will allow the decoding of it.

Upvotes: 0

Views: 806

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

You don't link fact tables in a star schema. You flatten them.

In other words, instead of relating the Policy table to the People table through a bridge or foreign key, you would have a column in the fact for each person on the policy (Driver1, Driver2, Driver3, etc).

But I question whether any of these tables are even good candidates for fact tables, because I don't see any metrics in them, except Policy Adjustment Count. This looks more like a policy dimension and a people dimension, with the Transaction table being possibly the only fact table.

As a rule of thumb, the structure of your SSAS project should be driven by your reporting needs. Start with what you want to report on, and don't build anything that won't address those needs.

Upvotes: 1

Related Questions