10 votes
Accepted

SSAS Tabular: ImpersonationMode that is not supported for processing operations

I agree the documentation on this is a bit terse, but the error message on the other hand is very clear. ImpersonateCurrentUser is not supported for models attached to a SSAS instance. This is ...
Tom V's user avatar
  • 15.7k
9 votes
Accepted

8000 character limit in OPENQUERY against Analysis Server

I need to query Analysis Service with OPENQUERY, handle the query of length greater than 8000, save the results to a temporary table or select them directly. sql-serverssasmdxEdit tags Instead of ...
David Browne - Microsoft's user avatar
8 votes
Accepted

Regenerating a cube relational schema in Visual Studio

That's not how it works. A .dsv (data source view) is generated by defining which tables/queries you want to use in your cube. The flow is: create one or more .ds data sources by defining how to ...
Tom V's user avatar
  • 15.7k
8 votes
Accepted

SSAS Model Refresh - Not enough memory to complete this operation Error

TL/DR: Add more Memory, reduce the size of your Model(s), and/or move either the SQL Server Services or SQL Server Analysis Services to a different server (e.g. scale out) Longer Explanation: We went ...
John Eisbrener's user avatar
7 votes
Accepted

SSAS - Which way to order an attribute?

You have your terminology a bit wrong which makes it hard to tell what exactly you mean by your 2 options but you should use the OrderByAttribute (which is what I think you mean by option 1). The ...
Tom V's user avatar
  • 15.7k
7 votes
Accepted

What are the most relevant similarities between T-SQL and MDX?

General notes First of all, T-SQL and MDX are 2 completely different beasts. SQL is intended to query tabular relational data, while MDX is intended to query multidimensional data. I'll start with ...
Tom V's user avatar
  • 15.7k
6 votes
Accepted

How to find the size, name and other details of Analysis server database?

You could use powershell to connect to the database and list a lot of properties. Use this script to find out what properties you can get from a database: $loadInfo = [Reflection.Assembly]::...
Tom V's user avatar
  • 15.7k
6 votes
Accepted

SSAS Hierachies duplicates when browsing

That behaviour is dependent on your KeyColumns setting. Given a cube with these 2 named queries in the datasource view Cities: SELECT 1 AS id, 'India' AS country, 'Calcutta' AS city UNION ...
Tom V's user avatar
  • 15.7k
6 votes
Accepted

How do I tell which Edition and Version my SSAS Instance is running?

The easiest way is : using SSMS -- connect to instance --> reports -> standard reports --> General There are other ways as well - How to find Analysis Services Server Version ?.
Kin Shah's user avatar
  • 62.2k
5 votes

How to calculate a percentage over a dimension attribute and get correct aggregates?

Your main problem is that you are storing a measure in your dimension. You will keep running into issues with multiselect and or aggregation unless you store the measure where it belongs, namely in ...
Tom V's user avatar
  • 15.7k
5 votes
Accepted

SSIS / SSAS: Handling Age Columns

You are confusing 2 things in your question. Since you state you have duplicate attribute key errors you are using the the age table as a dimension, but since you also want to find averages you are ...
Tom V's user avatar
  • 15.7k
5 votes

SSAS Realtime Processing

You need to look into proactive caching for your partitions and dimensions where you can set up notifications based on a tracking table. From the documentation: Proactive caching provides ...
Tom V's user avatar
  • 15.7k
5 votes
Accepted

administrator permissions on a SSAS database

from the comments by Denis Rubashkin Try to use "Run as administrator" when running SSMS, it solved the same problem for me and that worked for me too. to run ssms as and administrator press ...
Marcello Miorelli's user avatar
4 votes

Using SSIS to extract query data and present it through a cube

tl;dr; SSIS moves data. SSAS creates cubes. SSRS displays data. Given the following problem statements The client cannot directly query the database. The client can only query the cube. Therefore, ...
billinkc's user avatar
  • 15.9k
4 votes
Accepted

Using SSIS to extract query data and present it through a cube

If you just transfer the data to another server using SSIS it won't magically appear in the SSAS database, so you would need to change the SSAS solution. If your question is about providing access ...
Tom V's user avatar
  • 15.7k
4 votes
Accepted

The column 'Date Offset' in table 'Date' has invalid bindings specified

The problem in this case was that I was trying to replace an existing column also named 'Date Offset' - this was not a calculated column, but taken from the source table. Resolution was to do the ...
Neil P's user avatar
  • 1,274
4 votes

The 'Database' with 'ID' = 'xxxxxxx' doesn't exist in the collection

One thing that could cause this is if you use the Name of the database instead of the ID. For example, I created a database with with the name Demo1, so the ID defaulted to the same string. ...
Tom V's user avatar
  • 15.7k
4 votes

Regenerating a cube relational schema in Visual Studio

The only time you need to use Schema Generation is if you did top down development and have never generated the schema before. Now that you have previously generated the SQL schema, you can just ...
GregGalloway's user avatar
  • 1,385
4 votes

administrator permissions on a SSAS database

The SSAS permissions are completely independent from SQL Server permissions. If you don't have permissions, there is one hope: By default, members of the local Administrators group are also ...
Evandro's user avatar
  • 868
4 votes

Configure SQL Server Analysis Services (SSAS) to run on same IP as SQL Server instance

Analysis Services always listens on all IPs unless it's a clustered instance or running in a VM. So you can't disable the browser service in this configuration. If you can live with IIS and HTTP ...
David Browne - Microsoft's user avatar
4 votes

StorageEngineUsed set to TabularMetadata. For databases in this mode, you must use Tabular APIs to administer the database

It sounds like you are trying to process your tabular model (which is of version >= 1200) with XMLA script (so all in all command is in XML). That kind of tabular model has to be processed with JSON ...
Piotr Przybylek's user avatar
4 votes

SSAS database backup

The solution to this is fairly straightforward. Using SQL Agent Job Tokens, you can include the date and time of the backup in the filename to ensure a new file is created each tiem the job runs ...
HandyD's user avatar
  • 9,942
4 votes

8000 character limit in OPENQUERY against Analysis Server

You could try to avoid openquery and use something like SSIS or Azure Data Factory or some other ETL tool which doesn't have the 8K character query limit. Alternatively if you want to keep using ...
Tom V's user avatar
  • 15.7k
3 votes
Accepted

Monitor number of records returned in a long-running SELECT statement during SSAS processing

If you connect SQL Server Profiler to SSAS and watch the Progress Report Current events with an EventSubclass=ReadData the IntegerData column will show the cumulative rows read so far for that SSAS ...
GregGalloway's user avatar
  • 1,385
3 votes
Accepted

SSAS where are olap cubes stored

Dimensions in Microsoft SQL Server Analysis Services support either ROLAP or MOLAP. Open the dimension in BIDS 2008, select the root dimension node and change this in the properties window. For ...
Martin Smith's user avatar
  • 84.7k
3 votes
Accepted

Formatting a Measure in SSAS cube

Please try this format string: 0,,.0 \M $ This trick is a bit buried in the documentation.
GregGalloway's user avatar
  • 1,385
3 votes
Accepted

SSAS How to calculate Standard Deviation

The StDev function does operate at a rollup grain per the first parameter you pass in. If you always want to calculate the StDev at the lowest level then you can write some mathematically equivalent ...
GregGalloway's user avatar
  • 1,385
3 votes

SSAS How to calculate Standard Deviation

To get this to work you need: To add a column in your DMV to produce the square of the raw value. SELECT [Value], square([Value]) as ValueSquared FROM factTable A measure which sums the raw ...
Sir Swears-a-lot's user avatar
3 votes
Accepted

SSAS Multidimentional Drillthrough with Many-to-Many Relationships

If your drillthrough command includes a dimension attribute from a many-to-many dimension then it behaves as described: showing a row zero to many times. The easiest solution is to create a ...
GregGalloway's user avatar
  • 1,385
3 votes

Newdatabase option is not available in sql server management studio 2014

For some reason, SSMS doesn't provide this New Database menu option for SSAS Tabular. But you can easily create a new empty or shell database with the following XMLA script. Just click the XMLA button ...
GregGalloway's user avatar
  • 1,385

Only top scored, non community-wiki answers of a minimum length are eligible