Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |