Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ellisjm
Regular Visitor

RDL using Power BI Dataset in Service

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!

 

6 REPLIES 6
jcadle
Regular Visitor

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
Not applicable

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. ?

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
Not applicable

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

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
Regular Visitor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.