DAX query on different DB but same SSAS server

[smpa01]

@lbendlin @TomMartens @GilbertQ @bcdobbs @parry2k @d_gosbell 

I have not learnt who are the SME on this and if you feel that there is someone else who can help me on this please don't hesitate to tag.
Sorry for the poor title. Let me demonstrate my question with an example.

If there are two SQL tables exist on different SQL databases (DB) but on the same server, SQL allows to perform queries between those tables from different DBs.

E.g.

if there are two databases on the same SQL server (server1) - Db1 and Db2. Db1 has a table called  tbl1with a column custId and Db2 has a table called tbl2 with a column custId , it is possible to write a query like this

select *
from Db1.dbo.tbl1 x
join Db2.dbo.tbl2 y on x.custId = y.custId

Is it possible to do the same in case of a SSAS server (e.g. PBI Premium Workspace)?

Let's suppose I have deployed two tables in the SSAS server called t1 and t2 with following structure

Is it possible to write a DAX query where I can perform  join/filtering between tables tbl t1 (from DB t1) tblt2 (from DB t2).

In normal circumstances if both tables are from the same SSAS DB, I am able to do this

But I was just wondering, like SQL server if it is possible at all to perform this across tables from different DBs. I extensively use DAX queries on the SSAS server and if this is possible, it can unleash some serious powers on my side to solve some complex business problems for my client.

Looking forward to hearing from you please.

Merry Christams and happy holidays !!!

[parry2k]

@smpa01 I don't see any workaround for this. 😞

[parry2k]

@smpa01 @bcdobbs exactly what @AlexisOlson suggested. That's what I was referring to, doing merge in PQ if two different datasets, if it is a single dataset then write DAX query 

Cheers!!

P

[smpa01]

@parry2k thanks for clarifying. I already knew that these tables can be brought over to PQ and do the rest, but I really want to bypass that and have the transformation on the server-side if possible. That is the original intent. Given PQ's performance issue, I really don't want to involve it, unless I absolutely have to.

[AlexisOlson]

This doesn't take advantage of them being on the same server, but you can load both tables and merge them in Power Query along these lines:

let
    Table1 = AnalysisServices.Database(Workspace, DB1, [Query="EVALUATE Table1"]),
    Table2 = AnalysisServices.Database(Workspace, DB2, [Query="EVALUATE Table2"]),
    Merged = Table.NestedJoin(Table1, "custID", Table2, "custID", "Table2", JoinKind.LeftOuter)
in
    Merged

I know this isn't exactly what you're looking for. It's not a Live or DirectQuery connection and isn't fully evaluated on the server side but might still be useful in occasional situations.

[parry2k]

@bcdobbs yes you can do it if you are using XMLA endpoint which @smpa01 was trying to do. I just tested it, connect it to the remote Power bI dataset using XMLA endpoint, and wrote the custom DAX query to get the result that I want. In theory, it is possible.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smpa01]

@parry2k  okay but how do you define different db names within EVLUATE, unless I am dropping all those SSAS dbs (t1 and t2) in a pbix (like @bcdobbs suggested, thanks for that, test pending on my side) and publish that in workspace and try to use the dax query on that model.

[bcdobbs]

Do mean connect to a composite model via xlma endpoint and run query against that? Or have you found a way to bypass the composite model layer? If so could you send an example query?

[bcdobbs · Accepted solution]

I don't think you can do it directly like you're trying. 

The closest you could get is to follow @parry2k suggestion and drop both models into a composite model and publish to premium workspace. To an external xlma connection that then behaves as a single model on which you could run your natural join.

Would be amazing if you could query directly like you suggest in SSMS or DAX studio but I suspect given the work that microsoft have had to put in to make composite models function I'm not sure it is. Happy to be corrected though.

[smpa01]

@bcdobbs  apologies for the delay in getting back to this. @bcdobbs  this is a brilliant suggestion. Yes, can't make SSAS behave like SQL at this point of time (with different DB but on the same server). 

The workaround is to put the SSAS DBs on a composite model and then all the DBs are available for combined transformation/query. @bcdobbs  awesome !!! 

[parry2k]

@smpa01 although try this, you have to use EVALULATE  to return the table

EVALUATE
NATURALINNERJOIN( <<first table>>, <<2nd table>>)

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@smpa01 Why would you do that? Isn't these tables are already joined in the model (Power BI dataset)?

[parry2k]

@smpa01 if you are using Power BI or AAS as a data source, yes, you can combine models called composite models (mainly known as DQ over Power BI and AAS) you can read more here. I hope this is what you are looking for. Using DirectQuery for datasets and Azure Analysis Services (preview) - Power BI | Microsoft Docs

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smpa01]

@parry2k  thanks for looking into it. But this is not what I was looking for. I was not looking for a composite model at all.

I am looking forward to do something like this (if possible) so that I can utilize the same in dataflow as well.

(where table t1 is from DB t1 and table t2 is from DB t2- but both reside on the same SSAS server)