Passing values in Input Parameters - Variables of Datasphere Model from Power BI

[Jinal_Patel]

Introduction

Currently there are no out of box connectors for Datasphere in Power BI. There is Beta version of SAP Datasphere OData connector available, but it is a custom connector and has Odata feeding. Hence for stability and performance perspective I chose ODBC Connector for my use case.

But Leveraging Standard ODBC connector also has some known challenges when it comes to reporting in Power BI using data source as SAP Datasphere. One of Major limitation identified is that Input parameter/Variables created at Datasphere level are not getting exposed in Power BI. Hence Push down of user inputs wont be possible and filtering will be applied at Power BI level which is not efficient in terms of dynamic calculation, resource utilization and performance.

This Document will explain workaround to mitigate this and step by step process in order to push down front end passed filters to Datasphere layer. Please note this workaround is suitable only for static reporting requirement in terms of fields and list of user inputs.

Steps

2.1 Create Datasphere Analytic model with input parameters enabled

2.2 Use ODBC connector with SQL string for initial SQL connection with input enabled Analytic Model

2.3 Edit Query, create new Parameter and map new parameter into SQL String. That’s It!!

2.4 Try Out Dynamic Filtering in Analytic Model by passing different values in Power BI Parameter

[AnkurGoyal03]

Hello Jinal,

This is great workaround, but could you please suggest if we can do something for Import data connection option?

Thanks,

Ankur

[maxpinato_horsa]

Hello Jinal, great post. I've a question similar to AnkurGoyal ... are you using live query ? 

[Jinal_Patel]

This is Import Connection @maxpinato_horsa @AnkurGoyal03 

[albertosimeoni]

Hello @Jinal_Patel ,

are you using the so called "database analysis user" to read from an analytic model? because as far as I know the "normal" database user defined inside spaces can not read analytic models:

The column view generated by the analytic model can not be read with database user defined inside a space.

[Jinal_Patel]

Now, Analytic model can be read as well with open sql user created in space. @albertosimeoni 

[albertosimeoni]

@Jinal_Patel 

I found a problem...

A Single Analytic model is deployed as multiple column view in the database:

1) A column View with base fact fields -> that can be read from DB users.

2) A column view for every associated dimension. -> that can not be read from DB users.

3) A final column view (Fact join Dimensions). -> that can not be read from DB users.

example with a database analysis user (read all permission).

Example with a DB User created inside a space (2 and 3 are missing!!):

[albertosimeoni]

Hello @Jinal_Patel ,

I confirm that now I can read Analytic Model through DB users but with limitations.

I ask to SAP to update the official Datasphere documentation here https://help.sap.com/docs/SAP_DATASPHERE/43509d67b8b84e66a30851e832f66911/4db6f5a329af44509ae422ad70...

As is not mentioned anywhere unless this note https://me.sap.com/notes/3416339/E

[AnkurGoyal03]

Hi @albertosimeoni ,

I faced the same situation when connecting using ODBC connection, the Association won't work. so unfortunately, we have to flatten the structure in Analytical Model and we did the text join underlying view and consumed in AM.

Thanks,

Ankur