Forum Discussion

Enigma's avatar
Enigma
Icon for Helper III rankHelper III
6 years ago

Redundant tables in model

Hi,    I have several tables in my report that are redundant once the data is transformed in Query Editor. And I am looking for ways to minmise the performance impact   Here's the situation: The...
Need Help
DataVitalizer's avatar
DataVitalizer
Icon for Super User rankSuper User
6 years ago

Hi Enigma 

 

I have couple questions to make it clear to me so I can help you:

1- Do all the 10 tables contain the same columns ?

2- Do yu really need all those columns data ?

3- Are there any rows you can exclude ?

 

Does it work ? Mark it as a solution 

A kudos would be appreciated 🙂 

 
Enigma's avatar
Enigma
Icon for Helper III rankHelper III
6 years ago

hi DataVitalizer 

 

  1. Yes. All 10 tables contain same columns. 70 columns in each table
  2. A few columns that are not needed, I have removed in Query Editor, as I have mentioned in my intial post.
  3. I cannot exclude any rows. It's all production data.

The 10 tables come from 10 people from 10 departments. Earlier each department had it's own report so it worked well.

Now, our clients need a common report for all 10 departments. Hence, the need to merge data in a single report file. 

 

I want it to make as simple as possible for them to upload their reports. All data manipulation is to happen after the data is pulled in Power BI.
So, the intent is, they will have to only dump their report in the excel without doing any changes themselves; Power BI will pull all the data, make transformations and build the common report. This is a monthly thing, and people come and go; so I want to eliminate any mistakes and make it as straightforward to dump data as possible.

Thanks for taking time to read and willing to help out. Truly appreciated!

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Having 10 identical tables as a your source is fine if it is best for your business.  First thing to do is to right click on each of those 10 tables and uncheck "Enable Load".  That will keep them from loading into your model., but they will still be used in refresh.  See how much that improve performance, but if all your analysis is only on table 11, it may not help much.  Hopefully it will.

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Enigma's avatar
      Enigma
      Icon for Helper III rankHelper III
      6 years ago

      Thanks mahoneypat 

       

      I wasn't aware of the Enable Load option. There's also a 'Include in report refresh' toggle option.

      I hope turning those off won't stop loading data in future iterations of data pull schedule. 

      But thanks for suggesting.

      I have a question - Does append queries happen after or before all applied steps are executed?

      If it's after, then I can remove unwanted columns from each of the 10 tables and not just the 11th.

      • mahoneypat's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        Don't uncheck "Include in Report Refresh".  That is required for your next refresh.  Just uncheck Enable Load.

        Regards,

        Pat

         

    • Enigma's avatar
      Enigma
      Icon for Helper III rankHelper III
      6 years ago

      Thanks mahoneypat - unchecking Enable Load seems to be the right option at the moment. There's a slight improvement.

  • DataVitalizer's avatar
    DataVitalizer
    Icon for Super User rankSuper User
    6 years ago

    Hi Enigma 

    Now you can save couple steps and work on 1 table from the beginning

    1- if you connect power bi to your workbook you will have to check all sheets and import them, this will display 10 tables, what I suggest you to do is to import only one sheet 🤔, Yes only 1 sheet.

    You will get then the below preview

    2- in the steps tab select the 1st step and you will able to expand data from all sheets without loading all sheets or append them

    3- in this table you will need to add couple transformations to clean rows and keep the needed columns 

     

    Does it work ? Mark it as a solution 

    A kudos would be appreciated 

    • Enigma's avatar
      Enigma
      Icon for Helper III rankHelper III
      6 years ago

      Thanks for suggesting DataVitalizer  - I will try it out and get back if it worked.