2

I'm recently trying out using MDX query in Power BI to query information directly from SAP Business warehouse cube. I encountered an error message that I can't figure out how to solve. The error message is: "SAP Business Warehouse: Specify a value for variable Day Interval"

In the report, choosing date range is mandatory. If I click through using Power BI to SAP BW connector, one of the steps is like the screenshot below: enter image description here

The day is in the Cube called [0CALDAY] and is specified using [0CALDAY].[20101107]

However if I use MDX to query similar data using the code below

select {[Measures].[00O2THVIBBMJV8JIFLLW439K8]} on columns 
from [0PT_MP01/Y_PT_TM_Q001_BOBJ]
where {[0CALDAY].[20101101]: [0CALDAY].[20101110]}

and click OK, I will receive the message says: "SAP Business Warehouse: Specify a value for variable Day Interval". My understanding of this is the way I'm choosing day interval is not recognized?

I've also tried to use & in front of [20101101] to make something like this: [0CALDAY].&[20101101] (according to some of the introduction online), then I receive syntax error message.

Is there anything I can do with the query language? Do I need to convert the number into date type? I'm really new to MDX and Cube, any suggestions will be really appreciated.

Thanks

Lambo
  • 857
  • 3
  • 14
  • 39

2 Answers2

1

You are required to add an SAP Variable at the end of the query.

To do this you can add an SAP Variable the following way:

SAP VARIABLES [<technical name of variable>] INCLUDING <value for variable>

Your query would end up looking like this:

select 
    {
        [Measures].[00O2THVIBBMJV8JIFLLW439K8]
    } on columns 
from 
    [0PT_MP01/Y_PT_TM_Q001_BOBJ]
sap variables
    [0CALDAY] including "20101101":"20101110"

Please find more information here: https://wiki.scn.sap.com/wiki/pages/viewpage.action?pageId=356124639 https://blogs.sap.com/2005/06/08/quick-help-in-creating-mdx-statement/

Cesar Flores
  • 762
  • 7
  • 16
wertwry
  • 26
  • 4
0

I would give an example to help explain the usage of mandatory SAP variables:

SELECT { [Measures].[measure1_name], [Measures].[measure2_name] } ON COLUMNS, NON EMPTY { [dimension1_name].[LEVEL01].MEMBERS, [dimension2_name].[LEVEL01].MEMBERS } ON ROWS FROM [BExQuery_name] SAP VARIABLES [!V000001] INCLUDING [dimension1_name].[80000000103] [!V000004] INCLUDING [0CALDAY].[20130101]:[0CALDAY].[20130104]

!V000001 and !V000004 are BEx variables.

The 80000000103 is a sample value for "dimension1_name". The 20130101 and 20130104 are sample range for 0CALDAY.

AecorSoft
  • 414
  • 4
  • 10