none
Load Data from SAP BO Universe into Excel with Power Query RRS feed

  • Question

  • Hi All,

    i'm very new in the Microsoft Excel Power Query Addon. I have the following question and would hope that someone can help me. I find the connection to SAP BO universes is very interesting. I can query measure and dimension from universes. The dimension and measure i want to query is very large. Is there any possibility to set or use filters to reduce the query in Power Query or SAP Information Design tool?

    Regards,

    Arne


    Thursday, July 24, 2014 10:06 AM

Answers

  • Hi Avbar,

    When connecting to your SAP universe in Power Query, you can select specific fields (dimensions/measures) in the Navigator task pane. Then, instead of clicking Load, you can click Edit and go into the Query Editor environment where you can apply transformations and filters to your query. You can also access the Query Editor after landing your query in the worksheet, by clicking "Edit Query" in the Query ribbon tab or via the Workbook Queries task pane.

    Thanks,
    M.

    Monday, August 4, 2014 7:10 PM

All replies

  • What do you mean by reduce the query? 

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, August 2, 2014 2:30 AM
    Owner
  • Hi Avbar,

    When connecting to your SAP universe in Power Query, you can select specific fields (dimensions/measures) in the Navigator task pane. Then, instead of clicking Load, you can click Edit and go into the Query Editor environment where you can apply transformations and filters to your query. You can also access the Query Editor after landing your query in the worksheet, by clicking "Edit Query" in the Query ribbon tab or via the Workbook Queries task pane.

    Thanks,
    M.

    Monday, August 4, 2014 7:10 PM
  • Hi Ed Price and Miguel Llopis,
    thanks for your fast answers. The Problem which i have is that the dimensions are very large (a lot of datasets >150k). I want to reduce the returned datasets on universe level. For example i have a dimension factory which contains factory 1, factory 2, factory 3, ... so on. When i select the dimension factory all datasets are returned. It is possible to select a filter in Power Query on universe level to reduce the returned datasets for example factory 1+2? Or is it possible to reduce the returned datasets by edit the universe in the SAP Information design tool and insert a where-clause in the dimension factory?

    Regards,

    Arne

    Tuesday, August 5, 2014 6:11 AM
  • Hi Arne,

    Power Query adds common filters you use in the editor to the query that it sends to the service. When it does so, not all the data is requested. It is possible to build filters in Power Query that cannot be translated fully to a query to the SAP BO service. In that case, Power Query will pull the data it needs into memory and filter it on your machine. You should not worry about things like running out of memory as running your query is isolated in a separate process. However, it is possible that this causes your query to take a long time to complete. Note that there is a configurable limit of the amount of data returned by the SAP BO service. I believe the default is 5000.

    For the specifics of how to apply built-in filters to dimensions in an SAP universe, you'll have to consult SAP documentation or forums.

    Tristan

    Tuesday, August 5, 2014 6:57 PM
    Moderator
  • Hey Tristan,

    thanks for your fast answer. For my understanding if i select the Dimension factory in Power Query on the first time all Data is load. In the Power Query editor i set a filter for example only factory 1. Subsequently i load the filtered data for factory 1 into Excel. If i refresh the query into Excel sheet Power Query try to translate the query and will only inquire the data of factory 1 from the Service? If its work only the data for factory 1 is returned. In the other case all data is load from the Service (factory 1,factory 2,factory 3) and filtered on the machine.

    The background is that i have different users which want to get data out of a BO universe (Live production data) in MS Excel. The user only needs different areas for example different factorys. So if they set the filter in Power Query editor the database would not unnecessary charge with requested data which is not need. I hope you understand my problem ;).

    Regards,

    Arne

    Wednesday, August 6, 2014 5:37 AM