Tagged Questions

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

learn more… | top users | synonyms

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 ...
2
votes
0answers
193 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 ...
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 ...
1
vote
1answer
791 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
0answers
581 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
2answers
385 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
149 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.
10
votes
1answer
505 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 ...
1
vote
0answers
169 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 ...
5
votes
1answer
900 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 ...
3
votes
1answer
88 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
138 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 ...
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 ...
3
votes
1answer
277 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 ...
7
votes
1answer
174 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 ...
2
votes
1answer
540 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 ...
14
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 ...
2
votes
0answers
153 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 ...
0
votes
2answers
511 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 ...
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, ...
5
votes
1answer
646 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
895 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 ...
1
vote
1answer
3k views

SSAS -> New cube contains two fact tables. How do I relate these so I can use measures from both at the same time?

I am working on a new cube. I know that you cannot directly create a relationship between two fact tables (and expect good results). I am just wondering how to go about doing this I have read ...
0
votes
0answers
235 views

Cube dimension relationships confusion

I'm trying to get my cube working without getting "attribute key not found" errors, but the only way I've found to make it work, is to set up a referenced dimension from Dim_PropertyInfo to ...
5
votes
1answer
1k views

SSAS Cube in Excel - Show only applicable rows with KPI?

I have a cube built in SSAS 2008r2. I'm exposing it for the moment through an excel PivotTable. I recently added a KPI to the cube, and when I show the KPI in my PivotTable interface it causes ...
6
votes
2answers
249 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 ...
3
votes
1answer
2k views

SCD Type 2 Dimension -> Is this the correct layout for a type 2 scd with this type of data present?

Here is an example picture of my layout. As you can see I have my SCD types present (status/startdate/enddate/businesskey). My key is a surrogate key that identifies each record. My problem is ...
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 ...
1
vote
1answer
761 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
163 views

SSAS 2000 vs 2008 Cube processing times

I have been told that we can greatly reduce our processing times by rebuilding our 2000 cubes in 2008. I was told the difference is substantial (2hrs down to 20mins). Is this true? If so, why?
3
votes
1answer
155 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, ...
5
votes
1answer
335 views

Dimension Help - Deciding Fact or Dimension

We have the following Dim and Facts: Customer Dim: SCD Type 2, Info about the customer ie first purchase date, name, address, etc Product Dim: SCD Type 2, about our products Customer Snapshot ...
11
votes
0answers
268 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 ...
2
votes
1answer
710 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: ...
21
votes
2answers
8k 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 ...
1
vote
1answer
670 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
840 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
295 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 ...
2
votes
1answer
278 views

SSAS Dimension Help - Finding Values Not In the Dimension using Role Playing

I have a Fact / Fact Less table that has a list of Products likley to be purchased by client. Client ID Product ID 1 1 1 2 2 2 2 5 etc... I need to to allow ...
1
vote
1answer
121 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 = ...
1
vote
1answer
546 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 ...
2
votes
3answers
962 views

SQL Analysis Services - SSAS

Do you know what the best practices would be regarding SSAS and having real-time reports. I am not sure if we should be pulling data off our live database or using SSIS to move data periodically to a ...
1
vote
1answer
313 views

SSAS -> MDX -> Create a price comparision against resellers

I want to write an MDX query that will do a price comparison for a product against each re-seller and return a count which a 'greater', 'lesser' or 'equal' for a given month. Measure: ...
1
vote
1answer
902 views

SSAS / MDX Query - Add an extra dimension when using Order

I wish to add an extra dimension to this query, so that I'm able to see the total amount of an item sold, per month/year: SELECT NON EMPTY Order([Item].Children, Measures.[Total Line Value], ...
2
votes
1answer
199 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 ) } ...
4
votes
2answers
1k views

SSAS queries time out when Windows File System Cache drops

We have a SQL 2008 Analysis Services instance on Windows 2003 that is experiencing near-daily performance issues (query timeouts) during peak activity. A second identical instance queried from the ...
0
votes
1answer
173 views

BI development in Microsoft BI

I need to learn how to create a dimensional modelling for an organisation that do not has a BI tool and BI model. Where can I learn more about BI development that should be used in a company? IT's ...
4
votes
3answers
3k views

Datawarehouse Design: Combined Date Time dimension vs. Separate Day and Time dimensions and timezones

We are just starting design for a new data warehouse and we're trying to design how our date and time dimensions will work. We need to be able to support multiple timezones (probably at least GMT, ...
3
votes
1answer
196 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] ( ...
2
votes
1answer
723 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 ...