How to extract Power BI model to data warehouse bus matrix

 Check list of all posts

To quickly overview your power bi data model, we can turn the model into a data ware warehouse matrix. This document is intended to provide an easy way with the steps listed below.

Step 1: Open DaxStudio and run DMV statements:

select FromTableID, FromColumnID, ToTableID, ToColumnID from $System.TMSCHEMA_RELATIONSHIPS;
select [ID],[Name] from $System.TMSCHEMA_TABLES;
select [ID],TableID,ExplicitName from $System.TMSCHEMA_COLUMNS where LEN(ExplicitName) < 40;

Step 2: Put each query results into excel spreadsheet to generate SQL queries:

drop table if exists #RELATIONSHIPS;

create table #RELATIONSHIPS (FromTableID int, FromColumnID int, ToTableID int, ToColumnID int);

insert into #RELATIONSHIPS values 

 (10468,10480,1046,1049),

 (10468,10476,1119,1122),

.......

 (17766,17773,7789,7797);

 drop table if exists #TABLES;

create table #TABLES (ID int, Name varchar(50));

insert into #TABLES values 

 (1046,'Tabname1'),

 (1119,'Tabname2'),

......

 (17766,'Fact5');

drop table if exists #COLUMNS;

create table #COLUMNS (ID int, TableID int,ExplicitName varchar(50));

insert into #COLUMNS values 

 (1049,1046,'Column1'),

 (1050,1046,'Column2'),

......

 (17867,17766,'ColumnN');

Step 3: Run queries below, you will get matrix

drop table if exists #RELATIONSHIPSEX;

 select 

  R.FromTableID

 ,TF.Name as FromTable

 ,R.FromColumnID 

 ,CF.ExplicitName as FromColumn

 ,R.ToTableID

 ,TT.Name as ToTable

 ,R.ToColumnID 

 ,CT.ExplicitName as ToColumn

 ,1 R 

 into #RELATIONSHIPSEX

 from #RELATIONSHIPS R

 left join #TABLES TF on (R.FromTableID=TF.ID)

 left join #TABLES TT on (R.ToTableID=TT.ID)

 left join #COLUMNS CF on (R.FromColumnID=CF.ID)

 left join #COLUMNS CT on (R.ToColumnID=CT.ID);

drop table if exists #MatrixHighlevel;

drop table if exists #MatrixDetail;

SELECT ToTable as Dimension,[Fact1], [Fact2], [Fact3] , [Fact4] , [Fact5]  

into #MatrixHighlevel

FROM   

(SELECT R,FromTable,ToTable

FROM #RELATIONSHIPSEX) p  

PIVOT  

(  

sum(R) 

FOR FromTable IN  

( Fact1, Fact2, Fact3, Fact4, Fact5 )  

) AS pvt  

ORDER BY ToTable;  

SELECT ToTable as Dimension,[Fact1], [Fact2], [Fact3] , [Fact4] , [Fact5]

into #MatrixDetail

FROM   

(SELECT R,FromTable,ToTable,FromColumn,ToColumn,ToTable+'.'+ToColumn+'-->'+FromTable+'.'+FromColumn as R1

FROM #RELATIONSHIPSEX) p  

PIVOT  

(  

max(R1) 

FOR FromTable IN  

( Fact1, Fact2, Fact3, Fact4, Fact5 )  

) AS pvt  

ORDER BY ToTable;