Log In
Register
Home
April 15, 2021
Offline
I have been watching videos and reading up on relationships (one to many/many to many) and have not found any specific information regarding table creations per se'. Of course, I'm aware of transforming data when bringing data into power query, but I've found in a few instances, I run into relationship issues, and it got me thinking about dimension tables for slicing and dicing my information. My reporting is not traditional sales information but procurement information for my company (I'm in procurement), and while the information I find is helpful, I haven't seen a lot of videos, etc., slicing and dicing procurement type info.
My main question is when you have a large data dump (many columns) would I be correct in assuming this would be the fact table? But within power query, can you transform the data into smaller dimension tables to use as my filtering options? I'm starting to use Power BI as well, so I'm looking to expand my reporting
For Example, I have procurement data from column A through AJ. This contains program information, supplier information, organization information, procurement staff, etc.
My assumption is that all data would be my fact table, but from that I would have separate dimension tables for Program, Suppliers, Organization, and ProcurementStaff. Is that thought process, correct?
I would then create relationships "one too many". Also, this purchasing info has hundreds of thousands of rows.
if you can create your dim tables from the main fact table would it be preferred to create those by pulling that from the source to create or just as easy to transform it?
July 16, 2010
Offline
Hi Sid,
You're on the right track. Although, just because a table has many columns doesn't mean it's always a fact table. Facts are your transactional data, where as dimensions are more static e.g. customer details, supplier details etc.
The classifications in the example file are correct, however, you need to retain a copy the identifier from each dimension table in the fact table so that you can create relationships between the two tables.
Please consider taking my Power Pivot & DAX course to continue your learning.
Mynda
1 Guest(s)
