RDL using Power BI Dataset in Service

[ellisjm]

Hi All, we're doing a PoC of PBRS.  I've created a quick & dirty RDL using Power BI Report Builder (v15.0.1380.0) that uses a Power BI Dataset in Service.  We have a Premium tenant.  While the RDL runs just fine in Power BI Report Builder, I can't get the darn thing to run once uploading it to PBRS.

Steps to recreate:

1. Start a blank report in Power BI Report Builder
2. Right click on Data Sources, choose Add Power BI Dataset Connection:
3. Select the workspace & dataset
4. Right click on Datasets and Add Dataset
5. Select the Data Source and click Query Designer
6. Grab a couple of dimension attributes & measures in Query Designer, hit OK
7. Add a tablix to the report and aforementioned dimension attributes & measures.
8. Save the RDL
9. Upload to the PBRS
10. Manage the report & go to Data Sources
11. Connection Type is PBIDATASET.  It has an error saying "The data extension used here is not available. It has either been uninstalled, or it is not configured correctly."

I've tried changing the type and doing more of an XMLA type connection to the Power BI Dataset in the service but can't get that working either.  Any hints would be greatly appreciated!

[jcadle]

I found a way to accomplish this though not with that extension, I think that extension is fairly new even to the power bi report builder and may come to power bi server in a future release. The upside to the discovery I made is that it works on a regular report server also. Though you can not do it as a shared datasource on the server.

Step 1: make a basic report using Power Bi report builder and the new datasource that lets you connect to a published dataset. Add your datasets and save the file.

Step 2: Go to your power Bi online enviroment to the dataset and choose the analyse in excel option. Once excel opens go to the data tab and choose existing connections. Right click on the connection in the workbook and choose edit. On the Definition tab you will find the OLE DB connection string. Copy this.

Step 3: Open Report builder for RS or PBI RS. Create a OLE DB embedded connection and paste in the connection string from excel then remove the Integrated Security=ClaimsToken entry. You have to check the box to Use single transaction (I think this is why it wont work from a server shared connection, that option is not available). Then go to the credentials tab and either set it to prompt or to use a username password, if setting a user name it will need to be a member of the Power BI workspace where the dataset is published. Test the connection and save.

Step 4: Add a dataset. Choose the embedded dataset you just created as the datasource in the query segment choose the import button. In the file selection window find that origonal basic Power BI Report Builder file you made and select it. A box will pop where you can choose the datasets you had created there. Bring what ever you had created in to the new project and you are ready to build out the rest of your report.

[Anonymous]

Hi

i am trying to run my paginated reports from Power bi report server and facing the same issues.

Could you please explain the Step 3 again?

are you asking to enter the connection string in Power BI report builder. ?

[jcadle]

Yes step 3 is copying the OLE DB  created by exporting to analyse in excel and pustting that  connection into the report builder tool. Its not a clean solution but it did accomplish the task of making it work.

[Anonymous]

Thanks!! the solution worked. 

but i have an issue with using named parameters in report builder. do you have any solutions here ?

EVALUATE
VAR _TempTable=
SUMMARIZECOLUMNS (
'INVOICE_ITEM'[COMPANY_ID],
'INVOICE_ITEM'[INVOICE_ID],
FILTER (
VALUES ( 'INVOICE_ITEM'[CUSTOMER_ACCOUNT_ID] ),
( 'INVOICE_ITEM'[CUSTOMER_ACCOUNT_ID] = VALUE(@CustomerNo) )
)
)
RETURN
SELECTCOLUMNS(_TempTable,"COMPANY_ID", [COMPANY_ID],
"INVOICE_ID", [INVOICE_ID]
)
ORDER BY [INVOICE_DATE] DESC

[jcadle]

I don't have a direct solution as I didnt go very far after just getting it to work. If I were takleing that though, I would try and push what I could of that into the published data source.

[jcadle]

I am seeing the same thing, the option was not available in version 14 either so I updated to version 15 thinking that may resolve it. No go, and so far this is the only topic I have been able to find on the internet even discussing this.