I have a customer table that I load. All customers are held in one table. My Sales table has two links to customers
Purchase Customer
Shipped Customer
In the customer table there is an identifier to show Purchase or Shipped or Both
What is best practice for modelling?
Would you create database views as this is possible?
If not would you use a direct query to seperate the two differnet types of customers?
It just seems plain dumb to double load the data.
Oh and the two different dimenions in the model have in the past had different column names but I am not convinced this is needed.
So the CustomerName in the Purchaser table becomes PurchaserName
And the CustomerName in the Shipped table becomes ShippedName
How you set up your Model will depend on what you want or need to showcase in your report.
You can use Power Query to Extract, Transform and Load your data, so it’s suitable for reporting.
From what I understand you have something like this. On the left a Sales fact table with Purchased by and Shipped to Customer ID’s. On the right the Customer dimension table.
Depending on what you need to report you can create active OR inactive relationships between Sales and Customers. Much like all the scenario’s you can find within Enterprise DNA dealing with multiple dates in fact tables.
Where you “activate” inactive relationships with USERELATIONSHIP or create virtual relationships using TREATAS if there is no relationship present.
I had ruled out using Active and Inactive relationship as we want to view and Analyse the sales by Purchased or by Shipped. The two dimensions may have different attributes but there are core details that are shared.
Currently there is a query that gets the data for customer into Power BI. Then I have two tables that the source points to the initial query. I am hoping this keeps the size of the model lower and will allow the users to be able to use Shipped or Purchased dimensions.
I hope to see two dimesions in the model but keep the size of the model small.
Thats probably asking for the world. But if you dont ask you dont get.
Thanks
E
Nothing is stopping you from creating a duplicate Customer dimension table, it’s just that IMO most of the time you just don’t need to.
(and it would have been helpful if you’d stated that thought process in your initial request…)
BTW it’s still not clear to me why you would rule out active and inactive relationships over a single dimension table. Is that because you only design the model and others built measures on top of that?
Is there a need to show Shipped vs Purchased side by side? If so how will you achieve that if they don’t share the same dimension??
If I knew Power BI better I might be able to explain this better. I dont want to load the data twice or potentially three times. I think the shipped and purchased would be used for further analysis - seperately or combined and not sure as I dont have a lot of experience with active / inactive. I could see inactive / active for measures but dont understand how this would halp to create the dimensions.
If i relate it to Analysis Services I would want two different dimensions rather than hierarchies because I think thats how I see the reports and analysis going. i.e they will want do ad hoc analysis by both dimensions.
As I am trying to design a data model without the true picture of what people want it is just a guess.
E
Quick question. Have you worked through the complete Data modelling course yet? If not I highly recommend doing that first.
A good model is designed and optimised for a specific task. Finding out what the requirements and objectives are is key. Try to involve your stakeholders, report developers and whomever is involved in your organisation. So you can build an effective model that that everyone understands.
Hi @ells. You shouldn’t need to double-load the Customer data … just use “Reference” to 2 copies of the [Customers] table in Power Query, one each [Purchase Customer] table and [Shipped Customer], then disable load in the [Customers] staging table; the data will be loaded once but used twice. Hope this helps, Greg.
Did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
I have used the reference solution provided and the file size has reduced. I have not fully investigated the active inactive relationship as runing through the data model course as fast as i can.
It’s great to know that you are making progress with your query @ells. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
