SQL Server Analysis Services, an OLAP server bundled with SQL Server by Microsoft since version 7.0

learn more… | top users | synonyms

3
votes
2answers
1k views

ParallelPeriod returning null for Feb. 29 in date dimension

I have a calendar date dimension backed by a physical table of dates (originally created on SQL Server 2000, hence the datetime instead of date): CREATE TABLE [dbo].[PostDate_Dimension]( ...
3
votes
1answer
19 views

Formatting a Measure in SSAS cube

I have a measure that show number in millions / billions. The formatting of the number for now is like this : "$#,##0.0;-$#,##0.0" I would like to format the number to show something like this: ...
3
votes
1answer
916 views

Script out XMLA for all databases

I have several SSAS databases. I only want to script out the XMLA for them on a nightly basis. This will be a second tier backup in addition to the regular backups we have. How do I auto-generate ...
3
votes
1answer
2k views

Does msmdpump.dll pass the authentication info to SSAS?

I'm using msmdpump.dll with basic authentication to access a SSAS OLAP cube from the internet. Does this method pass the auth info to SSAS so if I put permissions on the cube they get applied ...
3
votes
1answer
30 views

After processing complete, SSAS DB slowly consumes all available space on disk, w. high memory use also

This problem is with an SSAS 2012 SP3 (11.0.6020.0) multi-dimensional server, hosting a single cube database, containing 1 cube and 16 dimensions. Aggregations have been designed using SQL Management ...
3
votes
1answer
104 views

Two facts, two dimensions, one query, one cube, can it be done and how?

I have four tables, two Facts and two Dimensions. Below I've listed the relevant columns of these tables, and the results I would like to see in the Cube. Fact Order table ---------------- SK_Order ...
3
votes
1answer
63 views

Wich is the most efficient/easy way for end-user to get data from Analysis database?

I know that you can quey a cube by connecting from excel to Analysis database and using formulas like cubevalue() or cubemember(). I also know that after converting the power pivot to formulas you can ...
3
votes
1answer
175 views

SQL Calculation based on 'similar' rows within table

I'm using SQL Server 2008 R2, building a fact table for SSAS. I can therefore accept solutions to this via either SQL query to SSIS task. Let's say I have a fact table that looks a little like this, ...
3
votes
1answer
286 views

Connect tables in a cube based on historical data

So this may be the wrong place, and may seem pretty basic, but I'm new to building up cubes, so please forgive me. Imagine a setup described by the following: CREATE TABLE [dbo].[Orders] ( ...
3
votes
1answer
110 views

How to check the health of a SSAS database? And of its backups?

regarding backups of the SSAS databses, based on the following behaviour: Backup on an empty SSAS database hangs Is there a way to check whether or not the SSAS backup is healthy? I would accept ...
3
votes
1answer
117 views

Analysis service database security at application level

I have got a SSAS Cube, we have written an windows application which would issue MDX queries to access the data from the cube and show it in the reports . This windows application is a licensed ...
3
votes
1answer
436 views

Help on designing SSAS Dim and Fact table

I am having a problem designing a Dim and Fact Table for the following problem. I have a Pivot Grid with data as below : USER | RELATED USER | RELATIONSHIP | PHONE NUMBER ...
3
votes
1answer
143 views

SSAS relationship/granularity help

Hi all I have 2 fact tables with a measure group each, Production and Production Orders. Production has production information at a lower granularity (at the component level) productionordershas ...
3
votes
0answers
196 views

Date dimension changes between Test and Production Server

I have a cube deployed into a Test environment and Production environment. I've noticed something strange happen with the date dimension. When deployed into our test environment, the Date Key comes ...
3
votes
1answer
97 views

SQL Server Analysis Services - where do I store user logins?

If I would like to create a Business Intelligence (BI) system using SSAS, with SSRS getting data from the SSAS cube. Which would be the best possible option to store users' credentials used with ...
2
votes
1answer
209 views

How to handle dimension hierarchies that re-join?

The obvious example is a time dimension where days roll up by day>month>year but also roll up days>weeks>year. BIDS complains about it, but I see no other way to model the situation.
2
votes
1answer
1k views

Why does an AS cube rebuild use so much TempDB?

I have a 50GB SQL 2008 database (only 36 of that is data) with a 24GB Analysis Server cube that rebuilds nightly. The cube rebuilds have been using more and more of the TempDB on the SQL server each ...
2
votes
1answer
606 views

Process Data cube from job on SQL server

I have an SQL Server Agent job on server(X) that has a step that is supposed to process a data cube on a remote server(Y). Whenever I run the job it fails and says that server(X) does not have ...
2
votes
1answer
984 views

Wrong aggregation of Measures in Analysis Service in combination with UNION and DESCENDANTS

Update: When I tried to provide a simple example which reproduces the issue I found the leveller in my database which introduces this issue. Therefore I did an update to this question in order to ...
2
votes
1answer
2k views

Need to filter dimension where two attributes are the same

I've obfuscated the scenario but the general idea still applies, so if the schema seems retarded, it probably is in this example... Let's say I have a Student dimension, and in it are two attributes ...
2
votes
1answer
1k views

Processing occasionally fails with “Unexpected end of file has occurred.”

I've got an Analysis Services database that's used for reporting on email activity. It mainly counts incoming and outgoing messages, who they're to and from, etc. Fact table: 11,367,910 rows Address ...
2
votes
1answer
39 views

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

On a SQL Server 2014, I have an SSAS tabular model which is querying a relational clustered columnstore table (with 800 mio. records), when the model is being processed. The query is just a simple ...
2
votes
1answer
77 views

Analysis Services Project: Data Source per Enviroment

I'm trying to configure a multi dimensional analysis service project, so that each developer of a team can configure his/her own data source (oracle). Changing the deployment target is easy, but how ...
2
votes
1answer
53 views

How to find out ALL the users that can connect to SSAS?

I have an user called mycompany\aives that is not listed in the list of administrators in the SSAS, not belong to any of the AD (active directory) groups listed there, however, he can connect to ...
2
votes
1answer
26 views

SSAS - how to script the General settings? where are them stored? how can they be queried?

I believe I am an administrator on server BIREPLON1 SSAS as you can see on the picture below. These security permissions can easily be scripted. this is an example: apologies foe the horizontal ...
2
votes
1answer
116 views

In MDX what is the ParallelPeriod of UnknownMember?

Lets say I have a time dimension with the levels all, year, quarter, month, day. I have some sales per month and a calculated measere [sales last year] WITH MEMBER [Measures].[sales last year] AS ...
2
votes
1answer
179 views

Update OLAP Cube data

We are using OLAP in our project and we are facing a scenario where we need to update the cube with real time data on daily basis. I need some suggestions about an optimal way for updating the data ...
2
votes
1answer
225 views

Backup on an empty SSAS database hangs

I created an SSIS package to iterate through all databases on an SSAS instance. It grabs the DatabaseId using AMO in a vbscript, then generates the XMLA to backup each database. Most databases ...
2
votes
1answer
491 views

SSAS Drillthough Limit

There is a limit of 10,000 rows that can be returned for a drillthrough rowset in recent version of SSAS. I'm concerned about the risk of of returning partial results without warning if the selection ...
2
votes
1answer
3k views

Tabular mode disabled during SSAS installation

I am trying to install SSAS in Tabular Mode. However, i find that the radio button is disabled. I am installing it on a separate instance as well. The regular Multidimensional SSAS had been installed ...
2
votes
1answer
893 views

Difference between SSAS 2005 and SSAS 2008

I'm asking this question since i'm planning to book a seminar, but the provider told me that they only offer trainings for SSAS 2008 or as individual training which are expensive. So my question: ...
2
votes
1answer
144 views

SSAS cube fact and dimension relationship

I have a cube that all the tables has one row per ID and have measure in all of them except for the calendar dimension. When I view the measures in excel from Table 1 with attributes from table 2, I ...
2
votes
1answer
18 views

Unable to display parent-child heirarchy

I am creating a parent-child hierarchy in my dimension. The AdventureWorks2012DW sample database is used. I have set the ParentEmployeeKey, NameColumn property to LastName as per the wrox sql server ...
2
votes
1answer
75 views

SSAS and SQL Server on the same server

I have a server that is primarily used for SSAS, heavy processing, however I have SQL Server on it alongside SSAS, but I don't want sql server to hold ssas back. I have set the SQL Server MAX memory ...
2
votes
1answer
117 views

MDX query is SLOWER with warm cache

I have a MDX query that executes faster on cold cache than warm cache. If I ran the same MDX query after restarting SSAS it takes 14 seconds. All subsequent runs of the same statement then take 50 ...
2
votes
1answer
323 views

How to verify a SSAS Backup and how to perform Integrity Checks in SSAS?

I have a procedure to backup SSAS Databases that is working successfully. I also delete the SSAS backup files that are older than 2 days. What I am looking forward to implement is: 1) How to verify ...
2
votes
1answer
930 views

Huge Analysis Services log file: msmdsrv.log

I've just discovered our prod analysis services is writing to a log that is 1.6 GB in size. I was able to get a copy of the log file saved off to an alternate location and open it in WordPad ...
2
votes
1answer
2k views

SSAS : An error occurred when instantiating a metadata object

Aside from installing SSAS, I have no experience using it. Our developers are saying that it is no longer possible to create a new aggregation, whatever the cube. The error occurs when hitting ...
2
votes
2answers
879 views

Analysis Service Connection String

I use sql server 2012, and have multi cube. I can connect to my created cube from Excel (Data Tab\Connections) with windows authentication. How can set permission to SQL Server user on my cube. and ...
2
votes
1answer
116 views

Debugging SSAS Timeouts

I have a drill-through problem apparently related to security. Users in one role are seeing timeout failures when trying to invoke a drill-through action. They have permission on the action through ...
2
votes
1answer
765 views

Cube Designer Aggregation Wizard Optimization Zero Percent Every Time

I am pretty new to SSAS. I have a cube (SSAS 2008 R2) that once had aggregations that has been created using the Aggregation Design wizard. When changing schema a few dimensions, i encountered an ...
2
votes
1answer
2k views

MDX - Sum values greater than then a median calculated measure

I'm relatively new to SSAS / MDX. I have a cube with a calculated field which calculates the Median value for a range of prices. MEDIAN( [DimItems].[Dim Items Id].CurrentMember.Children, ...
2
votes
1answer
1k views

Can't connect to Analysis Services or Reporting Services on localhost

I'm able to connect to my SQL Server 2008R2 localhost Database Engine and Integration Services. However, when I try to connect to Analysis Services, it fails with the message: "A connection attempt ...
2
votes
1answer
155 views

SSAS cell permission

I'm trying to restrict the access to the cube to certain dimension members. I Used the following function on the "Cell Data" tab of the role editor: [Magatzems].[Codi Magatzem].CURRENTMEMBER = ...
2
votes
1answer
250 views

MDX misunderstanding: complex FROM clause

I have a MDX query which syntax is familiar to me: SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS, NON EMPTY { ([Projects].[Id].[Id].ALLMEMBERS * [Priorities].[Id].[Id].ALLMEMBERS ) } ...
2
votes
1answer
541 views

SQL server 2008 Analysis Services DSO properties

We are planning to do Sharepoint and project server 2007 databases migration. The share point website also processes cubes. So we will be moving SSAS cubes from SQL 2005 to 2008 and then re-pointing ...
2
votes
1answer
50 views

MDX Intersection - how to?

A similar question has been asked here, but I'll give a brief intro. In SQL Server there are 3 tables: Order (Id, Date); Product (Id, Name); OrderRefProduct (Id, OrderId, ProductId); ...
2
votes
1answer
893 views

Running SSRS and SSAS from different server than the primary database instance(SQL Server Engine)

Right now both SSRS, SSAS and SQL Server Engine are on the same server. We are using 2008R2 Enterprise. We are thinking to upgrade to SQL Server 2012 or 2014. and We want put on separate serve the ...
2
votes
1answer
469 views

Cube Processing vs Database Processing

We have a SSAS database (SQL Server 2012 Enterprise) with 4 cubes in it. 1 of the cubes is significantly bigger than the other 3. We want to process the 3 smaller cubes at regular intervals during the ...
2
votes
1answer
1k views

Dealing with measure groups of different granularity

Let's say I have a simple data warehouse with the following fact tables: FactPurchase: one row per property purchased, with purchase amount, value, etc. FactMonthlyStatus: one row per property, per ...