mdx

Can I Select a Calculated Member On the "Row" Dimension in MDX?

I know I can do this (quick example off the top of my head): WITH Member Measures.AnotherDataColumn AS [MyDimension].CurrentMember.Properties("MyProperty") SELECT { Measures.DataColumn, Measures.AnotherDataColumn } ON COLUMNS { [MyDimension].Item } ON ROWS But is there a way to include th...

Is there any way to use LINQ for MDX queries?

Anyone know if there are plans for LINQ to MDX . Does any way currently exist to use LINQ with MDX / CUBES ...

How to Retrieve and mail OLAP data in SSIS

I have a mdx query that returns valid results in Sql Server Management Studio and would like to automate the execution of this query and put the result into an email. SSIS seems to be the natural fit for this. I have been able to run the mdx in an "Execute SQL Task" and populate an object variable with the result, but I am unsure how to...

how to figure out which columns in the fact table are used for calculating measures in an OLAP cube?

I have to verify that olap cube data and the data from relational tables from where a cube is built is correct. And I will do so by writing the TSQL queries and compare the values with that of cube. But, I got stuck in the course of determining which columns are used for measure. How do I figure out which columns are used for measures?...

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! ...

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...

MDX, SSAS 2005 calculating SPLY (Same Period Last Year)

Hi, I'm trying to achieve the following: I have a measure: [Measures].[Sales] and a time dimmension: [Time].[Year - Month - Day]. I would like to add new measure: [Measures].[Sales SPLY] to work in the following way: MONTH-----------------Sales--------------Sales SPLY 2009 January--------120 000,00--------110 000,00 2009 February ---...

Sql Server analysis Services 2008 Dynamic dimension security using MDX expression

Some dimension tables in my warehouse: DML Site Id bigint Name nvarchar(256) Primary key (Id) DATA SiteId Name 2 Site 2 3 Site A DML UserSite Username nvarchar(256) SiteId bigint Primary key (Username, SiteId) Foreign key (SiteId) referrences Site(Id) DATA Username Sit...

Performance Implications of Calculated Members in SSAS

I'm wondering if there are any performance implications from adding a lot of calculated members to my cube. On one hand, it's nice to have things defined once, located centrally, tested, and available for use from any client which doesn't support MDX. On the other hand, some of these members I'm adding might not be used very frequently...

Cube Calculated Member Question

I'm rather new to SSAS and am completely stumped on how to solve this problem. I have a dimension called Thresholds. Within the Thresholds dimension there are the following Members: [Threshold Year], [Threshold1 Amount], [Threshold2 Amount], [Threshold3 Amount] I also have a measures called [Qualifying Commission], [Tier1 Amount],...

MDX ability to calculate ratio of sums from child levels

I'm very much a novice at MDX, and have no idea how to approach this problem. I'd appreciate any help, even if just pointers to where relevant functions are explained. I need to be able to calculate the ratio of sums, where the numerator is a trivial SUM measure, but the denominator is the sum of dimension-specific values. For those k...

Mixing MDX Drillthrough statement and SQL joins

I want to use an MDX drill through command but i also want to join it to some SQL tables as well. that is there will be an Id in the fact table and not a lot of other data I want to join this on to another table or view and produce a report based on those ID's returned in the drill down. Ideas? ...

Add two types of ranking to MDX query

Starting with the MDX below I'd like to add two ranking columns that evaluate the member "Close Ratio". The first rank would be based on the sales rep population. Let's say there are 100 reps then if no close ratios repeat the rep with the highest ratio would be number 1 and the rep with the lowest ratio would be number 100. The second...

can any body tell me the difference, why they produce different results?

1) select non empty [Measures].[carded transactions] on 0, {[Date Dimension].[DATE].&[39446]:[Date Dimension].[DATE].&[39505]} on 1 from [Food and Beverage] where ( FILTER( {( [Revenue Center].[Revenue Center].[Revenue Center] * [Date Dimension].[Fiscal Calendar].[Fiscal Day] * [Date Dimensi...

is it possible to have multiple search condition in filter funtion in MDX

Can there be multiple search conditions in the "filter funtion" in mdx. If possible how do I put [Measures].[x] > 0 and [Measures].[y] = 0 in filter(set expression, search condition) thanx ...

MDX - Concurrent calculations based on a "record time range"?

Dear MDX experts, - Is it possible to get the concurrent calculation based on a record time range? Lets say I have; 'start date', 'end date', 'used', and 'color' available... in my fact table.. Is it possible to get the concurrent of 'used' per time (the biggest sum of 'used' that happened during the same range), if yes - what about c...

How to do an aggregation in MDX similar to a 'sum over partition' statement in sql

I have a situation where I have a product and a time dimension, with a fact table of sales volume. Over time, various details about the product changes, with the except of the business key for the product. In my flat reporting from the cube, I want to include some aggregration at the 'business key' level, regardless of what other parts o...

How do I go about restoring an SSAS 2000 .CAB file using 2008 tools?

I am trying to work out through the sample code that comes with Fast Track to MDX. ...

Is the Tuple in MDX an implicit crossjoin?

Are the two statements equivalent? Tuple: SELECT {[Measures].[Volume]} ON COLUMNS, ([Product].[Product Id].[Product Id].AllMembers ,[Time].[Time].[Year].AllMembers) ON ROWS FROM [My Cube] Versus explicit crossjoin: SELECT {[Measures].[Volume]} ON COLUMNS, [Product].[Product Id].[Product Id].AllMembers * [Ti...