We help IT Professionals succeed at work.






SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.

I'm seeing very strange behavior after upgrading a Tabular SSAS Project to SQL Server 2019 (1500).  When I right click the Model in Visual Studio and go to Properties, the Compatibility mode is correct as shown in the attached screenshot: Model Mode
If I right click the Project itself, under Deployment Server, the Version says 13.0.  This is read only and I'm unable to change it.  The server was upgraded to SQL Server 2019 in the past couple days, so when the project was first create in VS, we were on SQL Server 2016.

When I go to the SSAS Server, right click the Server and go to Properties, I see the supported compatibility mode is 1500, Mode is Tabular and Version is like I would expect: Server Compatibility
If, however, I right click the Database within the SSAS Server, I get the following screenshot showing a Compatibility Level of SQL Server 2016 (1300): Database Compatibility
I have reprocessed everything, deleted the entire database and processing it again from scratch, cleaned and rebuilt the project several times, etc.  Nothing seems to resolve the database being stuck in the old compatibility mode.  Any ideas on what to try here?
I am working with a tabular model in Visual studio SQL SERVER 2017 and one of my underlying source table has changed design.
I would like to refresh the table on my model so that this change is updated on my tabular model.

When i look this up there are lots of references (https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/refresh-the-schema-in-a-data-source-view-analysis-services#bkmk_DSVrefresh) ,
 to go to the DSV (data source view) but i don't see data source view under solution explorer -
If i click on the 'data source' under tabular model explorer  but there is no refresh option.

I tried processing the database on full since someone mentioned that as a solution (https://stackoverflow.com/questions/54708558/how-to-update-tabular-data-from-source-tables)
but i'm still getting an error

I would appreciate any help or advice on this please?
Thank you
I have created a tabular model in visual studio [Enterprise 2017 version 15.9.04]
I have deployed it to my analytics server.

I want my college [Enterprise 2017 version 15.09.14] to be able to work on the model also, so they have copied over the complete solutions folder onto their own drive and they opened the file and can see the model and the tables - however when they look at the grid [on design view] it says 'does not support preview' and they can not see the data against the tables.

we have tried to rebuild the model on their machine, and refresh, all connections to the source are working. we then tested and imported a new table into their model, when they do this they can see the data for this table. but still nothing for the other tables.

What could we do to help this? If we want to build tabular models in the future, can we not build them on our desktops and deploy them and share the solutions with others to work on, do they have to be on the servers workspace (desktop).

We googled a few suggested solutions: we checked the configuration properties for the solution was tagged so when it was built it would build everything.
We also tried deleting the bin and obj file closing VS and reopening and and rebuilding everything. Can anyone please help?
I am following a tutorial on SSAS that was created using SQL Server 2016.
I am trying to find  a 2016 Developer Edition to download but Microsoft's site seems to only have the 2019 version
Any guidance appreciated.
Dear Experts
We are using CRM application which uses MYSQL database. we have customized the CRM application for our core process therefore the CRM modules marketing, sales and contacts and opportunities are not made use instead we have completely customized, the solution is deployed on premises.
Challenges: to build/develop certain reports which are very much essential for daily operations and monthly reports and analytics. At present we are taking csv dump from crm and preparing it manually by applying formulas,  this is time consuming and resource dependent and as reports are not ready made available therefore end users to depend on the technical resource to export the reports and bring it to the required format and then provided to the end users. We also require analytics from the CRM data.
We are planning to implement SQL Server Reporting Services (SSRS) and integrate with Power BI for analytics the data source will be CRM application either integrated OR excel as input to the SQL Server Reporting Services (SSRS) or Power BI.
Please suggest will this approach work for us.
Hi EE,

What permissions do I give a user in SSAS so they are able to assign cube roles to other users?

Any assistance is welcome.

Thank you.
Hi. I have a user trying to connect remotely to our MS SQL 2016 SSAS with SQL Server Management Studio and she receives the following error:

The system cannot contact a domain controller to service the authentication request. Please try again later (Microsoft.AnalysisServices.AdomdClient)

Note that I can connect successfully using another user account (standard domain user) on the same machine and even within the same user session (just using "run as different user").
Also, the same user account can login successfully using SSMS locally on the SQL server (via RDP).

I am trying to get the connection logs on the SQL server but I don't know where to find them.
It's only happening for this user. Do you have any idea?
I would at least need to find the logs on the SQL server.
I'm trying to write an MDX query that returns 1 aggregated row of data based on a portion of a row value. Kinda of like slicing via a dimension attribute that doesn't exist by itself.

Here is the MDX Query I have so far

SELECT  {  [Measures].[PnL],   [Measures].[Hire Rate],  [Measures].[TCE],  [Measures].[Offhire Days],  [Measures].[Net Days]  } ON COLUMNS,
															  { ([Voyage].[Vessel Tag].[Vessel Tag].ALLMEMBERS ) } ON ROWS 
														 FROM ( SELECT ( { [Date].[Year].&[2018] } ) ON COLUMNS FROM 
																			( SELECT ( {  
																								[Voyage].[Vessel Tag].[Vessel Tag].ALLMEMBERS,
																								   Instr([Voyage].[Vessel Tag].CurrentMember.Name,'20K DWT') > 0 									
																					  }   ) ON COLUMNS 
																				 FROM [cubeVoyageResults])) 
																 WHERE ( {[Voyage].[Module Type].&[Spot], [Voyage].[Module Type].&[TC Relet]},  [Date].[Year].&[2018])

Open in new window

I get all the 20K DWT variations but I just want one 20K DWT row not all its sub variations.

Is there a way to do that?

MDX Query Result Set
Cannot establish AdomdConnection in my ASP MVC C# application via the msmdpump.dll at all to connect to my cube

However if i use the same connection strings credentials to log into the Cube via Excel I have no problem.

Its gives me an "Object not set to an instant of an object"

lsConnectionString = "Provider=msolap; Persist Security Info=True; Data Source=\"https://reporting.q88.com\"; Initial Catalog=" + InitialCatalog + "; Cube=" + CubeName + "; User ID=HI\\vmDemo; Password=vmPassword1";

using (AdomdConnection connection = new AdomdConnection(lsConnectionString))

Open in new window

I tried various logins but still the same result. Any ideas?

and here is a stacktrace
at Microsoft.AnalysisServices.AdomdClient.HttpStream.ImpersonateLogonIdentityAndExecute(Action action)
   at Microsoft.AnalysisServices.AdomdClient.HttpStream.GetRequestStream()
   at Microsoft.AnalysisServices.AdomdClient.HttpStream.Write(Byte[] buffer, Int32 offset, Int32 size)
   at Microsoft.AnalysisServices.AdomdClient.CompressedStream.Write(Byte[] buffer, Int32 offset, Int32 size)
   at System.IO.StreamWriter.Flush(Boolean flushStream, Boolean flushEncoder)
   at System.IO.StreamWriter.Flush()
   at System.Xml.XmlTextWriter.Flush()
   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.EndRequest(Boolean useBinaryXml)
   at Microsoft.AnalysisServices.AdomdClient.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean 

Open in new window

Hi EE,

My developer is trying to resolve the error: A connection cannot be made to redirector. Ensure the 'SQL Browser Service is running'

Troubleshooting so far:

- Restarted the SSAS service and SQL browser,
- Allowed inbound connections on port 2832,2833 for the SQL Server Browser.
- A can log into the SSAS instance fine using the DC1DEVRPT01\DC1DEVRPT01 via SSMS.
- Gave the SSAS service dedicated port 3100, no fix. Although I am not sure I'm entering in the connection string correction:

Any chance this could be a permissions thing? I'm using Visual Studio 2017.

Attached is the error message and my config for SSAS.

Any assistance is welcome.

Thank you.
I have a long running query because it is reading a very large database.  The issue is there is a string of codes that each have an order number.  There can be as many as 30 on a  line. The order is then matched to another table to get the actual value.  What is the best way to loop through this row to get the codes for all of the 20 values.  This is an SQL statement.
We have an ETL process that populates a star schema database and then proceeds to create cubes. Problem being its blowing out the star schema ad re doing everything for scratch every single time. Which is incredibly time consuming. We are using SQL Server SSAS 2014 Standard. Is there a way not to do this anymore and just update the information that has changed?
need to to understand the physical structure of an OLAP database. And if I looked into a Oracle database, how can I tell if it's relational or OLAP? Isn't the OLAP data still stored in a table with keys that join to the other tables? I'm having a hard time picturing the structure and then I know querying is different too.

For years I heard these data warehouse jockeys talking about cubes, dimensions, and measures, craftily convincing the IT managers they know what they are talking about, and impressing the users that "this is the thing." But it always confused me. So I watched a few videos today, and the light bulb goes off - "oh, it's a pivot table".

btw, the cube is a nifty visual for a powerpoint presentation, but it's still presented in two-dimensions (piece of paper or a computer screen). Unless you have a 3-D printer I suppose ...

So I got the concept of measures, dimensions, etc., but now need to "get the picture" in what the data actually looks like and how to query it. I know this is bigger than one post here, but that's my objective.

For example, here's my confusion - I was also told today that an OLAP database is NOT relational in essence, it's totally different. In this article, you have
- the time dimension
- the product dimension
- the customer dimension
- the Fact table (total $ sales, quantity, discount)

BUT - don't all the dimension have keys that link it to the fact …
Hi, we are using Azure Hybrid connection to connect to our internal SQL server from an Azure web app. This works without issue, however we now have a requirement to connect to an SQL Analysis Services database from Azure.

We have created a connection using the same hybrid connector install and the status is coming back as "Connected". In our web.config file we are connecting to the analysis server using the string      <add name="MyConnection" connectionString="Provider=MSOLAP;Data Source=SQLSERVERNAME:2383;Initial Catalog=DBName;" />

When we try to reference the connection string we are getting the error message "An attempt was made to access a socket in a way forbidden by its access permissions"

This same web page connects to the internal SQL server fine.

We have tried setting up logging, enabling debug logs for the connection manager and looking in the SQL xp_readerrorlog but cant see anything that stands out.

Any help in getting this to work would be appreciated.
Hi Experts


How would you edit the following formula to count only Distinct items nested within the containsrow function...


OHA_OHP = CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CaseType]="Face to Face"), DISTINCTCOUNT(Data[WorkType]),CONTAINSROW({"IMA OHP OffSite","OHP OnSite","IMA Network OHP","OHP Review 30 Minutes", "OHP Case Teleconference call (40 mins)"},Data[WorkType]))
We are using SSAS 2016 Enterprise

We are building a mega cube with multiple companies data and we want to partition by Company.
We also will want to schedule separate ETL schedules for each company.

Question is in the event there is failure from a partition perspective will that take down the entire cube? In the event the data from one company goes wonky?

is it possible to connection Oracle OLAP , Oracle application server, Oracle BI answer and BI server to MS SQL ?

we are thinking about what if we just migrate Oracle DB to MS SQL DB and left the rest untouched.
Hi, I am connecting to SSAS data cube (tabular model) from Excel. This is company's operational data. I am wondering if there are any templates I can reuse to visualize data. What is the best place to start? Any books/video suggestions, other resources?

Thank you in advance.
When communicating with SSAS msmdpump via SOAP requests Analysis services returns with Content-Types that are compressed and in binary XML (Content_type: xs+xpress).

We would like to include an extra layer of abstraction between the client and Analysis services and would like to know if there is a way to have Analysis services send back plain XML or how to read the xs+xpress content-types.
Is there a way in SSAS 2016 and the latest version of Excel 2016 to add a value to the Description property of a Dimension Attribute and have that render as a tooltip when hovering over it in Excel?
I have an excel 2016 pivot table that is using a data connection to an ssas 2012 Olap cube

When I try to add a timeline slicer I get the dreaded

We can't create a timeline for this report because it doesn't have a field formatted as a date

I do have an order date attribute that is defined as a date field on the dimCalendar table

I have done some research and found some possible causes

one possible issue mentioned is that the field may have blank or invalid dates.
(I double checked this is not the case in my data)

The other was that the field needed to have the

ValueColumn of the date key to use an actual date field from the underlying SQL table.  The KeyColumn is an integer and the NameColumn is WChar.  and to set the ValueColumn to use type Date

I validated this was true as well

Anyone have some other thoughts
The attribute key cannot be found when processing: Table: 'dbo_FACT_PROD_QTY', Column: 'CONTRACT_RATE_KEY', Value: '0'. The attribute is 'CONTRACT_RATE_KEY'.
The attribute key was converted to an unknown member because the attribute key was not found. Attribute CONTRACT_RATE_KEY of Dimension: ContractRateLevel f

- I can process the dimension (ContractRateLevel), that the Fact table is pointing to with no problem.
- The dbo_Fact_Prod_Qty table does have Nulls in column Contract_Rate_Key.
- There are no Null values in the Dimension table: ContractRateLevel.

Can someone help, as I don't have this issue with my test cube, and the Fact table as NULL values for the same column.
I want Excel to prompt me each and every time for credentials each time i try to connect to the cubes. I don't want them saved ever.

Where does that setting live?
the two servers involved are sql 2014 and sql 2016
Our billing system is an OLTP database and it is very hard to report off of.   I have been reading on OLAP and ssas.  Is there a good manual or starting place for a rookie to try to overcome the reporting issues.
any ideas on should i start with trying to use ssas or creating an olap database from the online transactional database.

I'm getting the following error when I try and process my SQL data cube:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_DIM_GEO', Column: 'CITY', Value: 'COLUMBUS'. The attribute is 'CITY'.

How do I correct the issue?






SQL Server Analysis Services (SSAS) is an online analytical processing (OLAP) and data mining tool in Microsoft SQL Server used as a tool by organizations to analyze and make sense of information possibly spread out across multiple databases, or in disparate tables or files. Analysis Services includes a group of OLAP and data mining capabilities and comes in two flavors - Multidimensional and Tabular.

Top Experts In