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
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!