Modelling many fact tables

[vikrantarora25]

Hi PBI Folks,

I have an ecommerce SQL relational model similar to one shown in the image, there are many fact tables (Website sessions, page views, orders, order items, order refunds) and only 1 dimensional table products.

I am trying to create a PBI model and need your help to make a suitable model. Here's what I planning to do:

1. I have made a date dimension table in SQL and will bring that into PBI. Since my data is timestamp type (pageviews, sessions, orders) so to faclitate hourly & time of day (morning, evening etc.) analysis I will create another dimension table which will have hours from 0 to 23 & time of day based on hour. I have decided against having an hour column in Date dim table because that will make it 24 times larger.

2. Secondly I plan to merge orders & order items table which will have Order item ID as the P Key, so bascially truning 2 fact tables into 1

After above I am confused, I have 4 fact tables 1- orders_all (which I derived by mergin orders & order items tables),  2- sessions,  3-Page views and 4- order_item_refunds. And sessions & page views table serve as fact & dimension types both since they serve as dimenions for orders table. 

Now my question is: is it okay to have relationships between fact tables? In my case I can see these relationships between fact tables

1. Sessions & page views  via session_id

2. Sessions & orders via session_id

3.  Orders_all (derived by mergin orders & order items tables)  and order_item_refunds via order_item_id

Any suggesrtions or reference URL for this kind of modeling (many fact tables) in PBI will be highly appreciated!

Thanks & Regards

Vikrant

[amitchandak]

@vikrantarora25 , you should create separate date time column and join them with date and time table

Date = [datetime].date

time =[datetime].time

https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

You should have the fact that combines session and pageviews for required measures and session as a dimension too

[vikrantarora25]

Thanks Amit for your kind reply.

1. I already have a date tbl which connects with all my fact tables thru a date key (only date not time). I have extracted date key (with date only) from the created_at (timestamp) columns in all my fact tables & used it to connect with my date tbl.  

2. I don't have datetime column in my date tbl because the  most granular time dimension I need is Hour (not minutes or secs) and I have a created seperate tbl 'Hour' which has values from 0 to 23 & another column for Time of the day (early morning, late morning, afternoon etc. based on hour value). And I connect this hour table with all my fact tables through hour column derived from created_at (timestamp) columns in fact tables.

My model has fact tables connected to each other and I assume that shouldn't be a problem. I want to make sure I can filter correctly all fact tables from various dimensions. Can you have a look at my model & relationships and let me if any suggestions.

Thanks & Regards

Vikrant