cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mittalpatel130 Regular Visitor
Regular Visitor

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

Accepted Solutions
Super User
Super User

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


@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

6 REPLIES 6
Tahreem24 Member
Member

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

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!
Super User
Super User

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


@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)

mittalpatel130 Regular Visitor
Regular Visitor

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

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. 

 

 

Super User
Super User

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


@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

manalla Member
Member

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

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

Super User
Super User

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


@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. 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 218 members 2,541 guests
Please welcome our newest community members: