cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Modelling many fact tables

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

Capture.JPG

 

 

2 REPLIES 2
Super User IV
Super User IV

@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

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

VikrantModel so far.JPG

Relationships.JPG

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors