Query optimization

[Markzolotoy]

I have a transaction table with about a half of a million of records. I will need about 5-6 tables based on that data. I am afraid that creating a query for each table won't be as efficient as having one query and filter more tables out of it. Any recommendation?

Thanks

[lbendlin]

Sounds like you are talking about an aggregation mode where the summaries and dimensions are connected via import and the fact via direct query?

[lbendlin]

Usually fact tables contain the things that get measured/computed, and dimension tables contain the things that you filter or group by.  You can choose a spot somewhere between full denormalization (one fat table) and full normalization to the 6th form - both of the end points are likely counterproductive, but somewhere in between is your sweetspot.

Keep in mind there is no such thing as a free lunch - you still have to spend your transform effort somewhere,  at the cost of performance , network bandwidth, or storage space (or all three) 

[Markzolotoy]

@lbendlin  All are very good points. What I am trying to understand if I already processed my fact table into some summaries and dimension tables at the initial queries stage why would I also load the fact table?

[Markzolotoy]

@mahoneypat That's what I am getting to too. One question though. Why would I need my  facts table? I can create dimensions and summaries tables ahead of time by querying it and then just forget it.

[mahoneypat]

If you don't need your fact table to do analysis on, it is the right decision to not load it.  However, you may want to do additional analyses later and/or pass this off to someone else to maintain.  But you can add it back later if needed.

Pat

[Markzolotoy]

@mahoneypat I am also not sure about Reference tables because some of my queries have a pretty complex SQL code that I am not ready to convert to DAX. So, where the actual table definitions are going to be?

[mahoneypat]

You would not need to change your SQL, and I am assuming you would bring in your half million rows with your SQL and then create a fact table and mutliple dimension tables from that (a good practice).  All your tables would be created in the query editor and no DAX tables would be needed.  Even if you uncheck enable load on the original table, it would still be included in the refresh (as the other queries that are loaded would reference it and trigger its refresh too).

Pat

[mahoneypat]

Do you plan to create a star schema of tables from your original table?  A good idea if so.  You can create your original query, then right click on it and choose Reference for each of your new tables, so you are hitting your source only once.  You can then uncheck "Enable Load" on your original query.  Also make sure you uncheck the box in the options to "Enable Parallel Loading of Tables".  You'll want that turned off so that each query doesn't repeat the original query.

Pat

[lbendlin]

Sadly "disable parallel load" does nothing on the service.. it's a sham.

[Markzolotoy]

@mahoneypat Will data be refreshed if the original query is not loaded nymore?

[Markzolotoy]

@lbendlin The transaction table is not wide. Additional tables are summaries based on that table. There will be a few dimension tables for slicing of these summaries. Also in terms of filtering. Would it be easier to slice data working with calculated tables?

[lbendlin]

That is a relatively small table. How wide is it? Will your derived tables use all columns or only small subsets?