SAP Hana Connection in Excel Power Query - Input Parameters

%3CLINGO-SUB%20id%3D%22lingo-sub-1560457%22%20slang%3D%22en-US%22%3ESAP%20Hana%20Connection%20in%20Excel%20Power%20Query%20-%20Input%20Parameters%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560457%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20see%20an%20example%20of%20MDX%20script%20in%20Excel%20to%20use%20use%20the%20function%20%22SapHanaRangeOperator.Equals%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20established%20a%20connection%20to%20an%20SAP%20HANA%20database%20using%20power%20query%20in%20excel%202016%20using%20the%20function%3A%26nbsp%3BSapHana.Database.%26nbsp%3B%20Using%20this%20connection%2C%20i%20can%20access%20data%20of%20the%20type%20%22cube%22%2C%20by%20adding%20dimensions%20and%20attributes.%26nbsp%3B%20If%20the%20data%20type%20is%20%22parameterized%20cube%22%20i%20get%20an%20error%20message%20when%20i%20try%20to%20add%20dimensions%20and%20attributes.%26nbsp%3B%20I%20think%20this%20is%20because%20the%20SAP%20hana%20server%20needs%20to%20be%20told%20a%20date%20range%20(input%20parameters)%20in%20order%20to%20actually%20provide%20the%20data%20from%20the%20%22parameterized%20cube%22%20data%20kind.%26nbsp%3B%20There%20message%20i%20get%20says%20that%20i%20lack%20permissions%2C%20but%20i%20know%20this%20is%20not%20the%20case%20because%20i%20can%20access%20the%20data%20through%20other%20applications%2C%20such%20as%20%22SAP%20analysis%20for%20office%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20example%20of%20the%20script%20i%20am%20using%20to%20successfully%20get%20data%20out%20of%20the%20HANA%20database%20using%20power%20query%20on%20a%20%22non-parameterized%22%20cube.%26nbsp%3B%20What%20i%20want%20to%20know%20is%20an%20example%20of%20how%20to%20add%20the%20%22SapHanaRangeOperator.Equals%22%20or%20other%20such%20function%2C%20if%20the%20cube%20%22YYYY%22%20were%20to%20a%20%22parameterize%22%20cube.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Elet%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Source%20%3D%20SapHana.Database(%22%3CPORT%3E%22)%2C%3C%2FPORT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Contents%20%3D%20Source%7B%5BName%3D%22Contents%22%5D%7D%5BData%5D%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%23%22%3CZZZZ%3E%22%20%3D%20Contents%7B%5BName%3D%22%3CZZZZ%3E%22%5D%7D%5BData%5D%2C%3C%2FZZZZ%3E%3C%2FZZZZ%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%3CYYYY%3E%20%3D%20%23%22%3CZZZZ%3E%22%7B%5BName%3D%22%3CYYYY%3E%22%5D%7D%5BData%5D%2C%3C%2FYYYY%3E%3C%2FZZZZ%3E%3C%2FYYYY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%23%22Added%20Items%22%20%3D%20Cube.Transform(%3CYYYY%3E%2C%3C%2FYYYY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7BCube.AddAndExpandDimensionColumn%2C%20%22AGREE_DATE%22%2C%20%7B%22AGREE_DATE%22%7D%2C%20%7B%22AGREE_DATE%22%7D%7D%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7BCube.AddMeasureColumn%2C%20%22VOLUME%22%2C%20%22VOLUME%22%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7D)%3C%2FP%3E%3CP%3Ein%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%23%22Added%20Items%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1560457%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I would like to see an example of MDX script in Excel to use use the function "SapHanaRangeOperator.Equals". 

 

I have established a connection to an SAP HANA database using power query in excel 2016 using the function: SapHana.Database.  Using this connection, i can access data of the type "cube", by adding dimensions and attributes.  If the data type is "parameterized cube" i get an error message when i try to add dimensions and attributes.  I think this is because the SAP hana server needs to be told a date range (input parameters) in order to actually provide the data from the "parameterized cube" data kind.  There message i get says that i lack permissions, but i know this is not the case because i can access the data through other applications, such as "SAP analysis for office".

 

 

Here is an example of the script i am using to successfully get data out of the HANA database using power query on a "non-parameterized" cube.  What i want to know is an example of how to add the "SapHanaRangeOperator.Equals" or other such function, if the cube "YYYY" were to a "parameterize" cube. 

 

let

    Source = SapHana.Database("<Server:port>"),

    Contents = Source{[Name="Contents"]}[Data],

    #"<ZZZZ>" = Contents{[Name="<ZZZZ>"]}[Data],

    <YYYY> = #"<ZZZZ>"{[Name="<YYYY>"]}[Data],

    #"Added Items" = Cube.Transform(<YYYY>,

        {

            {Cube.AddAndExpandDimensionColumn, "AGREE_DATE", {"AGREE_DATE"}, {"AGREE_DATE"}},

            {Cube.AddMeasureColumn, "VOLUME", "VOLUME"}

        })

in

    #"Added Items"

 

0 Replies