[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More







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.

Share tech news, updates, or what's on your mind.

Sign up to Post

I am newbie to SSAS. I created the DSV, a few dimensions and a cube. Once I deployed the cube, it got so many error message. I assume that there might be some configuration I did not do properly. Please advice! Thank you!

Microsoft Azure 2017
LVL 12
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Hi All

I have a Time Dimension in my SSAS that i need to extend, i have followed the following Article


but it does nopt seem to work can anybody assist
While using SSAS 2016 I have inherited a Multi-Tenant Cube.

This cube contains data across all companies.

How ever when you access the cube via excel using an assigned company level windows login that exists on the user dimension...its smart enough to only bring back data for that company.

My question is where does that logic exist that makes Excel smart? How does it know? We have no proxy site set up yet. Just a site where the mspump sits.
I am trying to create SSAS tabular model. I have a stored procedure which i can pass parameters which is suppose to pull in some result set.

I go to visual studio>Model>Import from Data Source>Microsoft SQL Server. I put in the servername where the database is and use windows authentication and provider the credentials. Then i select "Write a query that will specify the data to import" and i put the stored procedure with the parameter. It validates fine but at the table import wizard i get the error which i have attatched.

Its wierd because if i just use a test proc where i pass in a parameter(integer) and it returns a date. This works and i get the data set.

So i am not sure if it is this proc related. I read somewhere it may be because of the use of CTEs. So i changed the proc to use physical tables instead of CTEs, still i am getting the error. I also used set no count on. This also did not help.
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.
Hi Team,

Could you Process walk me through steps in processing the SSAS Cube (standard edition)  having multiple partitions(>3) for each measure group . Multiple partitions are required as it would impact the processing time in production having large volumes of data . The plan is to have the partitions defined on each Calendar Month. we want to have atleast 2 years of data . So 24  partitions for each measure group.  This is achievable using enterprise edition but I don't know how to achieve the same using standard edition .

Please Note : We cannot use the enterprise edition as it costs 4 times to the standard edition cost  and the objective is to have a workaround (working solution) to process the cube (with partitions using standard edition).

Please help me with the solution and will appreciate your quick reply .

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 …
 Please find attached Image.
My query
I have Created Data warehouse. For example
Dimension Table : DimSite, DimCustomer,DimSalesperson
FactTable : FactinvoiceDetails

DimSite is Include

SiteID    Sitename

1            United Kigdom
2           USA
3           France

SiteID       CustomerID CustomerName
1                R001             Ryan
2                R001             Bryan
3                R001            Stephen

SiteID       SalespersonID      SalespersonName
1                S001                       Mark
2                S001                       Stacy
3                S001                       Franck

My question is

I have to connect DimCustomer and DimSalesPerson to DimSite Table also
I have Write the DAX Function
See below
TotalDays = DATEDIFF([ChangeDate],TODAY(),DAY)
But same date giving 2 differents values
See attached Screenshot and see example
Change Date   TotalDays
12/09/2018     10
12/09/2018      2
Any idea appriciated
Amazon Web Services
LVL 12
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

My company recently signed up for Azure SSAS and I'm testing out a few aspects of it.  I created a tabular cube that has 6,000,000 rows both in our on prem SSAS server and in Azure.  The on prem cube performs great.  The Azure cube does not.  A simple query to count the number of rows takes over 3 min in Azure vs. instantly in the on prem server.  We currently have the Basic B1 pricing scale which I've read should be more than sufficient for our needs.  Does anyone have any advice here on what I'm doing wrong?  It seems like this should be an issue easily addressed.

Below is screenshot of my dimension hierarchy and the browser window of the hierarchy. As you can see in the browser of the dimension in the cube, you can see all of the dates.

However, when I access this info in Excel, I can only see the latest date, which is 8-20-2018.

Dimension Hierarchy

Dimension Browser
I have a SQL task called TransferInfo inside of a Container with other SQL tasks.

Every now and then, it appears that only the TransferInfo task runs twice. The other SQL tasks in the same container only runs once.

Please advise!!!
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.
I would like to know if it can be done in MDX or DAX for checking status in every date like the following.

I can build the cube based on the following data.  I would like to count the status for id 100,101,102 and 103 in each date. Attached a sql for creating the table.event_sql.txt
   id      event_date      status
100      01/01/2018      Pending
101      01/01/2018      Pending
102      04/02/2018      Pending
100      07/02/2018      Not Granted
101      14/02/2018      Granted
101      18/02/2018      Pending
100      15/03/2018      Pending
102      01/05/2018      Granted
103      03/06/2018      Pending

I would like to have the result like the following: (in 2018-01-01: (Pending:100,101); 2018-02-04(Pending:100,101,102); 2018-02-07(Pending:101,102, NotGranted:100 )....
event_date      Granted      Not Granted      Pending
01/01/2018                      2
04/02/2018                    3
07/02/2018             1      2
14/02/2018      1      1      1
18/02/2018             1      2
15/03/2018                    3
01/05/2018      1             2
03/06/2018      1             3

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]))
Problem statement: I am trying to do a writeback through .NET assembly stored procedure in SSAS - Multidimensional Cube

Are there any server level settings ? or any additional roles setting?

**Note: User is already an admin.

The .NET code works perfectly fine when debugged from the visual studio, the connection to the server works and the cube update also works.

1) When created an assembly using the dll file from .NET class library in the SSAS server, the calling of assembly stored procedure fails and throws error as follows;
a) Assembly Permission set : Safe/External Access
"Executing the query ...
Query (1, 6) Execution of the managed stored procedure UpdateCube failed with the following error: Exception has been thrown by the target of an invocation.Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed..
Run complete"
b) Assembly Permission set : Unrestricted
"Executing the query ...
Query (1, 6) Execution of the managed stored procedure UpdateCube failed with the following error: Exception has been thrown by the target of an invocation.An error was encountered in the transport layer.
The peer prematurely closed the connection..
Run complete"

Attached my .cs code which I build to obtain the dll file and then create an assembly on SSAS.
I decommissioned a database analysis server and need to move a tabular  model to a new server. I open the project in Visual Studio, change the workspace server yet when I try to compile I get "cannot build projects that contain more than one .bim file". There is only one .bim file in this project. Where is 2nd one?
We are switching to a DevOps local source control database environment.

In SQL Server Data Tools , In an Analysis Service project,  (I note there are no pre/post build events to take advantage of) is there a way to make DataSource Dynamic?

So when we deploy the cubes it takes the Environment into account.
HTML5 and CSS3 Fundamentals
LVL 12
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

I'm trying to write a conditional sum in MDX but its being applied to the whole instead of just applying to the criteria i specified. Does anyone know what i'm doing wrong?

WITH MEMBER [Measures].[PnLCalc] AS
  CASE [Voyage].[Vessel Tag].CurrentMember
  WHEN Instr([Voyage].[Vessel Tag].CurrentMember.Name,'ACCL') > 0 AND [Date].[Year].&[2018] 
  THEN [Measures].[PnL] + (150 * [Measures].[Net Days])
  ELSE [Measures].[PnL] END , FORMAT_STRING = "$#,##0;($#,##0)"  

SELECT  {   [Measures].[PnLCalc], 
							} ON COLUMNS,
						{ ([Date].[Month].[Month].ALLMEMBERS  ) } ON ROWS 
					FROM ( SELECT ( { [Date].[Year].&[2018] } ) ON COLUMNS FROM 
									( SELECT ( {  
															[Voyage].[Vessel Tag].[Vessel Tag].ALLMEMBERS,
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'FCCI') > 0
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'FCCBV') > 0
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'FCCSP') > 0																								   
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'ACCL') > 0
																Instr([Voyage].[Vessel Tag].CurrentMember.Name,'ACCBV') > 0
												} ) ON COLUMNS 
											FROM [cubeVoyageResults])) 
							WHERE ([Voyage].[Vessel Tag].CurrentMember, [Date].[Year].&[2018])

Open in new window

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.
Hello ,

I have a Tabular SSAS Model cube and have a measure that hours unused  percent like Hours Unused %:=(1 - (sum([CompletedHours])/sum([HoursLeft]))) * 100

In the table, I have area ie North, South, West and East. I also have Employees per area.

When I view the data in excel and in pivot view I see the Hours unused % showing correctly when I filter by an area but when I try to add in the employees into the rows it displays all employees not just the employees in that particular area.

I'm was looking to maybe use calculate table function, but I can't seem to get it right. Can anyone assist, please?

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.






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