Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mittalpatel130
Helper III
Helper III

SSAS Tabular model : When connect in import mode, it merges all tables, measures

Hello All ,

 

I've created one tabular model with -

  1. Table 1
  2. Table 2
  3. Table 3
  4. Measure 1
  5. Measure 2
  6. KPI 1
  7. KPI 2

Then, When  I connect with tabular model using Live Connection mode, it looks fine in Power BI Desktop Fields section.

But, when I connect with Import mode, it merges everything in 1 table and tables doesn't look clear and separate for end users to utilize. 

 

Why is this different nature for both connection types?

Any thoughts?

 

Thank you 

1 ACCEPTED SOLUTION


@mittalpatel130 wrote:

I'm wondering if I've to create measures and KPIs again in Power BI Data Model then what is the use of Tabular in between my data source and Power BI? just to join the Dim and Fact tables? 


If you want to use import mode in Power BI there is no point using a Tabular model. It is a waste of time and resources to build a Tabular model in between your data source and Power BI if you just going to import the data into Power BI anyway.

 


@mittalpatel130 wrote:

What is the most efficient and reliable connection type with Tabular model? Import or Live Connection? 

If I go with Live connection, report developers won't be able to add more sources if require or add more measures and calculated fields. 

 


If you already have a Tabular model or you want an official model that has been tested and proven that you don't want anyone altering then using a Live Connection is the way to go. You can add report level measures to a model in live connection mode, but you can't add new tables and columns.

 

If you want to use Import mode I would avoid building a Tabular model if you can, they are not a good source for reading detailed information. And populating the model is an unnecessary extra step that wastes time and resources. 

View solution in original post

7 REPLIES 7
GiridharP
Frequent Visitor

That is the behavior when you select all the tables in one go. In this case all the relationships are already established for you.

 

Try selecting one table at a time Import > OK.  

Launch Get Data again for the next table Import > OK 

This way you will get all the tables separately; but you need to set up the required relationships between the tables.

d_gosbell
Super User
Super User


@mittalpatel130 wrote:

Then, When  I connect with tabular model using Live Connection mode, it looks fine in Power BI Desktop Fields section.

But, when I connect with Import mode, it merges everything in 1 table and tables doesn't look clear and separate for end users to utilize. 

 

Why is this different nature for both connection types?

 


This is because when you are in Import mode you have the freedom to restructure the data however you want. If you bring every column in using a single query they will appear as a single table. If you want the users to see 3 tables you need to build 3 queries (one for each table).

 

Note that measures and KPIs won't import correctly. You would need to copy the DAX expressions for those and re-implement them in the new model. 

 

Also you can run into performance issues reading a lot of detail from a tabular model. Typically you would be better off querying the same original source that the Tabular model uses (if you can get access to it)

Thank you very much for  your responses!!

 

I'm wondering if I've to create measures and KPIs again in Power BI Data Model then what is the use of Tabular in between my data source and Power BI? just to join the Dim and Fact tables? 

What is the most efficient and reliable connection type with Tabular model? Import or Live Connection? 

If I go with Live connection, report developers won't be able to add more sources if require or add more measures and calculated fields. 

 

 


@mittalpatel130 wrote:

I'm wondering if I've to create measures and KPIs again in Power BI Data Model then what is the use of Tabular in between my data source and Power BI? just to join the Dim and Fact tables? 


If you want to use import mode in Power BI there is no point using a Tabular model. It is a waste of time and resources to build a Tabular model in between your data source and Power BI if you just going to import the data into Power BI anyway.

 


@mittalpatel130 wrote:

What is the most efficient and reliable connection type with Tabular model? Import or Live Connection? 

If I go with Live connection, report developers won't be able to add more sources if require or add more measures and calculated fields. 

 


If you already have a Tabular model or you want an official model that has been tested and proven that you don't want anyone altering then using a Live Connection is the way to go. You can add report level measures to a model in live connection mode, but you can't add new tables and columns.

 

If you want to use Import mode I would avoid building a Tabular model if you can, they are not a good source for reading detailed information. And populating the model is an unnecessary extra step that wastes time and resources. 

Hi @d_gosbell 

 

I am also trying something similar, and totally agree with your point on importing data from SSAS Tabular to PBI Desktop.

 

But when we connect with Live Connection, we cannot create any columns. This way we are restricting users. 

In my case, end users wasnts to create some substrings, LEFT, CONCATENATE etc. This is purely end users requirement on any text column they would like to. But when we live connect they are not able to do this, and have to come back to Developer to make changes in Model.

 

I even tried importing as one single table in to PBI desktop, but all the fields are imported as Text fields, that is not helping and also we dont want to import data as its already present in SSAS.

 

Do we have alternate solution, if my end users should be able to create small columns connecting Live to SSAS Tabular cube?

 

Thanks

Manoj


@manalla wrote:

I even tried importing as one single table in to PBI desktop, but all the fields are imported as Text fields, that is not helping... 

 


The column data types depends on how you do the import. If you use the user interface to select a table for some reason query is executed as an MDX query. This has the side effect of only bringing in the "correct" data type for measures and all attributes are treated as text.

 

You can either edit the query and correct the data types OR if you enter a custom query using the form:

EVALUATE '<tablename>'

You will find that the data types from the model should be retained.

 


@manalla wrote:

 

Do we have alternate solution, if my end users should be able to create small columns connecting Live to SSAS Tabular cube?

 


No there is no other solution at present. I know there are ideas posted on ideas.powerbi.com to allow for composite models when using SSAS data sources, but I don't think Microsoft has announced any roadmap for this sort of functionality. 

Tahreem24
Super User
Super User

Hi,
Live connection only you can use while connecting it to analysis services like tabular or mutildimentional cube.

For more deets please refer the below links:
https://community.powerbi.com/t5/Community-Blog/Power-BI-Live-connection-vs-Import-comparison-and-li...

https://radacad.com/directquery-live-connection-or-import-data-tough-decision

Please give Kudos and also accept this as a solution if it helps you!
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.