SAP BW Data Pull Issue (Using Import Query)

[Anmolgan]

Getting the below error when try to pull a cube into the PowerBI using import connection, can someone explain me what am I doing wrong. 

I have installed the SAP librariers that are required to connect to SAP-BW, is there any way I can perform direct query ??

DataSource.Error: SAP Business Warehouse: No roll storage space of length 1623160 available for internal storage. table blocks.
Details:
DataSourceKind=SapBusinessWarehouse
DataSourcePath=192.12.12.55/00/300
Message=No roll storage space of length 1623160 available for internal storage. table blocks.
Key=TSV_TNEW_BLOCKS_NO_ROLL_MEMORY
ErrorCode=204

[Anonymous]

Hi @Anmolgan 

The reason behind the error is inadequate memory in the SAP BW. To be more precise, when you fetch records from SAP BW into Power BI using Power Query, internally for every dimension in the cube, the system generates sub-queries to fetch the attributes/properties of each dimension from the respective master data tables and gives you a flattened table with hundreds of fields. Sometimes, the BW system runs out of memory to execute all these queries and its results simultaneously and throws the error that you got. 

This can be resolved by the following steps.

1. Reduce the batch size, use implementation 2.0, specify the language.

     Source = SapBusinessWarehouse.Cubes("XXX.XX.XX.XXX", 
                                      "00",
                                      "300", 
                                [LanguageCode="EN", Implementation="2.0", BatchSize=5000]
               ),

    Doing this will help in the reduction of the query result size and probably this itself will resolve your issue.
2. If the 1st step didn't resolve the issue, try reducing the query results by performing the following...
   1. See if you can reduce the number of columns you are fetching from the cube. (Here I am talking about removing unnecessary dimensions).
   2. Fetch the fact table first and include only the keys of each dimension. Don't import all the attributes/properties of all dimensions at this fact table query. Then create separate queries for the properties/attributes of each dimension and create a star schema /snowflake schema inside Power BI rather than importing a flattened table with hundreds of fields from SAP BW.
   3. Remember to follow the first step (Batch size, language, implementation)
3.  Step 1 and Step 2 can be considered as best practices to reduce the query size and load on SAP BW. Now coming down to the error specifically, you can do the following.
   1. Increase the heap memory size in the SAP BW system. 
   2. Google this "abap/heap_area_dia". This is the parameter.
   3. I think the heap memory can be increased using SAP transaction code RZ10. This has to be done by the administrator/basis team.
   4. Inadequate memory was the fundamental reason behind that error. But it's not always possible to increase this parameter as there is a dependency on the hardware configuration of your server. Although this is the solution, try to follow steps 1 & 2 always. Changing the heap memory is your solution, but keep it as a last resort.

Disclaimer: I am just pointing you to the direction. Before touching the SAP BW system or modifying any parameter, you must be very clear about what you are doing and do it only with the help of Basis team or administrator even if you have the authorization for those transaction codes.

[Anmolgan]

Can you elaborate on the below point with some examples? No.1 solution is not helping us to get pass through the errors that we are facing.

1. Fetch the fact table first and include only the keys of each dimension. Don't import all the attributes/properties of all dimensions at this fact table query. Then create separate queries for the properties/attributes of each dimension and create a star schema /snowflake schema inside Power BI rather than importing a flattened table with hundreds of fields from SAP BW.

[Anonymous]

Hi,

For example, if you are gonna pull data from the cube related to purchasing data, I assume you will use the standard cube "0PUR_C01". This cube will have roughly the following fields...

Id	Name	Kind	Data
[Measures].[0CONTR_ITEM]	Contract items	Measure	() as any => ...
[Measures].[0DELIVERIES]	Deliveries	Measure	() as any => ...
[Measures].[0DEL_DT_VR1]	Delivery Date Var. 1	Measure	() as any => ...
[Measures].[0DEL_DT_VR2]	Delivery Date Var. 2	Measure	() as any => ...
[Measures].[0DEL_DT_VR3]	Delivery Date Var. 3	Measure	() as any => ...
[Measures].[0DEL_DT_VR4]	Delivery Date Var. 4	Measure	() as any => ...
[Measures].[0DEL_DT_VR5]	Delivery Date Var. 5	Measure	() as any => ...
[Measures].[0DEL_SCHEDS]	SA schedule lines	Measure	() as any => ...
[Measures].[0EFF_VAL_RE]	Ord. val. (ret.)	Measure	() as any => ...
[Measures].[0GR_QTY]	Actual GR quantity	Measure	() as any => ...
[Measures].[0GR_QTY_RET]	GR qty. of returns	Measure	() as any => ...
[Measures].[0GR_QTY_WDT]	GR qty (WDelTm)	Measure	() as any => ...
[Measures].[0GR_VAL]	GR value	Measure	() as any => ...
[Measures].[0GR_VAL_PD]	GR value pstg. date	Measure	() as any => ...
[Measures].[0GR_VAL_R_P]	GR val.: Returns PD	Measure	() as any => ...
[Measures].[0INVCD_AMNT]	Invoiced amount	Measure	() as any => ...
[Measures].[0INV_RC_QTY]	IRQ (Pstg. Dt.)	Measure	() as any => ...
[Measures].[0INV_RC_VAL]	Invoice Amount	Measure	() as any => ...
[Measures].[0IR_QTY_RET]	IR qty. (returns)	Measure	() as any => ...
[Measures].[0IR_QTY_R_P]	IR qty: Returns (PD)	Measure	() as any => ...
[Measures].[0IR_VAL_RET]	Invoice amount: Ret.	Measure	() as any => ...
[Measures].[0IR_VAL_R_P]	IR val: Returns (PD)	Measure	() as any => ...
[Measures].[0IV_REC_QTY]	Invoiced quantity	Measure	() as any => ...
[Measures].[0ORDER_VAL]	Purchase Order Value	Measure	() as any => ...
[Measures].[0PO_ITEMLNS]	PO schedule lines	Measure	() as any => ...
[Measures].[0PO_ITEMS]	No of PO items	Measure	() as any => ...
[Measures].[0PO_QTY]	PO quantity	Measure	() as any => ...
[Measures].[0PO_QTY_RET]	Order qty. (returns)	Measure	() as any => ...
[Measures].[0QTY_DEV_1]	Qty variance 1	Measure	() as any => ...
[Measures].[0QTY_DEV_2]	Qty variance 2	Measure	() as any => ...
[Measures].[0QTY_DEV_3]	Qty variance 3	Measure	() as any => ...
[Measures].[0QTY_DEV_4]	Qty variance 4	Measure	() as any => ...
[Measures].[0QTY_DEV_5]	Qty variance 5	Measure	() as any => ...
[Measures].[0QUOT_ITEMS]	Number of quotation items	Measure	() as any => ...
[Measures].[0RFQ_ITEMS]	RFQ items	Measure	() as any => ...
[Measures].[0SCH_AGR_IT]	Sched.agmt.itms	Measure	() as any => ...
[Measures].[0TAR_DL_QTY]	Target delivery qty	Measure	() as any => ...
[Measures].[0TOTDELTIME]	Total del.time	Measure	() as any => ...
[Measures].[0WTDDELTIME]	WtdTotalDelTime	Measure	() as any => ...
Measure Properties	Measure Properties	MeasurePropertyFolder	[Table]
[0COUNTRY]	Country	DimensionFolder	[Table]
[0CT_FLAG]	Flag for Contracts	DimensionFolder	[Table]
[0DOCTYPE]	Purchasing doc. type	DimensionFolder	[Table]
[0DOC_ITEM]	BW: Document Item	DimensionFolder	[Table]
[0DOC_NUM]	BW: Document Number	DimensionFolder	[Table]
[0GR_RE_IND]	Goods Receipt	DimensionFolder	[Table]
[0INFO_REC]	Info record	DimensionFolder	[Table]
[0INFO_TYPE]	Infotype	DimensionFolder	[Table]
[0MATERIAL]	Material	DimensionFolder	[Table]
[0MATL_GROUP]	Material group	DimensionFolder	[Table]
[0PLANT]	Plant	DimensionFolder	[Table]
[0PURCH_ORG]	Purchasing org.	DimensionFolder	[Table]
[0REVERSEDOC]	Reverse	DimensionFolder	[Table]
[0VENDOR]	Vendor	DimensionFolder	[Table]
[0VERSION]	Version	DimensionFolder	[Table]
[0VTYPE]	Value type	DimensionFolder	[Table]
[0CALDAY]	Calendar day	DimensionFolder	[Table]
[0CALMONTH]	Cal. year / month	DimensionFolder	[Table]
[0CALWEEK]	Calendar year / week	DimensionFolder	[Table]
[0FISCPER]	Fiscal year/period	DimensionFolder	[Table]
[0FISCVARNT]	Fiscal Year Variant	DimensionFolder	[Table]
[0BASE_UOM]	Base Unit	DimensionFolder	[Table]
[0LOC_CURRCY]	Local currency	DimensionFolder	[Table]

That is about 65 fields. I called them fields, but in reality, they are dimensions and key figures. Mostly likely you won't be using all of these 65 fields in your reporting. So you have to first figure out what are the dimensions and key figures that you will need in your reports and import only the "Keys" of those dimensions. To elaborate it further, please run the following power query in your system and paste the output of this query to a spreadsheet and just count the number of fields that you get from the cube flattened out. That will most likely will be more than 500 fields.

let
ServerIP = "123.123.123.123",
SystemID = "00",
ClientID = "900",
Language = "EN",
BatchSizeSpecification = 25000,
ImplementationVersion = "2.0",
InfoAreaID = "$INFOCUBE",
InfoCubeID = "$0PUR_C01",

Source = SapBusinessWarehouse.Cubes(ServerIP,SystemID,ClientID,[ExecutionMode=SapBusinessWarehouseExecutionMode.BasXmlGzip, LanguageCode=Language, Implementation=ImplementationVersion, BatchSize=BatchSizeSpecification]),
InfoArea = Source{[Name=InfoAreaID]}[Data],
InfoCube = InfoArea{[Id=InfoCubeID]}[Data],
InfoCubeContents = Cube.DisplayFolders(InfoCube),
SelectDimensions = Table.SelectRows(InfoCubeContents,each ([Kind]="DimensionFolder")),
AddedColumn = Table.AddColumn(SelectDimensions,"Expand1",

each
    let 
    Source = SapBusinessWarehouse.Cubes(ServerIP,SystemID,ClientID,[ExecutionMode=SapBusinessWarehouseExecutionMode.BasXmlGzip, LanguageCode=Language, Implementation=ImplementationVersion, BatchSize=BatchSizeSpecification]),
    InfoArea = Source{[Name=InfoAreaID]}[Data],
    InfoCube = InfoArea{[Id=InfoCubeID]}[Data],
    InfoCubeContents = Cube.DisplayFolders(InfoCube),
    DimensionAttribute = InfoCubeContents{[Id=_[Id]]}[Data],
    AddedDimensionProperty = Table.AddColumn(DimensionAttribute,"Expand2",
    each
        let
        Source = SapBusinessWarehouse.Cubes(ServerIP,SystemID,ClientID,[ExecutionMode=SapBusinessWarehouseExecutionMode.BasXmlGzip, LanguageCode=Language, Implementation=ImplementationVersion, BatchSize=BatchSizeSpecification]),
        InfoArea = Source{[Name=InfoAreaID]}[Data],
        InfoCube = InfoArea{[Id=InfoCubeID]}[Data],
        InfoCubeContents = Cube.DisplayFolders(InfoCube),
        DimensionAttribute = InfoCubeContents{[Id=_[Id]]}[Data],
        DimensionProperty = DimensionAttribute{[Id="Properties"]}[Data]
        in
        DimensionProperty
    )
    in
    DimensionAttribute
),
    #"Expanded Expand1" = Table.ExpandTableColumn(AddedColumn, "Expand1", {"Id", "Name", "Description", "Kind", "Data"}, {"Expand1.Id", "Expand1.Name", "Expand1.Description", "Expand1.Kind", "Expand1.Data"}),
    #"Expanded Expand1.Data" = Table.ExpandTableColumn(#"Expanded Expand1", "Expand1.Data", {"Id", "Name", "Description", "Kind", "Data"}, {"Expand1.Data.Id", "Expand1.Data.Name", "Expand1.Data.Description", "Expand1.Data.Kind", "Expand1.Data.Data"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Expand1.Data",{"Description", "Data", "Expand1.Description", "Expand1.Data.Description", "Expand1.Data.Data"})
in
    #"Removed Columns"

Note: Change the server IP, client id etc... as necessary.

After running this query please let me know how many rows are there in the output. I will explain further once you tell me the row count.

[Anmolgan]

But I am using BEx query where I have to put start and end date parameter i order to connect to the cube. Where to right the Power Query in that case?

Is it possible for you to get on a teams/skype call so that I can elaborate further I have 2 support ticket opened with Microsoft PowerBI team but they are of no use and havent provided any resolution to the issue and its been 2 Months.

[Anmolgan]

I am getting the below M-Query when I select all my fields that are needed to build a report, am using start date as 2017 June and end date as 2019 June.

let
    Source = SapBusinessWarehouse.Cubes("192.12.12.55", "00", "300", [LanguageCode="EN", Implementation="2.0"]),
    YBILLGSTM = Source{[Name="YBILLGSTM"]}[Data],
    #"YBILLGSTM/ZYGST_M_QUERY_Q001" = YBILLGSTM{[Id="YBILLGSTM/ZYGST_M_QUERY_Q001"]}[Data],
    #"Added Items" = Cube.Transform(#"YBILLGSTM/ZYGST_M_QUERY_Q001",
        {
            {Cube.ApplyParameter, "[0I_CALMO]", {"[0CALMONTH].[201706]", "[0CALMONTH].[201906]"}},
            {Cube.AddAndExpandDimensionColumn, "[0BILL_NUM]", {"[0BILL_NUM].[LEVEL01]"}, {"Billing document.Billing document Level 01"}},
            {Table.AddColumn, "Billing document.Billing document Level 01.Billing document Level 01.UniqueName", each Cube.AttributeMemberProperty([Billing document.Billing document Level 01], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "Billing document.Billing document Level 01.Key", each Cube.AttributeMemberProperty([Billing document.Billing document Level 01], "[20BILL_NUM]")},
            {Cube.AddAndExpandDimensionColumn, "[0BILL_TYPE]", {"[0BILL_TYPE].[LEVEL01]"}, {"Billing Type.Billing Type Level 01"}},
            {Table.AddColumn, "Billing Type.Billing Type Level 01.Billing Type Level 01.UniqueName", each Cube.AttributeMemberProperty([Billing Type.Billing Type Level 01], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "Billing Type.Billing Type Level 01.Key", each Cube.AttributeMemberProperty([Billing Type.Billing Type Level 01], "[20BILL_TYPE]")},
            {Table.AddColumn, "Billing Type.Billing Type Level 01.Name", each Cube.AttributeMemberProperty([Billing Type.Billing Type Level 01], "[10BILL_TYPE]")},
            {Cube.AddAndExpandDimensionColumn, "[0CALDAY]", {"[0CALDAY].[LEVEL01]"}, {"Calendar Day.Calendar Day Level 01"}},
            {Table.AddColumn, "Calendar Day.Calendar Day Level 01.Key", each Cube.AttributeMemberProperty([Calendar Day.Calendar Day Level 01], "[20CALDAY]")},
            {Cube.AddAndExpandDimensionColumn, "[0BILL_ITEM]", {"[0BILL_ITEM].[LEVEL01]"}, {"Item.Item Level 01"}},
            {Table.AddColumn, "Item.Item Level 01.Item Level 01.UniqueName", each Cube.AttributeMemberProperty([Item.Item Level 01], "[MEMBER_UNIQUE_NAME]")},
            {Table.AddColumn, "Item.Item Level 01.Key", each Cube.AttributeMemberProperty([Item.Item Level 01], "[20BILL_ITEM]")},
            {Cube.AddAndExpandDimensionColumn, "[0MATERIAL]", {"[0MATERIAL].[LEVEL01]"}, {"Material.Material Level 01"}},
            {Table.AddColumn, "Material.Material Level 01.Key", each Cube.AttributeMemberProperty([Material.Material Level 01], "[20MATERIAL]")},
            {Table.AddColumn, "Material.Material Level 01.Material group (Key)", each Cube.AttributeMemberProperty([Material.Material Level 01], "[20MATL_GROUP]")},
            {Table.AddColumn, "Material.Material Level 01.Material group (Name)", each Cube.AttributeMemberProperty([Material.Material Level 01], "[10MATL_GROUP]")},
            {Table.AddColumn, "Material.Material Level 01.Material Pack Size (Key)", each Cube.AttributeMemberProperty([Material.Material Level 01], "[2ZMAT_2]")},
            {Cube.AddAndExpandDimensionColumn, "[0MATL_GROUP]", {"[0MATL_GROUP].[LEVEL01]"}, {"Material group.Material group Level 01"}},
            {Table.AddColumn, "Material group.Material group Level 01.Key", each Cube.AttributeMemberProperty([Material group.Material group Level 01], "[20MATL_GROUP]")},
            {Table.AddColumn, "Material group.Material group Level 01.Name", each Cube.AttributeMemberProperty([Material group.Material group Level 01], "[10MATL_GROUP]")},
            {Cube.AddAndExpandDimensionColumn, "[0PLANT]", {"[0PLANT].[LEVEL01]"}, {"Plant.Plant Level 01"}},
            {Table.AddColumn, "Plant.Plant Level 01.Key", each Cube.AttributeMemberProperty([Plant.Plant Level 01], "[20PLANT]")},
            {Table.AddColumn, "Plant.Plant Level 01.Medium Name", each Cube.AttributeMemberProperty([Plant.Plant Level 01], "[50PLANT]")},
            {Table.AddColumn, "Plant.Plant Level 01.Name", each Cube.AttributeMemberProperty([Plant.Plant Level 01], "[10PLANT]")},
            {Cube.AddAndExpandDimensionColumn, "[0SALES_GRP]", {"[0SALES_GRP].[LEVEL01]"}, {"Sales group.Sales group Level 01"}},
            {Table.AddColumn, "Sales group.Sales group Level 01.Key", each Cube.AttributeMemberProperty([Sales group.Sales group Level 01], "[20SALES_GRP]")},
            {Table.AddColumn, "Sales group.Sales group Level 01.Name", each Cube.AttributeMemberProperty([Sales group.Sales group Level 01], "[10SALES_GRP]")},
            {Cube.AddAndExpandDimensionColumn, "[0SALES_OFF]", {"[0SALES_OFF].[LEVEL01]"}, {"Sales Office.Sales Office Level 01"}},
            {Table.AddColumn, "Sales Office.Sales Office Level 01.Key", each Cube.AttributeMemberProperty([Sales Office.Sales Office Level 01], "[20SALES_OFF]")},
            {Table.AddColumn, "Sales Office.Sales Office Level 01.Name", each Cube.AttributeMemberProperty([Sales Office.Sales Office Level 01], "[10SALES_OFF]")},
            {Cube.AddAndExpandDimensionColumn, "[0SALESORG]", {"[0SALESORG].[LEVEL01]"}, {"Sales Organization.Sales Organization Level 01"}},
            {Table.AddColumn, "Sales Organization.Sales Organization Level 01.Key", each Cube.AttributeMemberProperty([Sales Organization.Sales Organization Level 01], "[20SALESORG]")},
            {Table.AddColumn, "Sales Organization.Sales Organization Level 01.Long Name", each Cube.AttributeMemberProperty([Sales Organization.Sales Organization Level 01], "[40SALESORG]")},
            {Cube.AddAndExpandDimensionColumn, "[0SALESEMPLY]", {"[0SALESEMPLY].[LEVEL01]"}, {"Sales Representative.Sales Representative Level 01"}},
            {Table.AddColumn, "Sales Representative.Sales Representative Level 01.Key", each Cube.AttributeMemberProperty([Sales Representative.Sales Representative Level 01], "[20SALESEMPLY]")},
            {Table.AddColumn, "Sales Representative.Sales Representative Level 01.Medium Name", each Cube.AttributeMemberProperty([Sales Representative.Sales Representative Level 01], "[50SALESEMPLY]")},
            {Table.AddColumn, "Sales Representative.Sales Representative Level 01.Postal Code (Key)", each Cube.AttributeMemberProperty([Sales Representative.Sales Representative Level 01], "[20POSTAL_CD]")},
            {Cube.AddMeasureColumn, "Basic Price per Liter", "[Measures].[5IDBH7KL3B2A8GIH90JHHY3P4]"},
            {Cube.AddMeasureColumn, "Basic Price Total", "[Measures].[5IDBH7CWKCGKPTZ136H57W4ZC]"},
            {Cube.AddMeasureColumn, "Billed Quantity", "[Measures].[5IDBH62TEKUBM2QC4633JKCOO]"},
            {Cube.AddMeasureColumn, "Cash Discount", "[Measures].[5IDBI0DK72GZ2ZG34RBJ7BAD4]"},
            {Cube.AddMeasureColumn, "Direct Fright", "[Measures].[5IDBH7ZY589P9PLDKOO62214O]"},
            {Cube.AddMeasureColumn, "Early Bird Discount", "[Measures].[5IDBHS9O6JK1F6X0Z0UKL8N5K]"},
            {Cube.AddMeasureColumn, "Free Coupon", "[Measures].[5IDBI0SX8ZOE48IZGFG7RF7SO]"},
            {Cube.AddMeasureColumn, "Free Invoice Quantity", "[Measures].[5IDBH6AHXJG14P9SA05FTMBEG]"},
            {Cube.AddMeasureColumn, "Free Quantity", "[Measures].[5IIC1DKO8CJ0MEJEKVPJ2MWJC]"},
            {Cube.AddMeasureColumn, "Freight Subsidy", "[Measures].[5IDBH7S9M9NZR31XEULTS02EW]"},
            {Cube.AddMeasureColumn, "Gross Bill value", "[Measures].[5IDBH5NGCNMWKTNFSHYEZGF94]"},
            {Cube.AddMeasureColumn, "GST", "[Measures].[5IDBH5V4VM8M3G6VYC0R9IDYW]"},
            {Cube.AddMeasureColumn, "Infra Insentive", "[Measures].[5IDBHSP18GRGGFZXAOZ95CKL4]"},
            {Cube.AddMeasureColumn, "Internal Price", "[Measures].[5IDBH6XJIF95OKW4RICGNS7JS]"},
            {Cube.AddMeasureColumn, "Net Bill value", "[Measures].[5IDBI0L8Q12OLLZJALDVHD92W]"},
            {Cube.AddMeasureColumn, "Net of Returns", "[Measures].[ZNOR_1]"},
            {Cube.AddMeasureColumn, "Others", "[Measures].[5IDBHT4EADYVHP2TMD3XPGI0O]"},
            {Cube.AddMeasureColumn, "Prorata Quantity", "[Measures].[5IDBI18AAWVT5HLVS3KWBJ588]"},
            {Cube.AddMeasureColumn, "Return", "[Measures].[5IDBH6PUZGNG5YCOLOA4DQ8U0]"},
            {Cube.AddMeasureColumn, "Scheme Discount", "[Measures].[5IDBHSWPRFD5Z2JDGJ1LFEJAW]"},
            {Cube.AddMeasureColumn, "Secondary Scheme", "[Measures].[5IDBI10LRYA3MV2FM9IK1H6IG]"},
            {Cube.AddMeasureColumn, "Spcl Discount", "[Measures].[5IDBHSHCPI5QXTGH4UWWVALVC]"},
            {Cube.AddMeasureColumn, "Total Billed Qty", "[Measures].[5IDR16YLSNTIE0GZBWK69ZJ20]"},
            {Cube.AddMeasureColumn, "Total Billed Quantity", "[Measures].[ZTBQ_1]"},
            {Cube.AddMeasureColumn, "Trade Discount", "[Measures].[5IDBH7581DUV77FKXCESXU69K]"},
            {Cube.AddMeasureColumn, "Zero Invoice Quantity", "[Measures].[5IDBH6I6GI1QNBT8FU7S3OA48]"}
        })
in
    #"Added Items"

[Anonymous]

If it's a BEx query, can you run the query in BEx Analyser/BEx web and see if the query is executing in BW for the same selection and parameters. If yes, how many records are there in the result?

BEx queries have an inherent limitation. So please confirm if it is running fine. Then we will think about Power BI part.

[v-chuncz-msft]

@Anmolgan ,

You may take a look at DirectQuery and SAP Business Warehouse (BW).

[Anmolgan]

Thanks for the response, but that does not answer my question.