To load into data model or not

[FlowSnake]

Dear all,

Another question I can't seem to really find an answer to.

I've got some queries based upon local files and external large data sets through ODBC.

In power Query I delete unnecessary rows, do some calculations in added columns, merge various tables to enrich the local file with the data I want, and then load the one query which totals the wanted dataset as a flat table into Excel.

Obviously I create a connection only, however, should or should I not load all the data into the datamodel? I'm not creating any pivot tables from this data, so is there any use to load it into the data model?

Is there any use for the data model if you don't plan to use a pivot table?

[Olly]

You can use the data model to create relationships between tables, and create Measures (custom calculations); then you can report on these using Pivot Tables / CUBE functions.

If all you need to do is materialise your queried data to a table, then no, there's no value in loading to Data Model (but it does make me wonder what you ARE doing with it!)

[FlowSnake]

Hi Olly,

Thanks for your answer. Well, the queried data does get merged, filtered, etc, they contain 100.000's of rows, so loading it directly into Excel is a bad idea.

I suppose there's a hidden hint/question in your last part. Is that regarding the data in this particular case, or with data models in general?

Or to put it differently, is it a mistake from my part thinking a flat table from queries and merges after Power Query is a solid solution, instead of making use of power pivots, DAX, and data models?

I see it as follows, use PQ to setup the data, then either use Excel or Power Pivot to further work on this. I acknowlegde it's probably a shortcoming that I know my way 'faster' in Excel than in Power Pivot, so I'm more likely to use flat tables after all. Is this what you're hinting at?

[Olly]

Yes. In general - materialising all the data on a worksheet is usually not the most efficient way of handling it.

What do you actually DO with the data next, after importing, merging, transforming, etc? It's rare that a big flat table is the actual required output - it's far more common that users want to aggregate / interrogate that data in some way, and in that case, using the data model is nearly always more efficient.

Hard to give specific advice without a specific workbook / use case, though

[FlowSnake]

Hi Olly, It's hard to really post examples since it's a data jungle.

To actually tell what I am doing; We've multiple businesses run by different people. Our business is a wholesaler, and the other one is a webshop B2C.

Now the webshop really has no data integrity, it's a mess. They've multiple GTIN/EAN coupled per SKU, whereas we actually have one SKU per GTIN/EAN.

So I need to join these together to add their information from multiple data tables to our productsheet, from where I can extract information from their online scrapers etc.

But I just tried to use a DAX formula in the data model, as I thought it was PIVOT only, and even in a flattable, it works. So I start to see where my short comings actually are, I should stop making unnecessary joins in PQ, and indeed make relationships.

I suppose I should use PQ to really cut down on unnecessary data and model the data to be able to relate all the tables in the data model. This is what I was doing in PQ instead.