Forum Discussion

lazarys's avatar
lazarys
Regular Visitor
4 years ago
Solved

Consolidating multiple SAP BW queries, with parameters from excel sheet into a single resultset

I have a report that runs a number of different SAP BW queries, (each with a distinct but varying set of parameters, some as "*" wildcards). I have put the input requirements (Lets call that table "REQUIREMENTS") into an excel table. To that I have added a column that based on the parameters, indicates which of four BW Queries should be run with the indicated parameters (GL303_BASE,GL303_DT,GL303_ORDERS, and GL303_DT_ORDERS.) I have also created four PowerBI functions that return the resultset of each query (see above), given the parmeter set. I have not found how to loop through the driving "REQUIREMENTS" table (which has multiple distinct rows for each of those query methods), calling the appropriate function for each, and then consolidating them into a single table resultset. Does anyone have an example showing this kind of thing?

Need Help
Tutorial Requests
  • lazarys's avatar
    lazarys
    4 years ago

    Hi Stephen -

    I hadn't tried merge-queries, because I was still trying to get an excel spreadsheet to drive the multiple dynamic queries to SAP business warehouse. Another part of the issue was the dreaded "formula firewall".  I eventually solved it by separating the connection portion of the datacube from the retrieve data portion, putting the retrieve data into a custom function that returned a dataset depending on what parameters were passed. I was then able to read the requirement excel spreadsheet, and add a column invoking the custom function for each line with the specific parameters and get that table back for each line. Finally using Table.Expand TableColumn to blow the list up to all the records in one dataset worked very neatly.  

     

    Thank you for looking into this for me.

     

2 Replies

    • lazarys's avatar
      lazarys
      Regular Visitor
      4 years ago

      Hi Stephen -

      I hadn't tried merge-queries, because I was still trying to get an excel spreadsheet to drive the multiple dynamic queries to SAP business warehouse. Another part of the issue was the dreaded "formula firewall".  I eventually solved it by separating the connection portion of the datacube from the retrieve data portion, putting the retrieve data into a custom function that returned a dataset depending on what parameters were passed. I was then able to read the requirement excel spreadsheet, and add a column invoking the custom function for each line with the specific parameters and get that table back for each line. Finally using Table.Expand TableColumn to blow the list up to all the records in one dataset worked very neatly.  

       

      Thank you for looking into this for me.