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

learn more… | top users | synonyms

2
votes
1answer
481 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 ...
1
vote
1answer
477 views

Get Results from DMV Query into SQL Server Table

today I began to troubleshoot an issue that I was asked to resolve, and for the first time I found myself querying in the SSAS database. I obtained a very useful set of results (who was connecting to ...
1
vote
0answers
291 views

SSRS switch statement

I have a report with a variable number of columns. I can hide columns with the ismissing function based on an expression like Iif(Fields!Site.ismissing, true, false). However, the hidden columns still ...
7
votes
2answers
714 views

Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts). My fact table is at the grain of requisitionKEY, ...
1
vote
0answers
2k views

An error was encountered in the transport layer - SSAS 2012 from Excel 2013

When connecting to an SSAS 2012 tabular model from Excel 2013 (Data... From Other Sources... From Analysis Services... fill in Server Name... click Next), I get the error: An error was encountered ...
2
votes
1answer
3k views

Security permission to only read SSAS Cube with Excel

I have an SSAS database set up on a SQL Server 2008 server. I have a Role set up with a group of users that I want to be able to access the Cubes in that database. I set up "Read definition" and ...
4
votes
1answer
261 views

Should I snowflake or duplicate it across my facts?

I'm building up a data warehouse to be used by SSAS to create cubes on, and I'm debating between two possible schemas. In my warehouse, I've got two different fact tables that tracking daily changes ...
10
votes
3answers
566 views

Is backing a website by a SQL Server 2012 OLAP cube considered reasonable?

I have been tasked with architecting a solution for a large retail chain. They want to allow each of its 1.2 million customers to log on to a web site to see the distribution of recent purchases ...
2
votes
1answer
90 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
161 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
0answers
45 views

How to remove leaf member values?

I have a dimension hierarchy that is related to multiple fact tables with different granularities. In the case of one fact table, it relates to the middle level of a three level hierarchy. When you ...
4
votes
1answer
686 views

How do I display subtotals in cube browser in SSAS 2012 (Sql Server Data Tools)?

Apparently this feature was available in previous versions but has now been removed. What are some alternatives?
2
votes
1answer
931 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
0answers
148 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 ...
4
votes
1answer
394 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, but even with a No Aggregation measure type I'm getting an ...
11
votes
0answers
275 views

SQL Server Analysis Studio naive Bayes attribute has too many states [closed]

I'm using SQL Server Analysis Studio and I'm trying to train a naive Bayes classifier. I've got an attribute called "item description" that basically represents products you can buy at a store. I've ...
1
vote
0answers
252 views

How to aggregate calculated measures involving dimension attributes?

The business case is as follows - I have a cube of sales transactions and a dimension of parts. One of the attributes of a part is the current cost, which is non-aggregatable. A user needs to model ...
3
votes
1answer
101 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 ...
4
votes
1answer
77 views

Simple filesystem cube

I'm trying to understand cubes so thought I would create a simple datamart to represent my filesystem. So my question is, if I wanted to create a simple datamart to represent my filesystem, how would ...
2
votes
1answer
159 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
331 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 ...
14
votes
1answer
1k 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 ...
3
votes
1answer
2k views

Real Time SQL Server Analysis Services Cube Processing Report

I would like to know if there is a way to generate a report to determine which SQL Server Analysis Services cubes are processing using SQL Server Reporting Services? I would like to be able to run ...
1
vote
0answers
162 views

How to model Week Commencing / Week Ending scenario

I’m reasonably new to data warehousing and I’m struggling to get a feature of my time dimension working. I’d like to model a Week Commencing and Week Ending type of scenario. I’ve created a DimTime ...
5
votes
1answer
1k views

How do aggregations work in SSAS?

I understand the concept of pre-aggregating data so that multi dimensional queries perform faster, but the descriptions I've seen of aggregations in SSAS seem to imply that an aggregation only ...
5
votes
1answer
4k views

How to simplify SSAS deployment from DEV to PRO environment

Here is my issue: In my company, there is a team of developper who creates cubes, dimensions(...) in SSAS in a DEV environment (let's call it SSASDEV). This environment is bounded to a SQL Server ...
2
votes
0answers
15 views

Real-Time SSAS cube status notification [duplicate]

Possible Duplicate: Real Time SQL Server Analysis Services Cube Processing Report I would like to know if there is a way to generate a report to determine which SQL Server Analysis Services ...
2
votes
1answer
304 views

Displaying parent attributes alongside child attributes

SSAS - Parent Child hierarchy - displaying other Parent attributes alongside Child Attributes For example: Employee Table +-----+-------+------+---------------+ | KEY | NAME | ROLE | MANAGERKEY ...
10
votes
1answer
639 views

MDX Query for RANK regardless of hierarchy

I'm hoping to add a calculation to my SSAS Cube (SSAS 2012) that will show the relative rank of whatever the current member is against all other members in that grouper, without specifying the ...
6
votes
2answers
3k views

How to balance SQL Server memory with SQL Server Analysis Services memory

We have many systems that have the same configuration One Server (virtual or physical) Running SQL Server (SQL) and SQL Server Analysis Server (AS) multiple cores 16gb RAM Each night the SQL ...
2
votes
0answers
229 views

Semi-additive measures for other dimensions

I am looking for a way to do semi-additive aggregations on a non-time dimension. The model I am working with is similar to a snapshot inventory warehouse. Specifically I am analyzing processes ...
7
votes
1answer
182 views

Dynamic Dimension Security

I have a cube in SSAS 2012 with about 30 dimensions, one of which is Company. The Company dimension contains a list of companies, of which there are several hundred in the cube. Our security model ...
0
votes
2answers
3k views

How do I design a Dimension and Fact Table from a relational database?

I'm new to using SQL Server 2008 R2 SSAS. I have the following tables in my database, that I wish to model using SQL Server Analysis Services: Table1 ServiceTypeID | ServiceTypeName ...
2
votes
0answers
676 views

Finding Microsoft.AnalysisServices.Xmla dll in SQL Server 2008 R2

I'm not able to find Microsoft.AnalysisServices.XMLA.dll that I need to import using Microsoft.AnalysisServices.Xmla; I have found some articles and answered questions for SQL Server 2005 and 2012. ...
1
vote
1answer
1k 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 ...
1
vote
2answers
432 views

How to connect to SQL Server Analysis Server via Management Studio command line

I can connect SQL Server Management Studio to a SQL DB by passing command line parameters like this: "C:\Program Files (x86)\....\Ssms.exe" -E -S SERVERNAME -d DB_NAME -nosplash (Obviously -E is ...
2
votes
1answer
407 views

SSAS - Inferred Dimension Attributes - are they necessary?

I have a cube with fact tables that have a parent-child relationship: Person - Case. Some dimensions are inferred from the parent, e.g. State in the example below. Person does not have a foreign key ...
1
vote
1answer
850 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 ...
1
vote
0answers
172 views

Configuring SQL Server 2008 R2 Change Data Capture with SSAS

I'm completely new to SQL Server Analysis Servers (SSAS) so you'll have to excuse my ignorance. I've been running through the MSDN SSAS tutorial get a handle on how to use SSAS. We have SQL Server ...
3
votes
1answer
91 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 ...
0
votes
0answers
143 views

How to design the table and its relationship?

I am having a problem designing a Dim and Fact Table for the following problem. I want to display my data in PivotGrid as below : First table shows that if i search a person, anyone related to ...
3
votes
1answer
334 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 ...
2
votes
1answer
607 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 ...
15
votes
3answers
3k views

Dynamically Define a Range in a Dimension

I have an issue that i face every time i decide to build a cube, and i haven't found a way to over come it yet. the issue is how to allow the user to define Range of things automatically without ...
6
votes
2answers
267 views

Few Questions about SSAS Cubes

I am newbie to SSAS 2012 and here are my questions: Can we have measures of complex types (table)? Is it possible to order the results by a specific measure or attribute? My cube can be modeled as ...
2
votes
0answers
157 views

MDX Block Calculation Optimisation

I have a query I'm trying to optimise. I have read an article here http://web.archive.org/web/20100823233821/http://www.biblogs.com/2008/03/30/optimizing-countfilter-expressions-in-mdx/ about ...
1
vote
1answer
1k 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, ...
0
votes
2answers
557 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 ...
5
votes
1answer
708 views

Custom aggregation for non-leaf level nodes to avoid double counting

I have a fact table with the following columns: WorkerName OrderId NumberOfPackagesPerOrder Note that neither WorkerName nor OrderId is unique in this fact table. Multiple users could work on a ...
2
votes
1answer
1k 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 ...