none
Loading data from SAP BW to Azure data factory

    Question

  • There is a project that has started to implement Microsoft Azure. I would like to confirm if the data flow that the developers are using is correct which is

    1) From data factory extract data from SAP BW to a flat file in Azure.

    2) the flat files are stored per day.t

    3) the flat files are then used to upload to Azure data warehouse

    4) The data is imported into PowerBI(not direct query) apparently due to limited features that can be performed in DAX.

    The other issue is in the data factory.  You cannot select the BW dimension properties(BW attributes) so therefore cannot select the key and a property from a dimension . for example a vendor no, name and address

     

    Due to the MDX that ADF generates we get memory issues in SAP.  We create our own MDX through a long process of generating a universe and then upload to ADF which then works fine.

    below is the steps followed and the syntax from ADF and a universe to see the difference

    thanks

    Heather

    ADF – copy

    SAP BW

    Select bex query

    Syntax from ADF

    SELECT

        { [Measures].[006EI4QHBAHHKDEONV0YZ3GGF] } ON COLUMNS,

        NON EMPTY

        { [0COMP_CODE].[LEVEL01].MEMBERS,

        [0INV_REASON].[LEVEL01].MEMBERS,

        [0IM_SIZE].[LEVEL01].MEMBERS,

        [0PRIORITY].[LEVEL01].MEMBERS,

        [0PROG_DEF_S].[LEVEL01].MEMBERS,

        [0PROJECT].[LEVEL01].MEMBERS,

        [0PS_LEVEL].[LEVEL01].MEMBERS,

        [0PS_PRJTYPE].[LEVEL01].MEMBERS,

        [0PS_APPLNO].[LEVEL01].MEMBERS,

        [0PS_RESPNO].[LEVEL01].MEMBERS,

        [0STATUSSYS0].[LEVEL01].MEMBERS,

        [ZBLDAT].[LEVEL01].MEMBERS,

        [ZBLDAT1].[LEVEL01].MEMBERS,

        [ZPOSID1].[LEVEL01].MEMBERS,

        [ZPRSN2].[LEVEL01].MEMBERS,

        [ZVORGA].[LEVEL01].MEMBERS,

        [ZZACTIVE].[LEVEL01].MEMBERS,

        [ZZPROFILE].[LEVEL01].MEMBERS,

        [ZZSTORT].[LEVEL01].MEMBERS,

        [ZTECODT].[LEVEL01].MEMBERS } ON ROWS

    FROM 0WBS_ELEMT/Z_0WBS_ELEMT_Q001

    Error

    Error in SAP

    Process in SAP UNIVERSE/WEBI

    1. Create a universe

    Please note the attributes/properties we need to select( highlighted in yellow). 

    With ADF you can only select level 01 circled below

    Pass through WEBI

    MDX FROM webi PASSED TO ADF and works. The difference seems that ADF does not allow you to select dimension properties.  The different in syntax highlighted below.

    SELECT

     { [Measures].[006EI4QHBAHHKDEONV0YZ3GGF] }

     ON COLUMNS ,

    NON EMPTY

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    ( CROSSJOIN

    (

    [ZZSTORT].[LEVEL01].MEMBERS ,

    [ZZACTIVE].[LEVEL01].MEMBERS ),

    [ZVORGA].[LEVEL01].MEMBERS ),

    [ZTECODT].[LEVEL01].MEMBERS ),

    [ZPRSN2].[LEVEL01].MEMBERS ),

    [ZPRNM].[LEVEL01].MEMBERS ),

    [ZPOSID1].[LEVEL01].MEMBERS ),

    [ZBLDAT].[LEVEL01].MEMBERS ),

    [0STATUSSYS0].[LEVEL01].MEMBERS ),

    [0PS_RESPNO].[LEVEL01].MEMBERS ),

    [0PS_PRJTYPE].[LEVEL01].MEMBERS ),

    [0PS_LEVEL].[LEVEL01].MEMBERS ),

    [0PS_APPLNO].[LEVEL01].MEMBERS ),

    [0PROJECT].[LEVEL01].MEMBERS ),

    [0PRIORITY].[LEVEL01].MEMBERS ),

    [0INV_REASON].[LEVEL01].MEMBERS ),

    [0IM_SIZE].[LEVEL01].MEMBERS ),

    [0COMP_CODE].[LEVEL01].MEMBERS ) ) 

    DIMENSION PROPERTIES

    [0COMP_CODE].[20COMP_CODE],

    [0COMP_CODE].[50COMP_CODE],

    [0IM_SIZE].[20IM_SIZE],

    [0IM_SIZE].[40IM_SIZE],

    [0INV_REASON].[20INV_REASON],

    [0PRIORITY].[10PRIORITY],

    [0PRIORITY].[20PRIORITY],

    [0PROJECT].[20PROJECT],

    [0PROJECT].[50PROJECT],

    [0PS_APPLNO].[20PS_APPLNO],

    [0PS_APPLNO].[50PS_APPLNO],

    [0PS_LEVEL].[20PS_LEVEL],

    [0PS_PRJTYPE].[20PS_PRJTYPE],

    [0PS_RESPNO].[20PS_RESPNO],

    [0PS_RESPNO].[50PS_RESPNO],

    [0STATUSSYS0].[20STATUSSYS0],

    [0STATUSSYS0].[50STATUSSYS0],

    [ZBLDAT].[2ZBLDAT],

    [ZPOSID1].[1ZPOSID1],

    [ZPOSID1].[2ZPOSID1],

    [ZPRNM].[1ZPRNM],

    [ZPRNM].[2ZPRNM],

    [ZPRSN2].[2ZPRSN2],

    [ZTECODT].[2ZTECODT],

    [ZVORGA].[2ZVORGA],

    [ZZACTIVE].[1ZZACTIVE],

    [ZZACTIVE].[2ZZACTIVE],

    [ZZPROFILE].[1ZZPROFILE],

    [ZZPROFILE].[2ZZPROFILE],

    [ZZPROFILE].[5ZZPROFILE],

    [ZZSTORT].[1ZZSTORT],

    [ZZSTORT].[2ZZSTORT]

     ON ROWS

    FROM

    [0WBS_ELEMT/Z_0WBS_ELEMT_Q001]

    Using the above syntax in ADF it works

    Wednesday, July 18, 2018 5:00 PM

All replies