June 29, 2017 at 6:00 pm #8039
- Started: 5
- Replies: 9
- Total: 14
I am approaching this from an Excel point of view, but I think it also applies to Power BI.
I am currently going though Matt Allington’s “Learn to Write DAX” book and I am finding the data modeling issues very frustrating. Power Pivot seems to be severely lacking in data modeling capabilities. Just a few examples:’
- Filtering in Data View does nothing for the pivot tables. It is purely visual in data view, which can be useful for debugging or looking at things, but it doesn’t actually change results.
- If you import data into Power Pivot from Access directly, you can easily edit the properties of tables in the Table Properties view.
- If, however, you import a query (where some data modeling has already happened I presume), then you cannot edit easily. You are forced to be in “Query Editor” mode and can only type out SQL statements. Even if you “Preview and Filter” when importing a query and edit data there with filters, the result is a SQL statement only and you cannot easily edit it again unless you speak SQL.
- There is no way to document why certain modeling steps were done.
My workflow would be to first import everything through Power Query (Get & Transform) first. Superb editor and filtering capabilities, and from there load it into the data model, and then report the heck out of it with Power Pivot. A few advantages I can see:
- Any and all filters done in PQ are recognized in the data model, so if you filter for 2001-2010 for in PQ, the data model only gets 2001-2010. It doesn’t matter where PQ got the data – an Access table, Access query, SQL Server table, or whatever, it always works the same way.
- PQ can force joins in a certain way. PP always does a left join it seems, so if the fact table has more data than a dim table (say you have item numbers sold in the fact table not in the dim table somehow – because stuff happens or because they are stored in different systems or whatever) in PP you will always get the extra records with no related dimension fields and without checking for it with DAX formulas to filter those out each step of the way. In PQ, you just force the join to either be where everything equals, or where all records from the DIM table are allowed, but only FACT data where it exists in the main table. Or I can do Anti-joins or whatever I want.
- PQ has easier and faster data modeling tools like trim, add column by example, all of the duration/time comparisons, etc.
- PQ is self documenting as all modeling steps for all tables/queries are listed in step, and I can add comments either in the UI or directly in the M-Language code.
I understand PP came out before PQ, so early adopters cut their teeth with PP data modeling, and I also understand I need to know how PP datamodeling works because I will inherit projects that have PP data modeling done vs done in PQ, as I work through examples in the book, why would I ever start with PP for data modeling?
You must be logged in to reply to this topic.