cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Markzolotoy
Post Prodigy
Post Prodigy

Query optimization

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

12 REPLIES 12
lbendlin
Super User III
Super User III

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
Super User III
Super User III

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) 

@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
Post Prodigy
Post Prodigy

@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.

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Markzolotoy
Post Prodigy
Post Prodigy

@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?

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

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

Markzolotoy
Post Prodigy
Post Prodigy

@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
Super User III
Super User III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors