cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wkmaylish
Frequent Visitor

Dynamic datasource issue with loop. NOT Web Contents

I am trying to setup data set that could help us document our data warehouse and Power Bi environments. 

 

I am currently working on pulling in system info from analysis services and am running into an issue with a dynamic datasource.  In my attempt to make my life easier I created a loop that would loop through a list of cubes and pull all of the data together into one query (instead of creating 20+ queries and another to union them together).It didnt hit me that this would be dynamic until I published. 

 

Is there anyway to do this without having to create a query for each table? 

 

 

 

Thanks for the help.

 

 

Bare bones version of the M code. 

 

let
DataCatalog = {"Cube1", "Cube2", "Cube3"},

FnGetOnePage = 
    (i) as record =>
    let    
    CatalogTables =  AnalysisServices.Database("{Server Address}", 
    List.First(List.Range(DataCatalog,i,1)), [Query="Select * from $System.TMSCHEMA_PARTITIONS"]),           
    CatalogTables2 = Table.AddColumn(CatalogTables,"Dataset", each  List.First(List.Range(DataCatalog,i,1))),
    res = [Data=CatalogTables2, Next = i+1 ]    
    in
    res,    
 
 GeneratedList =
  List.Generate(
      ()=>[i=0,res=FnGetOnePage(i)],
      each [res][Next]<= List.Count(DataCatalog),
      each [i=[i]+1,res=FnGetOnePage([res][Next])],
      each [res][Data] )    
in
    GeneratedList

 

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Here's how I would approach it.  Create a simple table out of the list of the cubes you want to query, and then run the query inside an added column. (Note: only one cube shown in this example)

 

 

let
    Server = "localhost:62185",
    Databases = #table({"Name"},{{"15aa750e-d54b-4902-85f5-6c5b44698a7f"}}),
    #"Added Custom" = Table.AddColumn(Databases, "Meta", each AnalysisServices.Database(Server, [Name], [Query="Select * from $System.TMSCHEMA_PARTITIONS"])),
    #"Expanded Meta" = Table.ExpandTableColumn(#"Added Custom", "Meta", {"Name", "QueryDefinition"}, {"Name.1", "QueryDefinition"})
in 
    #"Expanded Meta"

 

 

lbendlin_0-1635300532512.png

 

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

Here's how I would approach it.  Create a simple table out of the list of the cubes you want to query, and then run the query inside an added column. (Note: only one cube shown in this example)

 

 

let
    Server = "localhost:62185",
    Databases = #table({"Name"},{{"15aa750e-d54b-4902-85f5-6c5b44698a7f"}}),
    #"Added Custom" = Table.AddColumn(Databases, "Meta", each AnalysisServices.Database(Server, [Name], [Query="Select * from $System.TMSCHEMA_PARTITIONS"])),
    #"Expanded Meta" = Table.ExpandTableColumn(#"Added Custom", "Meta", {"Name", "QueryDefinition"}, {"Name.1", "QueryDefinition"})
in 
    #"Expanded Meta"

 

 

lbendlin_0-1635300532512.png

 

Thanks for the reply. I gave it a try and am able to pull back data in PBI Desktop but it still gives me a dynamic dataset error once I publish. I am going to look into pulling this data into our data warehouse somehow. 

You can run your DMV queries from Powershell scripts if you want.

How to Extract SSAS DMV Data using PowerShell - sqlitybi

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!