Import data from SSAS Tabular model

[yunchenlin]

In PB Desktop, I am connecting with "Import" mode to a basic Tabular SSAS model with 3 tables. However when imported into powerBI I have the results：

1. Only a single (Model) table with all fields was created and it is a mess without order. I would like to see 3 separated tables. why is this?

2. The number of records in that (Model) table became very large and equaled to

     (the # of rows in table-1)*((the # of rows in table-2)*(the # of rows in table-1).

That's why I got the "memory insufficient error" when I tried to import all the 13 tables in same time.

why is this?

appreciate if anyone can help.

[Anonymous]

Anyone has a solution for this? 🙂

[Anonymous]

Hi All,

I got a very similar issue. As I'm tasked to explore the feasibility of importing an Azure Analysis Service model into Power BI, I'm able to import each table one by one (but it's extremely slow even though I'm on a AAS S2 tier), but as soon as I import two tables it fails with the following error message:

Connecting live works fine but then users cannot create their own columns nor add other datasets to the report.

If anyone could share a workaround, that would be much appreciated.

Thanks

[v-chuncz-msft]

@yunchenlin,

You may do research into The Advanced Editor.

https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-reference

[yunchenlin]

Dear Sam,

I looked the Advanced Editor and the M Language reference. But I still don't understand how to solve the "import" issue.

When I choose 3 tables from SSAS Tabular model using "import connection" I will get only 1 table in PBI Desktop model with all  columns joined. The script of the Advanced Editor looks as below. 

Should I modify the script or change any settings to get the correct result？ 

I also couldn't find too many guides in how to "import" SSAS Tabular model to PBI Desktop. 

Thanks,

Jack

----------------------------------------------

let
Source = AnalysisServices.Database("192.168.12.51\pb_db2", "TabularProject1", [TypedMeasureColumns=true]),
#"TabularProject1 Model1" = Source{[Id="TabularProject1 Model"]}[Data],
#"TabularProject1 Model2" = #"TabularProject1 Model1"{[Id="TabularProject1 Model"]}[Data],
#"Added Items" = Cube.Transform(#"TabularProject1 Model2",
{
{Cube.AddAndExpandDimensionColumn, "[DimProduct]", {"[DimProduct].[ChineseDescription].[ChineseDescription]",...
{Cube.AddAndExpandDimensionColumn, "[DimProductCategory]", {"[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName]", ...
{Cube.AddAndExpandDimensionColumn, "[DimProductSubcategory]", {"[DimProductSubcategory].[EnglishProductSubcategoryName].[EnglishProductSubcategoryName]", ...
})
in
#"Added Items"

[Anonymous]

Have you managed to fix the issue? I am facing the same problem! Using Direct Query on AAS I can get the separate tables the way I have defined them in Analysis Services, whereas importing results in one big table with all the measures and dimensions into one 😮 Big mess! 

[yunchenlin]

No, I haven't gotten the solution. Sorry.

[Anonymous]

I think I may have found a workaround. You connect to AS and when you can choose which tables to load, you only choose one table. You load it (or edit first and then load) and then you connect again to AS via Get Data and choose another table, and so on, until all the tables needed are imported. 

But for my case, I think DirectQuery is the better connection option. 

Hope this helps!

[Shelley]

@Anonymous Thanks for the help! I like your workaround comment. Helped me a lot!

[Anonymous]

@Shelley since my comment, there has been quite a lot of Power BI advancements, so I do not see the need to import from AAS model anymore. We wanted to import so that we can add more data sources directly in the Power BI desktop model. But the new composite models feature that works with AAS models is fixing this issue. An extra note Shelley, opening to import for users can put big strains on your AAS Server when they refresh the data (and you have no control over this), so it can disrupt your normal DQ model. I am unsure what your case is, but I would advise against using import on AAS models

[yunchenlin]

@Anonymous Thank you. We currently are using the "Import connections & scheduled refresh" from SQL Server.

"Query performance" is more important than "realtime data" to us. 

Originally, we try to design the model in SSAS Tabular due to the consideration  of central management of those created measures for different reports. 

We will keep trying it. Thank you!

[Anonymous]

I know this is an old post, but it sounds like you need to establish relationships between your tables. You will only ever have one Model.bim file by the way; this is not affected by the number of tables you import. If you haven't done so, click on the Diagram icon (highlighted in the screen snip below) and establish those relationships.

[Anonymous]

Thank you for the input Petri. Relationships are there in AS, and Direct Query works very well. It's just that we were expecting a different behaviour in import mode. When you choose data from eg. 3 tables in AS, instead of getting 3 tables with relationships between them in Power BI, you get one flat table created based on the relationships between the 3 tables.