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

learn more… | top users | synonyms

4
votes
1answer
1k views

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

I would like to get the values of the measures of the previous month. That's what I have: SELECT [Measures].[Recuent distinct] ON ROWS, TAIL(NonEmptyCrossJoin([Dim Date].[Calendar].[Month ...
1
vote
1answer
74 views

Permissions Error on Member that doesn't exist

My team built an SSAS cube a year or two ago; it was the second cube we've built. We rolled it out to a few teams who have been using it without any issues. Today, I sat down with a new user to set up ...
15
votes
2answers
2k views

Recalculate Time Dimension in BIDS

I'm using BIDS to create a cube in SSAS 2008 r2. I used the wizard to create a time dimension. Part of that configuration is selecting a date range. After creation, I realized that I needed a wider ...
1
vote
1answer
48 views

Question regarding Excel connection to SSAS cube

Looking for a way to change the connection in an existing spreadsheet from old SSAS server to the new SSAS server without losing the data in the spreadsheet. Hardware refresh has made this change ...
3
votes
1answer
127 views

MDX - Getting the last 6 month Dynamically

I have a Dim table that called DimAccounts. It is describing a User account and the dates that the account has been created. for Example: I would like to get the list of accounts that have been ...
-4
votes
1answer
50 views

SQL Server and msbi relation and difference [closed]

With SQL Server 2008R2, we have analysis services, reporting services, and data moving and manipulation services with SQL Server Management Studio. So why do we need SSIS, SSRS, and SSAS?
33
votes
2answers
16k views

What does a DBA have to know about SSAS?

I've seen a lot of material covering the business aspect of SSAS, but not really much about the important aspects of administration and management. From the point of view of administering an instance ...
3
votes
1answer
512 views

SSAS cube process time increased after making fact and dimension small

I have a cube of 55 GB size which takes around 2 Hour to process full. As I have data for last 4 years but our business wants only 2 years of data. For that, I have changed dimension and Fact views to ...
2
votes
1answer
179 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
127 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 ...
4
votes
1answer
75 views

How do I make a nullable “parent” hierarchy level?

I'm setting up my first dimensional database with SSAS, and I have this [Materials] dimension that needs a hierarchy that goes something like this: [PriceCode v] --> nullable Price Code ... ...
0
votes
2answers
679 views

How to build dimensions from SCD tables

Having the foresight to begin collecting data from our production environment several years ago, I have a table with a following structure- Part Status Startdate Enddate I can write a relational ...
0
votes
1answer
61 views

Filtering by many-to-many table

I'm having a hard time to understand how to filter out result of multidimensional expression. This is my database schema. And this is my data. Manufacturer ╔════╦═══════════════════╗ ║ Id ║ ...
1
vote
1answer
73 views

Grant read/read-write access to SSAS cubes

I have a requirement to grant read access on SSAS cubes to one windows group and read/write access to another group. I have multiple SSAS cubes on my server. The question is: What minimum rights ...
0
votes
1answer
485 views

How to optimize performance for dimension attributes?

I'm looking to enhance the cube processing time and someone suggested me to set "AttributeHierarchyEnabled='False'" for attributes which were not used at user defined hierarchies but I'm not clear ...
2
votes
1answer
52 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); ...
3
votes
1answer
147 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 ...
4
votes
2answers
210 views

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

I need to make a comparison of the number of transactions per week and month of different stores since its opening date. The problem is that the stores has a different opening date. So, The idea is ...
1
vote
1answer
45 views

DSV - SSAS Project - New 2 Columns In Table Change More then I Planned

I encountered a very weird thing. I added in my DWH table 2 new columns but when I refreshed the DSV after adding this to my multidimensional project I saw that more (3) columns have been "changed". ...
1
vote
0answers
837 views

How to check the status of an SSAS cube while it is processing

When one processes an analysis services cube in Sql Server Management Studio, by right clicking on the cube name in the GUI and selecting the "process" option, a GUI pops up and displays the status of ...
2
votes
1answer
264 views

Migrating reports from SSRS 2005 to SSRS 2014 and SSAS 2005 cube to SSAS 2014?

As SQL Server 2005 end of life is getting closer I'm facing inevitable job of migrating databases, SSIS packages, SSRS reports and SSAS cubes. Since newest version of SQL Server is SQL Server 2014 we ...
4
votes
1answer
31k views

SQL Server is configured to allow remote connections

I use SQL Server 2012 and SSAS Tabular Mode (My Instance Name Is MSSQLSERVER2012T) Then I download AdventureWorks Tabular Model SQL Server 2012 From This link I open it with Visual Studio 2010 and ...
1
vote
1answer
717 views

SSAS Modelling - many to many with one dimension

I'm having bit of a hard time modelling an OLAP cube. My analogy is this: I have a list of students. Each one has one Major subject of study, and zero or more minors, up to five in total. The raw ...
0
votes
1answer
140 views

SSAS Calculated Field - rolling sum, for a year of days

I have a fact table storing employee occurrences incurred day over day. I need to have a running sum over the past year of an employee's life. I had, on first pass, done this on ETL, making this "...
4
votes
1answer
194 views

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

I have a customer dimension in our data warehouse (SSAS 2014 Multidimensional). It is set up as a Type II SCD, with just Start and End Date fields to track changes. (null end date = current). As our ...
4
votes
1answer
79 views

Problem when add administrator for Analysis Server ( SQL Server 2008 R2 )

I'm trying to add a user as an administrator in Analysis Server, doing this: 1 - Connect - Analysis Server - Right click on the Instance - Properties - Security - ADD : This number was already ...
0
votes
1answer
28 views

Tools to visualize adhoc multi dimentional data

With SSMS I cannot run a MDX query such as Select [Measures].[Internet Average Sales Amount] on 0, [Product].[Category].children on 1, Product.[Model Name].children on 2 From [Adventure Works]; ...
1
vote
0answers
76 views

Migrating SQL 2012 Analysis Services to another server

We have a SQL Server 2012 installation that was built on...let's say a compromised Windows installation. It's had so many installs/uninstalls done on it that nothing runs consistently, and we're ...
6
votes
1answer
634 views

Time as a measure

Is it possible to have times as measures in a cube? We're trying to view employee start times by day, aggregating as an average over time. Even with a No Aggregation measure type I'm getting an error ...
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]( [...
2
votes
1answer
19 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 ...
1
vote
0answers
430 views

how to pass the “file path” as a parameter in powershell?

I have recently added a SQL Server JOB on SERVER-A that goes to SERVER-B and backups all the SSAS databases. This seems to be fine. However when I want to delete backups older than 3 days, using ...
1
vote
1answer
399 views

How to migrate SSAS and SSRS from SQL Server 2008R2 to SQL Server 2012?

We are upgrading our SQL Server 2008R2 to 2012 and separate the SQL Server engine from SSAS and SSRS. We want put on Server 1 the engine and on Server 2 SSAS and SSRS. I did the side-by-side ...
2
votes
1answer
766 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 ...
1
vote
1answer
114 views

SSAS ErrorCode=“3239575554” Either the user does not have permissions to alter the 'SERVER' object

I have just installed SSAS on a server called SASBIO1, but when I try to run the script below, in order to grant Administrator rights to a specific AD group I get the error message: <Error ...
2
votes
1answer
27 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 ...
0
votes
1answer
534 views

SSAS / MDX Query - Need row total

I would like to add an additional row that will display the Total order count for all the different Customers in the Customer Dimension. Below is the query that I have been trying to modify. SELECT ...
2
votes
1answer
1k 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 ...
22
votes
1answer
2k views

How to calculate/store Top 10 in a tabular model?

We have recently created an SSAS tabular model so our users can access it via PowerView. We have a measure on one of our fact tables to get the TotalActiveItems using a formula: TotalActive:=COUNTAX(...
2
votes
1answer
137 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 (...
0
votes
0answers
160 views

Handle Duplicate entries in Fact table in Data Cube

We have been constructing data cube for our project in which we are facing a problem like 1 to many relationship in the dimension tables and making entries in fact table (duplication). ...
1
vote
1answer
125 views

SQL Server AS: Set ImpersonationMode via PS

Try to create a database in SQL Server Analysis Server (SQLAS, SSAS) version 2012 SP2 if that matters and set Impersonation Mode via Powershell. (try to avoid XMLA script). Here are my attempts. Does ...
-2
votes
1answer
396 views

how to delete multiple ssas database using xmla script

how to delete multiple SSAS database using XMLA script if possible please post me with syntax
1
vote
0answers
20 views

Process dimension task is querying the wrong column!

I'm in the process of building a new data warehouse and I am starting out by building a few dimensions. I have created a fairly typical "Date" dimension and that seems ok. Now I am trying to create ...
1
vote
0answers
24 views

Null Dimensions in SSAS [closed]

Several years ago 2008, I create a data warehouse using Business Objects. Business Objects did not process Outer Joins well and used SQL for all of its queries. Therefore, for every dimension I ...
2
votes
2answers
987 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 ...
0
votes
1answer
258 views

Is there a way to install both tabular and multidimensional modes?

I have started studying for the MCSE BI exam. I would like to have both the multidimensional and tabular modes available for studying/practice purposes. I have a SQL Server 2014 Developer Edition with ...
0
votes
1answer
44 views

Dimension design and MDX query issue

I'm new to MDX and SSAS but trying to learn. I'm on SQL Server 2012. I have the following table setup. My dimension relationships look like this: +-----------+----------+-------------------+-------...
1
vote
1answer
356 views

How to manually set Code Page and Collation for my SSAS Cube?

When I try to Process my SSAS2012 Olap Cube I get a lot of erros like this Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: 'dbo_ART' ,Column: '...
1
vote
2answers
2k views

Get distinct count on calculated member?

I'm using SSAS and created a measure group that is linked to Fact_SalesMetrics on SQL Server. In that, I have seven measures. All measures have aggregation of Sum except Sales Invoice which has ...