Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17







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

When I try to create a ssas tabular project in ssdt I get the following error.

You are not an Administrator on the workspace database server (Servername).

Any suggestions?
On Demand Webinar - Networking for the Cloud Era
LVL 10
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

In PowerShell ISE, running modified code to create partitions programmatically from a good example, but said example doesn't work.
$as = New-Object Microsoft.AnalysisServices.Server
   # get the database
   $db = $as.Databases.FindByName($AS_DATABASE)
   write-host ("Opened database {0} " -f $db.ToString())

   $model = $db.Model
   write-host ("Found model {0} " -f $model.ToString())

Open in new window

The connection to the database works fine. But $db.Model doesn't find an object. According to the directions, I'm running this in SQLPS, which I presume makes it unnecessary to add a reference to the Microsoft.AnalysisServices.Tabular assembly but is otherwise the same. When I run a similar code in ISE with the assembly reference, I do reach the model, but then I'm stymied by a DataSourceID I cannot find.
I'm new with SQL Server, but am pretty skilled with BI tools using Power Query & Power Pivot and such.  To learn more about the upward limits of Power Query and to get to know more about SQL Server Analytical Services (SSAS), the Tablular model and SQL Server Data Tools (SSDT), I started a self-learning project using a large CSV dataset from the US Dept of Ed, with 1900+ columns, 98000+ rows (dataset is publicly available).   I found Power Query does a nicer job of parsing raw CSV data into correct columns than the SQL Server import/export wizard, and has fewer constraints.  For example, Power Query can handle sets with > 1000 columns.  Most of the USDeptofEd data columns contain values that can be unpivoted; doing so creates a file with 18 columns but with over 185 million rows!  So it seems best to load this data into a proper SQL Server database with tables.  Of course, to get SSAS to accept Power Pivot data model I had to split the tables into 2 so none had more than 1000 columns - which I did.  I successfully imported the data into a SSAS Tabular solution using suggestions at this this OakTree post.  This was all done on a i5-2320 3GHz 64 bit processor running on Windows 10 with lots of unused storage, but 8GB of RAM.  

Here are a few screen shots so you can see what's going on:Power QueryPower Pivot - data modelSQL Server Data Tools in Visual StudioPower QueryPower Pivot - Data ModelVisual Studio
1. Can anyone tell if my data is optimally stored in SQL Server? …
Users get error 'operation was cancelled because of locking conflicts' in excel, when cube is processing, they can access the report from excel or Sharepoint. I need to show custom messages from cube itself. any suggestions please In general how can I replace the system generated message with custom message

I have a simple MDX cross join that produces the following result

select crossjoin (   {[Product].children}  ,{ [Measures].[Actual], [Measures].[Plan] }  )
ON Columns,
[Region].children on Rows
From [cube]

eg product 1   product 1     product 2  product 2
     Actual         Plan              Actual        Plan

crossjoin result
I want to combine the member names from the 2 column dimensions into one name, to look like a relational grid
eg product 1 - Actual    product 1 - Plan       product 2 - Actual    product 2 - Plan

Flattened Result
The Products are dynamic - the Measures can be hard coded.

Has anyone got a head start on how I may be able to produce the flattened output?

Hello ,

We have a report that displays a data dictionary of the SSAS Cube and we have description fileds missing. I wanted to enable users to update the description for say the Measures for certain tables.

Is there any way to do this with excel ? or is there another free tool that will allow them (certain permissions only) to update or generate a script to update the say $SYSTEM.MDSCHEMA_MEASURES ?

Thanks in advance.
Hi I have a cube that I have setup a Report Action on that opens an SSRS report that contains a parameter of @JobNumb.  The SSRS Report is based on a database, NOT a cube.  I can get the Action to appear on the menu when I test the cube in Excel and open the report is SSRS, however I am struggling to pass the parameter over!  I have tried several variations and simply cannot get it to work.

The Parameter Name in SSAS is the same as the Parameter Name is SSRS.
The SSRS Server Details are correct in the SSAS Reporting Action as it opens the report successfully.
If I type in the job number itself as the Parameter Value, eg "170000007" then the Report runs successfully with the correct job no.

I can seem to find lots on passing parameters from a cube to an SSRS Report based ON a cube, but nothing about passing a parameter from a cube to an SSRS Report based ON a database as I think it makes a difference.

I am not good with MDX and have tried the following variations as the Parameter Value, some do return a value in the SSRS Report Job Parameter field (those with a YES) but the majority don't:

"170000007"      yes      works and the report runs successfully with the correct job no.

[Job Trades].[Job Trade].[Job No].&[170000007]      no      
[Job Trades].[Job Trade].[Job No].CurrentMember      no      
[Job Trades].[Job Trade].[Job No].CurrentMember.Name      no      
[Job Trades].[Job Trade].[Job No].CurrentMember.UniqueName      no      
[Job Trades].[Job Trade].[Job No].CurrentMember.Value      no      
I have a mobile report with one SSAS (MDX) dataset that has a parameter for the "Region". When I test the URL containing the parameter query string 2 of the 16 Regions don't populate any data on the mobile report. In SSMS and in data preview of the mobile report publisher ALL regions have data.

I've already checked the dataset for filters and I have none. Any ideas on what would cause certain MDX members not to pull up data?

Thank you!
Hi all,

I have a request from my client how to prevent any user accessing a cube from SQL Server Management studio. My problem is we are deploying and processing  a cube on dedicated server that we can not control all, it means  there are some local admin/ group admin accounts who are sys role privileges. They definitely view data from my cube database.

The question is here how I can prevent them viewing my data from cube although they are  local admin/ group admin accounts?

Hello Experts Exchange
I have followed the instructions of this question;

But when I browse the cube all my cost centres have the total amount that is in my data.

I have uploaded a screen shot to show what I mean.

How do I get the correct figures for the correct cost centre?


New benefit for Premium Members - Upgrade now!
LVL 10
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.


Can anyone help me regarding scripts for monitoring SSAS (SQL Server 2012).

-- Blocking issues
-- SSAS Services status
-- Cube/Tabular model Performance
-  CPU/Memory/Disk IO utilization.

Please see attachmentThe dimension is not showing value in browser but it have members under it.
See the attachment
down vote

I want to apply the Classification ID3 algorithm in my data mining project, I open a new analysis service project in visual studio 2008, and after creation a data source, data view, mining structure and mining model (which is Microsoft decision tree), and set the parameters : SCORE_METHOD = 1 , SPLIT_METHOD = 2 ,

I got the following screen after select input attributes and the predict one which is from another table:
and when I run the process model I got the warning mentioned in the picture

what can I do  with those tow continuous content type, and can I ignore the warning??

please advice.

I have a cube where I need to fetch data based on region. Region are AMS, APJ and EMEA.

When I select as "[Dim Location].[Region].&[AMS]" or [Dim Location].[Region].&[APJ]..I don't get any data

but when I select with member position, I get the result " [Dim Location].[Region].&[1] " ..

What exactly is the problem.. Please provide solution.

I need to get result by member name not by position.


Hi ,

Can someone advice me that the both SSAS Tabular and Multi Dimension can be installed in single serve in SQL 2012.

Will there be any issue running both instance?

thanks in advance
I have a an SSAS cube for a warehousing client. Everyday we get a count of different activity categories. For most of the activities they charge the total sum of activities for the month but for two of the categories they only charge the maximum value of the count for a single day in the month.

My plan was to set that as the CustomRollupColumn value for that particular category getting it to aggregate as MAX() instead of SUM().

The following returns the correct value as a calculated measure : MAX([Date].[Dates].CurrentMember.Children, [Measures].[WMS Count])
But when I enter that in the Custom Rollup Column I don't get any values returned.

I'm developing a SSAS 2012 cube and the users require to see dimensions and measures are some logical order on there Pivot view from the excel. the order of display is business requirement, I should be able to do that what ever order they ask.

So, it should be control from the SSAS cube.

Can we achieve this and how?  Please help.
I've added a KPI to my SSAS cube.  I want to add the KPI Goal to my SSRS report.  However, as soon as I add the KPI Goal to my report, the report becomes unbearably slow.

The KPI in SSAS is not complicated, but I am employing a case statement in order to limit the scope of the KPI Goal to only the Production Order Number level.  This KPI makes no sense in a global scale and only makes sense at the Production Order level.

Is there a way to improve the performance so I'm able to use the Goal in my report?  Or is there another way to limit the scope of the KPI without the performance impact?

The KPI Value:  
[Measures].[Labor Hours Per Cubic Meter]

The KPI Goal:  
    WHEN ([Fact Production Order].[Production Order Number].CURRENTMEMBER.LEVEL IS [Fact Production Order].[Production Order Number].[Production Order Number])
        THEN ([Fact Production Order].[Production Order Number].[Production Order Number].CURRENTMEMBER,[Measures].[Allocated Labor Hours Per Cubic Meter Goal])

I'm using SQL 2008 R2 Standard.






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