ssas

what is the easy way to compare cube data with that of data mart?

I was trying to compare with the cube's data with that of data mart using TSQL query. It s hard for me to find where the columns used in the cube are coming from. Because the columns used in the cube are coming from data source view, but how do I know which table? Help appreciated ...

what if a fact table/view is a template(meant to contain only table structure but no data)?

I noticed that the fact tables used in a cube were actually views. Infact they were the templates of the fact tables (i noticed it in the script that "where 1=2" was used for the fact-views). So, if the template is used, there wont be any data in the view at any cost (and i dont know if I can insert in the view becasue I dont have inse...

cube partitions: is the fact table in the DSV designer just one of the cube partitions?

There are 16 partitions for a fact table. All 16 of them have the same structure and have the same aggregation scheme. 15 of them contains data and while the 16th contains no data. So, is the fact table in the DSV designer just one of those partitions? (the name in the header matches one of the partitions though) if it is so, then w...

Is there a way to specify that every cell should be brought back using MDX?

I'm using a query of the form: Select { ([Measures].[M1], [Time].[2000]), ([Measures].[M2], [Time].[2000]), ([Measures].[M1], [Time].[2001]), ([Measures].[M2], [Time].[2001]) } on 0 From [Cube] Where ([Some].[OtherStuff]) using Analysis Services 2008. Even though I'm not specifying NON EMPTY or similar, I'm still o...

how to find out which columns to join

Is there an easy way to find out the column dependencies between tables in the DSV of a cube? The reason I am asking this question is: When there are lots of tables (fact and dim tables) in the dsv, it is hard to follow the lines. Help appreciated! ...

Calculated members: SSAS

In the "Calculations" tab of an SSAS cube project, does "Measures" dimension represent all measures within the measure groups as well? For instance: Patron Revenue has measures actual win, cash in, cash out.... Patron Redemption has measures operator pay, redeeming dollars, redeeming comp... In calculation tab, [Actual Gross Profit] i...

Trace back columns in calculated measures

How do I trace back the actual columns used/joined in calculated measure? THe reason I am asking this question is, I am trying to write the equivalent TSQL query to verify the result with that of calculated measure. So far, my approach has been a look up into the measure properties and find the table/view and the column name used. The j...

Help needed on calculated measures

Patron Revenue Cube: In one of the calculated measures “Acutal Gross Profit”, there are four measures used as “[Measures].[Acutal Win]-[Measures].[Operator Pay]-[Measures].[Redeeming Dollars]-[Measures].[Redeeming Comp]” The first two measures belong to vw_fact_patronrevenue view and the other two belong to vw_fact_patronredemptio...

What is the best way to model a ragged hierarchy in SSAS for both ROLAP and MOLAP?

I'm currently trying to model a ragged hierarchy in SSAS and I'm stuck. First, I was trying to model it like so: ItemKey,Level0Key,Level1Key,Level2Key Item1,Lvl0-1,Lvl1-1,Lvl2-1 Item2,Lvl0-1,Lvl1-1,Lvl2-1 Item3,Lvl0-1,Lvl1-1,Lvl2-2 **Item4,Lvl0-1, , ** Where the last line in this example had blanks for the "missing" levels. Here, RO...

ssas cube: How is the data being pulled?

If there is single partition for a measure group, aggregation designed 0%, partition table being used as an empty table (a template), how does the cube browser still show data for the measure in that measure group. FYI: The partitions are generally created monthwise like vw_fact_patronrevenue_2007_1, vw_fact_patronrevenue_2007_2 and so ...

SSAS Versioning Without Source Control

What is the best way to manage and combine different versions of SSAS solutions, without using version control? Currently, we have a network drive where the "master" copy is stored. So individual develoeprs work with a local copy, but we recently ran into a problem with adding changes to the "master" copy. Any suggestions? Microsoft ap...

how to use market basket analysis (apriori) sql server 2005

I want to finish my final project, my project is about market basket analysis with apriori algorthm. I use C# and SQL server 2005. Can you help me please. I have a saving mart datasets. I am newbie and I don't know step-step how to use market basket analysis on sql server 2005. Please help me. ...

Add Analysis Services to a clustered SQL Server 2008 instance

I have an existing multi-instance SQL 2008 cluster, and I'm trying to add Analysis Services to an existing instance. I start the installation, pick "Add features", configure SSAS, and then on the final pre-installation check, I fail the "Existing clustered or cluster-prepared instance" check. Of course I'd fail this check - I'm trying to...

DSV (Named Query) vs. Database View

Please provide guidelines on when to use DSV's as opposed to database views. Any performance issue using one vs. the other? Environment: SQL Server 2008 ...

Hour of Day - SSAS

Hi, I would like to analyse data per hour using SSAS. The built in date dimension does not create any hour attributes. Currently I am creating a new table with HourOfDay and HourOfDayName fields and will use this table to create a date dimension. Could any one tell me if there is a common way of achieving time of day based analysis us...

SQL Analysis Services - query cannot be processed

I am running Analysis Services, added a dimension to the mix and now I cannot browse the cube. Browsing cube with Sql mgmt says there is an unknown error, running an MDX query in SQL Mgmt returns a value on one measure and null on other measures. How do I go the next level to work out what it is hitting in SQL? Can I run a SQL Profile...

What are the alternatives to SQL Server Analysis Services?

Are there any alternatives to SQL Server Analysis Services on the Windows x64 platform? I'm vaguely curious, because I haven't heard of any (though admittedly I haven't looked very hard). Basically, a product that allows multi-dimensional cubes and querying those to generate reports (though the generation and presentation of those repo...

SSAS Vs Pentaho

We are in a situation to decide one among MS SSAS and Pentaho. Is there any comparision list for both of them with pros and cons? Any help is very much appreciated. ...

Processing dimension in SSAS 2000

Hi, I have a shared dimension in SQL Server Analysis Services 2000. The dimension is used in two cubes. The dimension is marked as non-changing dimension. Names of some memebers in the database have changed. However no structal changes have been made. I process the dimension incrementaly, but the names of the memebrs stored in dimens...

Best Practices for Deploying SQL Server Projects (SSAS, SSIS, SSRS) across domains

Scenario: Production servers are running SQL Server 2008 in the domain myDomain.com Dev/test/stage servers are running SQL Server 2008 in the domain dev-myDomain.com Actual dev work done on local running SQL Server 2008 source controlled using SourceSafe First of all, does this setup/environment make sense? And, what are some good wa...