Cant figure out how to create this star scheme.

[EricHulshof]

Hello,

I am currently working on a report and decided i really need a star scheme. Why? Because everywhere i read it says its the best structure. However i have a little issue with my data setup where id like some help from you experts out here. 

The report is about systems(applications). There is a dataset connecting all other data sets. So that has to be the Fact table i believe. This one would look like this:

Portal

Application ID

Applications Title

Supportgroup

Created Date

Modified Date

The created date and modified date are the dates the record has been added or changed. for example (Created: 01-01-2017, Modified: 04-05-2018)

Now i have a couple of dim tables. these are connections to different systems where i can collect changes, incidents, issues, costs etc about that defined application. An example of these tables look like this:

Changes

Change ID

CreatedDate

ReportDate

CreatedThisMonth

DeletedThisMonth

Leadtime

Supportgroup

Impact

etc.

Incidents

Incident ID

CreatedDate

ReportDate

CreatedThisMonth

DeletedThisMonth

Leadtime

Supportgroup

Risk

etc.

Issues

Issue ID

Original estimate

Work completed

Work left

CreatedDate

ReportDate

Iscurrent

Applications Title

etc.

As you can see i collored where i can connect them. In Changes and incidents there is a creation date, a report date and some boolians deciden wether or not it was created/deleted that month. On Issues its different. There is also a created date but the status of each issue is measured by day, not by month.

Last step i would like to add a date tabel to the whole structure to be able to for example ask for everything between may 2019 and now. How would i go about connecting this date table to the current structure? here is the date table example:

Date

Date

Month

Week

etc.

Thanks in advance.

[JarroVGIT · Accepted solution]

Hi @EricHulshof ,

I think in your case you are mixing up Dimension tables and Fact tables. Fact tables often represent events or transactions. Dim tables describe objects tied to those events or transactions. In your case, Applications are usually a Dimension table as they describe objects (Applications). The table Changes can be construed as a dimension table (every change is an object in itself) but is much more logically a Fact table (changes are every accumulating and they happen in a chronological order, and are tied to for example Applications (by Application ID). The same goes for Incidents and Issues, these are typically Fact tables. 

Now think of it this way; you have multiple Fact tables (center of the Star model) that just happen to share similar Dimension tables (you don't need seperate Dimension table for Application for Incident or Changes table). 

I hope this makes sense and helps you forward. It seems you have a complicated model and you will need to use a lot of USERELATIONSHIP()  because on your date table you will create multiple inactive relationships with your fact tables. For example, you can create an active relation on CreatedOn and inactive relations on ReportDate.

Let me know if you have any questions 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[EricHulshof]

This actually makes alot of sence! Thanks alot, here, take this kudo 🙂