How to query table data that is in tabular model in SSAS

Question

How to query table data that is in tabular model in SSAS

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
August 23, 2016 at 11:49 am

#149279

I have two tables and I created a tabular model in sql server data tools VS 2010. and I am able to see this data in the design window.this data is imported form sql server.This model.bim is developed in sql server 2012.
After the model is developed I deployed it to SSAS.
Now when I call this model in report builder I am not able to see the table data.
I tried to run those two tables in SSAS. using DAX lang.
For example table name is employee
Can any one let me know why I am facing this error?
evaluate
(
'Employee'
)
Executing the query ...
Cannot query internal supporting structures for column 'Employee' because they are not processed. Please refresh or recalculate the table 'Employee'.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Martin Schoombee SSCoach Points: 19128 More actions · Answer 1

As the error suggests, you need to process the tabular cube first. The structure is deployed, but without processing the cube there will be no data.

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 2

This is where I got stuck.

How to process the tabular cube first? IS there any thing we have to do in SSMS?

Martin Schoombee SSCoach Points: 19128 More actions · Answer 3

mcfarlandparkway (8/23/2016)
This is where I got stuck.
How to process the tabular cube first? IS there any thing we have to do in SSMS?

https://msdn.microsoft.com/en-us/library/hh213155.aspx

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 4

OK, I processed the table as well as cube as you say. May I know the syntax for tabular cube to see the data.

is it starts with evaluate or select?

For example table name is employee, I want to see the data form this tabular.now I opened a new query in SSMS and I see cube name under I see measures,KPIs and tables.

Martin Schoombee SSCoach Points: 19128 More actions · Answer 5

mcfarlandparkway (8/24/2016)
OK, I processed the table as well as cube as you say. May I know the syntax for tabular cube to see the data.
is it starts with evaluate or select?
For example table name is employee, I want to see the data form this tabular.now I opened a new query in SSMS and I see cube name under I see measures,KPIs and tables.

You can use either MDX or DAX to query the tabular cube. Please see the respective syntax guides on MSDN.

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 6

If you have any idea on adhoc reporting please share with me.

Here are my questions -

1) End users can create their own reports on the fly using some predefined elements in adhoc reporting.

2) the current state which is existing is not supported by SSDT when we move to Sql 2012.so we planned to create a tabular model with all tables and views.

3) I have created the model and deployed to SSAS. We are giving report builder 3.0 to the users and the model

If users want to create reports will they have to learn DAX language?

4) Becoz the end users may not know right? Is their any option to drag and drop tabular model to report builder with out writing DAX queries?

5) And also I observed that in report builder 3.0 there is no icon for desiging mdx querys in query designer window its their in Sql server data tools.

patrick.w.wheeler SSCommitted Points: 1796 More actions · Answer 7

Just linked to a tabular model using embedded connection string to SSAS. I was able to select my dims and facts and drop them in the spots I wanted to. It wasn't hard at all and this was off of a tabular model. All the objects appeared including KPI's, etc.

I would think normal business users would have enough to play with if you build out the model correctly. If they need something like a unique count or some sort of condition aggregate you'll build that in DAX at the model level and the users will just grab the object as needed. Of course you'll rebuild and deploy.....

Querying Microsoft SQL Server 2012/2014 - Certified

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 8

Hello,

As you say I created a tabular model in SSDT and deployed to SSAS. Now in report builder 3.0 I have created data source where my tabular model is deployed to SSAS. and when I created a dataset ( Linked tabular model using embedded connection)

I have selected data source and Query type default is selected as text..

When I click ok I don't see any fields under datasets.

You said like you are able to select dims and facts in my case I don't see this.

patrick.w.wheeler SSCommitted Points: 1796 More actions · Answer 9

Data Sources

User a connection embedded in my report

Select SQL Server Connection Analysis Service

Press build button

Put your server name and select the database you want to connect to, in this case is should be your tabular projects

Test connection then save OK > OK

Then move on to datasets and select the data course name you made. Defaults Datasource1

select query designer and your dimensions and facts etc should be present.

Querying Microsoft SQL Server 2012/2014 - Certified

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 10

Yes, I see here. I have a condition I just dragged my table on to dimemsion area by default under hierarchy a column is assigned from my table and operator is shown as equals. I try to hit execute query red icon at the top of menu seems like its not working its just clicking and going off and I don't see any data in data pane..

Not sure where am I doing mistake..

patrick.w.wheeler SSCommitted Points: 1796 More actions · Answer 11

Yeah - That's all I got works for me.

Querying Microsoft SQL Server 2012/2014 - Certified