1
vote
2answers
21 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 ...
1
vote
0answers
40 views

How to get usage based optimization working on SSAS?

I have set up the configuration for SSAS to log the usage into the database, since the table did not exist I let it do that. However SSAS created the table OlapQueryLog under the schema of the SSAS ...
0
votes
2answers
113 views

SSAS tabular browse excel relationships not working

I'm currently building an education data warehouse. I have three factless fact tables for distinct set of grades due to the fact i have no measures (i'm not aggregating anything), the grades are ...
2
votes
1answer
172 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 ...
0
votes
1answer
210 views

Data Warehouse - Slowly Changing Dimensions with Many to Many Relationships

As an example, let's say I have a fact table with two dimensions and one measure FactMoney table ProjectKey int PersonKey int CashAmount money The two dimensions are defined like this: ...
0
votes
1answer
285 views

Kerberos Authentication Issue

I'm getting these errors when testing a linked server from SQL Server 2012 to SSAS 2012 (both servers are Windows 2012 and clustered) "An error was encountered in the transport layer" and "The peer ...
1
vote
0answers
1k views

MDX: How do I combine/merge/union two Measures dimensions with calculated members?

I am trying to combine two dimensions in an MDX query. SELECT NON EMPTY ( [XXX].[YYY].children ) ON ROWS, NON EMPTY { [Measures].[Dimension1A], [Measures].[Dimension1B], ...
2
votes
2answers
2k views

MDX Crossjoin filtering on different hierarchies from the same dimension is very slow

I have an MDX query that was written by an MDX novice that is excessively slow (the query, that is, not the MDX novice). And I am also an MDX novice. Here is the query: SELECT NON EMPTY ( ...
1
vote
1answer
308 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
113 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 ...
1
vote
0answers
51 views

how declaring `hierarchies and level` in SSAS help to improve MDX Query performance?

how declaring hierarchies and level in SSAS help to improve MDX Query performance? I use SQL Server 2008 R2 and I use SSAS and I reading book Professional Microsoft SQL Server Analysis Services 2008 ...
1
vote
0answers
163 views

how i can auto format mdx query in sql server [closed]

I Use SQL Server 2008 R2 And SSAS. I write/edit MDX Query and in some case complex i need format my MDX Query like Key (Ctrl+K)(Ctrl+Y) in SQL Tools Belt. after very search i find this site ...
3
votes
2answers
1k views

What are Measures and Dimensions in Cubes

I'm very new to Microsoft Sql Server Business Intelligence and Analysis Service(but I'm programming for years with SQL Server).Can any one describe Measures and Dimensions in Cubes in Simple words(If ...
10
votes
3answers
475 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
122 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
68 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
0answers
39 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 ...
3
votes
0answers
112 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 ...
1
vote
0answers
126 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 ...
4
votes
1answer
2k 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 ...
5
votes
2answers
1k 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 ...
3
votes
1answer
1k 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
1answer
636 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
508 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
318 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 ...
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 ...
7
votes
1answer
169 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
461 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
0answers
147 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, ...
5
votes
1answer
574 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 ...
6
votes
2answers
242 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 ...
1
vote
1answer
681 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 ...
3
votes
1answer
151 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
319 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
262 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 ...
21
votes
2answers
7k 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 ...
2
votes
1answer
254 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 ...
2
votes
1answer
187 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
979 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 ...
4
votes
2answers
2k 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
184 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] ( ...
1
vote
1answer
127 views

Migrating or Synchronizing SSAS Changes

Assume you have 2 SSAS databases, DB1 and DB2, that are identical. Now they branch off. Changes A and B are made to DB1 and changes C and D are made to DB2. Is there an easy method to migrate ...
1
vote
1answer
1k 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 permisions on the cube they get applied depending ...
4
votes
2answers
1k views

How do I stop refresh conflicts between Proactive Caching of facts and timed refresh of Dimensions in a SQL Server Analysis Service 2005 OLAP cube?

I have a SSAS 2005 OLAP cube which we've very nearly made into a ROLAP cube using a mixture of proactive caching for the fact partitions and timed refreshes for the dimension data. There are two fact ...