Solved

How to connect one Power BI Premium tabular model to another as a datasource?

  • 23 October 2023
  • 2 replies
  • 153 views
M
Userlevel 1
Badge

Hi all,

We are migrating from on-prem tabular models to PowerBI Premium models. Most datasources work flawlessly (including the CDATA Snowflake connector). However, we have been unable to use another tabular model as a data source when using PowerBI Premium models…

We have the same problem when trying to connect to our Power BI Premium models from our data catalog (Ataccama).

 

OLD On-prem Visual Studio source:

Friendly connection name: [model name]
Server or File Name: [server] \ [Folder]
Database name: [Database name]

OLD Ataccama /CDATA JDBC connector:

jdbc:cdata:ssas:User=[user];catalog="[catalog]";URL=http://[server]/OLAP/msmdpump.dll


New Connections that work:
Excel Connection (Power BI Premium Data Source):

Provider=MSOLAP.8;
Integrated Security=[];
Persist Security Info=[];
Initial Catalog=sobe_wowvirtualserver-[GUID];
Data Source=pbiazure://api.powerbi.com;
MDX Compatibility=1;Safety Options=2;
MDX Missing Member Mode=Error;
Identity Provider=https://login.microsoftonline.com/common, https://analysis.windows.net/powerbi/api, [GUID];
Update Isolation Level=2


SSMS Analysis Services

Server type: Analysis Services
Server name: powerbi://api.powerbi.com/v1.0/myorg/[Workspace Name]
Auth: Azure AD - Universal with MFA
User name: [user name]

 

If anyone has any ideas, I’d be most grateful!


Thanks,

Malcolm

Ps. Apologies, if I have the category wrong here, (might be cloud connect?)

icon

Best answer by Malcolm 17 November 2023, 18:00

View original

2 replies

Ankit Singh
Userlevel 5
Badge +1

Hi @Malcolm 

I am still somewhat confused with your question and the challenge you have here. From what I understand, you have created a Tabular model in Power BI using the SSAS connection. Is that correct?
Now, are you trying to connect to another Tabular model in the same Power BI report?

If yes, from the thread here: Solved: Combine multiple tabular models - Microsoft Fabric Community, it looks like creating a Composite model might be able to help: Composite models over Power BI datasets and Azure Analysis Services - Power Platform Release Plan | Microsoft Learn.

I still think this is more of a question from Microsoft and not CData related!

P.S: I might not be understanding the question entirely, so if you can provide more details, that’d be helpful.

That Solutions Guy
M
Userlevel 1
Badge

Shout out to Rei in customer support for his patience on this. (and @Ankit Singh for response)

Background: I don’t know when Microsoft rolled this out, but now with Power BI Premium per user licensing, you can store your SSAS Tabular Models on their cloud but connecting is...different. Formerly, you could use one model as a source for another with relative ease but that is trickier now.

However, the solution to the external tool connection (Ataccama, in this case) is to use the CDATA JDBC XMLA connector.

jdbc:powerbixmla:InitiateOAuth=GETANDREFRESH;Workspace=[workspace1], [workspace2]...;

Using a 3rd party tool like DBEAVER documentation is here.

Caveat: when adding a new driver, you need to click “OK” before selecting the “Driver class” as it won’t show up until you do.

Also, the “tables” show up as “views”, just FYI.

 

 

 

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings