Using SAP Lumira and SAP Business ByDesign OData for Analytics you can combine analytical data from multiple ByD systems (and other sources) into a global dashboard to perform gross-system analytics like global spend or global sales analysis.

 

In this blog I connect SAP Lumira with two SAP Business ByDesign systems using ByD OData for Analytics and assemble a global sales volume dashboard.

In other words two ByD reports from different ByD systems are combined into a single global analytics view:

Lumira_result_1.gif

 

 

My Scenario:

 

I have two ByD systems to run sales subsidiaries in Germany and US and I would like to setup a global sales volume dashboard with combined data of both ByD systems.

To avoid unnecessary long runtimes and superfluous data in Lumira, I utilize ByD analytics capabilities and aggregate data within ByD before it is transferred to Lumira.

 

 

Step 1: Prepare analytical reports in the ByD source systems

 

Lumira will retrieve ByD data via the ByD OData for Analytics interface. Of course you can do most of the selection and filtering setting OData parameters in Lumira as well, but the configuration in Lumira becomes much easier if you prepare the data sources in ByD.

Hence, in the following I assemble custom ByD reports tailored to the Lumira scenario having the usage via OData in mind.

  1. Work center Business Analytics, view Design Reports:
    1. Copy ByD standard report “Sales Order Volume” via “New” >> “Report as Copy”
    2. Select key figures
    3. Select characteristics: I reduced the number of selected characteristics to the information I really need in Lumira.
    4. Remove the “Default Values” in step “5 Define Variables” via bottom “Maintain Default Values“.
      The ByD report default values always apply using OData, regardless which filter criteria you are defining in the OData URL later on.
      If you keep the default values, they restrict the data that can be transferred to Lumira. On the other hand using relative default values you can refine user authorizations (for example using a default value “my accounts”) or pre-select data (for example setting a default value “current fiscal year”).
    5. Assign the new report to a suitable work center view (for example some view in work center Account Management). All business users with access to this work center view will have access to this analytics report; this applies as well if I access the report via OData.
    6. Get the report ID: Open the new report and click on Details: on the Technical Information popup you’ll find the Report ID, which you will refer to in the Lumira OData connector. Example custom report ID: Z050C6681A68E961A6B7C71
  2. Check report data
    1. Open the work center to which you assigned the report. Open the custom report and assemble a report view with all relevant fields.
    2. Check if all fields which you plan to use in Lumira are filled. For example check if the state is filled for all accounts to be able to assemble geographical heat maps in Lumira (check your account master data if any data is missing – you can navigate directly from the report line to the account master data by clicking on the account name or ID)
  3. Create similar ByD reports in all ByD systems.
  4. Make sure you have a business user with access to the ByD reports in each system. These users are used for OData access in Lumira.
    Align the language of your OData user in all ByD systems to make sure you extract the data in the same language (click on Personalize >> My Settings).

As result you have well-prepared ByD reports that can be accessed via ByD OData.

Step 2: Check the OData access to ByD and assemble the ByD OData URL

In this step I prepare and test the ByD OData URL. In particular I use the OData parameter $top to overwrite ByD default max. number of records returned and $select to make sure that I load aggregated data from ByD.

  1. Get OData “containers” for your ByD logon user (available work centers for the logon user) and note the “container” (analytics work center view ID) of the work center view to which you assigned the report.
    Example:

  2. Get entity set and entity metadata of your ByD custom report. The metadata contains all relevant information to assemble the ByD OData URL incl. property names, labels and filterable attributes.
    Example:

  3. Assemble and test the ByD OData URL with all required OData parameter:
    1. $select: By choosing the fields to be selected I implicitly define the level of pre-aggregation in ByD before the data is transferred to Lumira.
      In scenarios like this I would recommend to not add document IDs or time stamps to the list of selected fields, because in this case ByD does not aggregate data and Lumira loads all transactional documents. Make sure you select less than 50 fields, because that is the max number of fields ByD OData supports.
      I take the name of the fields from the entity metadata.
    2. $filter: Using the filter parameter I already filter the data on ByD side before transmission to Lumira. Sure I could filter in Lumira as well, but then I loaded already much more data than needed.
    3. $top: By default ByD OData returns at max. 50 records. Using the parameter $top I overwrite this default. Unfortunately the Lumira OData connector does not yet support dynamic paging using the OData parameter $top and $skip, but if you expect high volumes you could create multiple Lumira datasets and combine them afterwards on the Lumira Prepare screen (for example 1st dataset: $top=500; 2nd dataset: $skip=500&$top=500; … 4th dataset: $skip=1500&$top=500).

As result I know the ByD OData container, my custom entity set name, and I assembled the OData parameter string to access ByD in Lumira later on: 

  • OData container/base URL: https://my123456.sapbydesign.com/sap/byd/odata/crm_accountmanagement_analytics.svc
  • Entity set name: RPZ050C6681A68E961A6B7C71QueryResults
  • OData parameter string:
    $select=KCITEM_COUNT,FCITV_NET_AMT_DC,KCITV_NET_AMT_DC,FCITV_NET_AMT_RC,KCITV_NET_AMT_RC,FCITV_REQU_QTY_BU,KCITV_REQU_QTY_BU,FCITV_REQU_QTY_SU,KCITV_REQU_QTY_SU,CIPY_BUYER_PTY,TIPY_BUYER_PTY,CIPY_BUY_CNTCD_N,TIPY_BUY_CNTCD_N,CIPY_BUY_CTYNM_N,CIPY_BUY_REGCD_N,TIPY_BUY_REGCD_N,CITV_FSTREQ_E_DT,CDBA_DISTRCHN_CD,TDBA_DISTRCHN_CD,CIPR_CATCP_N,TIPR_CATCP_N,CIPR_PRODUCT,TIPR_PRODUCT,CIPY_SLS_UNT_PTY,TIPY_SLS_UNT_PTY&$top=2000

You find more details about ByD OData for Analytics in the SCN blog OData for SAP Business ByDesign Analytics.

Please note: The example OData URL above does not have filter parameters, which works for data sources with low data volumes only. If you work with high data volumes, setting appropriate filter options to keep the fetched data volume stable and within reasonable sizes is essential for a sustainable integration scenario. If your requirements exceed the capabilities of the Lumira Generic OData 2.0 Connector, the Lumira Data Acquisition Extension SDK may help to implement more sophisticated requirements.

For more details with regards to ByD OData access to high volume data sources, please check SCN blog OData for SAP Business ByDesign Analytics, section “High Volume Data Sources“.

Step 3: Create Lumira Dataset

In this step I create the first Lumira dataset. Later on I will append further datasets referring to other source systems to this dataset.

  1. Open Lumira Desktop Client
  2. Create a Lumira Dataset: Select from the menu “File” >> “New” and choose the data source “Query with SQL” >> “Generic OData 2.0”
    1. Enter the ByD base URL with container as Service Root URI:
      https://my123456.sapbydesign.com/sap/byd/odata/crm_accountmanagement_analytics.svc (Lumira adds /$metadata to retrieve the metadata itself).
    2. Enter user name, password and http proxy if applicable.
    3. Enter the OData parameter string in field “Custom Authentication Parameter” (not sure if this field was designed for that purpose, but it works – the parameter string seem to be added to the OData URL correctly). This cannot be changed after completing the Dataset!
      /wp-content/uploads/2015/05/lumira_odata_connector_1_714052.gif

      • Remark:
        Lumira doesn’t like spaces and other special characters in the URL, hence I replaced special characters by the corresponding %-syntax. That means
             $filter=CIPY_BUY_CNTCD_N eq ‘SE’
        becomes
        $filter=CIPY_BUY_CNTCD_N%20eq%20%27SE%27

        <space>
        %20
        %27
        ( %28
        ) %29
    4. Check advanced settings and make sure that the Lumira timeout is sufficiently high.
    5. Go to the next screen with bottom “Connect“.
  3. On the Dataset I selected my custom entity in the CATALOG_VIEW tree; observe that the entity is added to the Query.
    1. After clicking on “Preview” I select the fields needed for my Lumira dashboard.
    2. I enter “Global Sales Volume” as dataset name because this will be the dataset to which I will append all other datasets later on.
  4. With clicking on “Create” I complete my Lumira Dataset for the first ByD system.

Step 4: Combine the Lumira Datasets of multiple source systems

  1. Switch to Lumira view Prepare
  2. Add further datasets for other ByD systems (compare step 3)
    • Make sure all Datasets select the same fields from the ByD data source.
    • I named my second data set “Sales Volume US” because it connects to my US ByD system.
  3. I switch to the first dataset which I named “Global Sales Volume” and append all others: Click on “Combine” >> “Append”:
    Lumira_append.gif
  4. Feel free to add and append Datasets from other SAP or non-SAP data sources
  5. Rename measures and dimensions
  6. Create geographic hierarchies
  7. Create time hierarchies

As result I have a combined Lumira Dataset, ready for visualization. By the way in field KCITEM_COUNT I see how many ByD sales order items have been pre-aggregated in ByD for each record in Lumira.

/wp-content/uploads/2015/05/lumira_prepare_ready_714054.gif

Step 5: Define Lumira Visualizations

  1. Switch to Lumira view Visualize
  2. Create visualizations of your combined Dataset. I like in particular the geographical heat map and the pie with depth chart.

Step 6: Compose and share the Lumira Story

  1. Lumira view Compose: Create a new story and arrange the visualizations of step 5
  2. Lumira view Share: Publish your Story to your SAP Lumira Cloud or your SAP Lumira Server

Result:

Lumira_result_2.gif

More information about SAP Lumira:

Lumira homepage: http://www.saplumira.com/

SAP Help: http://help.sap.com/Lumira

SCN Lumira Space: http://scn.sap.com/community/lumira

SAP Idea Place – Lumira space: https://ideas.sap.com/SAPLumira

 

Related information about SAP Business ByDesign:

ByD OData for Analytics: OData for SAP Business ByDesign Analytics.

ByD Help: SAP Business ByDesign – SAP Help Portal Page

 

 

.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Jacques-Antoine Ollier

    Nice blog Knut!

    I am now waiting for a stable connector from the Bydesign team and Lumira will be the future of analytics in ByDesign!!!

    Thank you for your help!

    Best regards.

    Jacques-Antoine

    (0) 
  2. Christopher Buck

    Great article and it indeed opens up new avenues for Data Discovery.  I have been testing this with our in house ByD system and it works but some data set issues as you mention in your blog by using $top

    I have been trying to implement $filter in the Custom Authentication Parameters but when going to next screen and running the select it tells me there is an illiegal character.  Can you tell me if you have succeded in using $filter.

    I have been trying with : $filter=CPROJECT_UUID eg ‘CP1’

    (0) 
    1. Knut Heusermann Post author

      Hi Christopher,

      I replaced in the URL spaces and special characters by the corresponding %-syntax:

      <space>

      %20

      %27

      (

      %28

      )

      %29

      That means

           $filter=CIPY_BUY_CNTCD_N eq ‘SE’

      becomes

           $filter=CIPY_BUY_CNTCD_N%20eq%20%27SE%27

      Then it worked.

      Example:

      $select=KCITEM_COUNT,FCITV_NET_AMT_DC,KCITV_NET_AMT_DC,FCITV_NET_AMT_RC,KCITV_NET_AMT_RC,FCITV_REQU_QTY_BU,KCITV_REQU_QTY_BU,FCITV_REQU_QTY_SU,KCITV_REQU_QTY_SU,CIPY_BUYER_PTY,TIPY_BUYER_PTY,CIPY_BUY_CNTCD_N,TIPY_BUY_CNTCD_N,CIPY_BUY_CTYNM_N,CIPY_BUY_REGCD_N&$filter=CIPY_BUY_CNTCD_N%20eq%20%27SE%27&$top=500

      Best regards,

      Knut

      (0) 
  3. Raphael Branger

    Currently – with version 1.25.1 I’m facing the issue that for the preview it automatically adds $top=10. That’s fine for the preview. For the full dataset it leaves this away but as you mention in this article SBD will return only 50 rows then. Once I add my own $top=100000 for example, the preview doesn’t work anymore. The error message shows that I have now two times the top-Parameter: Once for 10 and once for 100000. And without a successful preview Lumira won’t create the dataset. I also tried to modify the dataset later on – but couldn’t find a way to add new authentication parameters once the dataset was created. I think this worked fine for me before I updated to 1.25.1 (means 1.25.0) But can’t proof it right now. Any ideas on your side? Can you double check if you can still use a valid top-Filter?  Thanks for your help!

    (0) 
      1. Knut Heusermann Post author

        Hi Christopher,

        Depending on the number of fields selected 1 Mio records might be a pretty large xml message size for a single web service transmission. I didn’t test it yet, but the data acquisition extension framework (DAE) might be a good alternative as well to access ByD data sources via OData. DAE would enable you to store the query state for paging and to modify the filter parameter.

        Regards,

        Knut

        (0) 
    1. Knut Heusermann Post author

      Hi Ramana,
      the only workaround I know is using the Extended Parameter when creating the data set (as done in the example above).
      Please address this question in the SAP BusinessObjects Lumira Community to trigger a discussion how to support OData parameter in Lumira’s OData connector.
      Regards,
      Knut

      (0) 

Leave a Reply