Hot answers tagged

10 votes
Accepted

SSAS cube process time increased after making fact and dimension small

Processing a cube largely consists of 3 steps, Getting the data building indexes calculating aggregations Step 2 and 3 are the least expensive (during processing) in my opinion so let's start with ...
user avatar
  • 15.4k
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 ...
user avatar
  • 15.4k
10 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 ...
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 ...
user avatar
  • 15.4k
7 votes
Accepted

Cube with 2 measure groups (with different relationships to dimensions) returns too many dimension members in reports

As you correctly noted this is what happens when you try to display measures across a dimension to which they don't have a relation. You basically have 2 options Use IgnoreUnrelatedDimensions Use an ...
user avatar
  • 15.4k
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 ...
user avatar
  • 15.4k
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 ...
user avatar
  • 15.4k
7 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 ...
user avatar
6 votes
Accepted

Ordering of a dimension in sql server datawarehouse

Analysis Services will set the data type of the dimension attribute to be the same as the source column. It is sorted exactly as you asked for it. Your days are declared as a character type. Therefore,...
user avatar
  • 15.5k
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 ...
user avatar
  • 15.4k
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 ?.
user avatar
  • 61.1k
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]::...
user avatar
  • 15.4k
5 votes
Accepted

Linked Server from SQL Server to SSAS not working - what is missing?

I found this article here. There it states that: To resolve that problem you have 3 options: Run SQL queries from data server (you need to be remotely connected to the database server) Enable use ...
user avatar
5 votes

Is processing required dimensions enough or to process the entire cube?

I think you are confusing things. First of all, processing the cube and processing the dimensions is sort of separate. I think you mean "processing the entire database". If you process only ...
user avatar
  • 15.4k
5 votes
Accepted

Process Data cube from job on SQL server

There are 2 ways you can go about this. You can either create a service account for the sql agent service and give that account permissions on the olap server, or you can create a sql agent proxy ...
user avatar
  • 15.4k
5 votes

How can I compare the number of transactions in the first weeks or months of different stores

We really need more detail on your measures and dimensions to give you an exact answer on this, but since you are using SSAS a few general rules apply. If you add more detail I can expand my answer ...
user avatar
  • 15.4k
5 votes
Accepted

MDX - Getting the last 6 month Dynamically

Assuming you have properly set the ValueColumn property of your CreatedOn attribute in your dimension (so that MemberValue comes back a date data type) then the following should work: select {[...
user avatar
  • 1,365
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 ...
user avatar
  • 15.4k
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 ...
user avatar
  • 15.4k
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 ...
user avatar
4 votes

MDX calculated member to band a measure

Try WITH Member [Age Band] Instead of WITH set[Age Band] Since you are doing a calculation based on the [Measures].[Age] Try the following and see if you can work with WITH MEMBER with member [...
user avatar
4 votes
Accepted

What known strategies, if any, can I use to validate a Slowly Changing Dimension?

Checking whether the dates are contiguous You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in ...
user avatar
4 votes
Accepted

SSAS - what could be a good starting point for memory settings?

Depending on how much RAM is on your server, below is a good starting point (given you have SSAS ONLY running on the server) : click to enlarge: Refer to :SSAS Memory Configurations for Common ...
user avatar
  • 61.1k
4 votes

How can I get the measures of the last month with MDX?

Without sample data or structure it would be hard to provide the exact code, but here goes. You need to look into the PARALLELPERIOD function. That function takes a Level from a hierarchy and ...
user avatar
  • 15.4k
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, ...
user avatar
  • 15.5k
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 ...
user avatar
  • 15.4k
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 ...
user avatar
  • 1,194
4 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 ...
user avatar
  • 15.4k
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. ...
user avatar
  • 15.4k

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