Forum Discussion
Dataflow issue
It is good if you follow e.g. star schema in your model. For example all dimension tables like Date should be in single relationship to your fact table/s (one-to-many)
Ok i just remodeled it to look like this, using the CYBS TR table and PDE 17 table both as fact tables. is this allowed in a star schema? and it wont let me select anything other than M*M. any ideas on that?
For each fact table start by describing the "grain", eg what does a single row represent and what describes it.
For a sales table the grain might be a product, sold to a customer on a specific date. Eg ProductID, CustomerId and Date describe the row. You then have Product, Customer and Date dimensions.
With multiple fact tables you want to share those dimensions (these are called conformed dimensions).
If you can describe the rows of your fact tables we can then help suggest the dimension tables you need.
You might find this useful:
https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/
Much appreciated for the link. I just signed up.
my cybs Tr table has one row that represents a single transaction for a customer. The PDE17 file, represents a refund on an order that i need to reference to the CYBS TR table. Each row in both tables represent a single transaction. Both of these files I pull directly from my vendors site with the full months worth of data.
My CYBS TR data has 316 columns for one table and PDE 17 has 48 columns.
I'm assuming most of those 100s of columns describe the transaction.
You want to use power query to split them into logical groupings in separate dimension tables.If all the data relates to a transaction I'd be tempted to use power query to merge the data on transaction id and end up with a single fact table with your dimensions coming off it.
However I still think something is missing from the description of the data. In your latest model you've got a many to many relationship between the two tables which would suggest that the same transaction appears multiple times in both tables or you're joining on the wrong field.