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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

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 May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!