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!
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.