.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

MDX Measure * Total Measure

Posted By:      Posted Date: September 26, 2010    Points: 0   Category :Sql Server

Company-Branch-Client -> Income -This FY-This Month


Company-Branch-Client -> Sum Income for Last FY


member [Measures].[LastFY] as
sum([Last FY by Invoice Date],[Measures].[Income])

member [Measures].[ThisFYThisMonth] as
sum(([Current FY],[Date].[Month of Year].&[1]]),[Measures].[Nett Income])

select non empty

 on 0,
non empty


on 1
from [cube]
the problem is that I got all clients  vs all clients, I need just compare Income this month vs Total Income of full lats year

View Complete Post

More Related Resource Links

same measure total differs in same cube


Hi.  Folks at http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/4dad8e33-315e-4d36-a781-94448ce7e7ad seemed anxious to mark my finding "answered" so I'm posting here for an explanation of what seems to be a bug in ssas.

I am getting different totals in one measure group depending on whether the ssas db is first deleted before processing.  The total is incorrect if the db isnt first deleted prior to a "process full" on this measure group.  The measure involved is sourced from a money column that uses (all) 4 decimal places not just 2.  For over 300 million facts, I havent seen the error amount to more than $1.15.  But that is unacceptable in this app.

To some extent we've exhausted the money vs currency display argument.  This doesnt seem to matter if we do a full deployment after the ssas db is deleted. 

Is this a bug?  Does it only affect measures sourced by money columns?  Only if they go past 2 decimal places?  Only after a large number of deletions and a few inserts in the source star table?

Normally, this measure group processes Add.  Will I find that isnt working either?   The star source row deletio

Speed up Running Total MDX calculated measure?


Hi all, I'm using the follow mdx to keep a running total of the Period Balance measure in my cube:

SUM({[Due Date].[Date].CurrentMember.Level.Item(0):[Due Date].[Date].CurrentMember}, [Measures].[Period Balance])

It works great, however it's really slow as the amount of data displayed increases. I can't use a MTD or YTD because the users may be analyzing data that overlaps years. Any way I can speed this up?

Thanks in advance.

CLR Inside Out: Measure Early and Often for Performance, Part 2


In the second of a two-part series, Vance Morrison delves into the meaning of performance measurements, explaining what the numbers mean to you.

Vance Morrison

MSDN Magazine May 2008

CLR Inside Out: Measure Early and Often for Performance, Part 1


In this month's column, get the inside scoop on how to build performance into your apps from the start, rather than dealing with the fallout after you deploy them.

Vance Morrison

MSDN Magazine April 2008

Decision tree dependency measure

The dependency network viewer executes the stored procedure System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph which yields some integer measure that represents the strength of influence of input variables on the output variable. How this measures are calculated (information gain, chi-square, correlation etc)? It seems as if conditional influences are not taken into account: if A and B are two major factors which impact variable C, but A and B are strongly correlated so that C given A is not dependent on B, dependency algorithm will still depict B as the second major factor. Am I right? So there is no analogy of tests like conditional chi-square, conditional mutual information or partial correlation?   It seems to me that MS Data Mining lacks some kind of Bayesian Networks algorithm wich would illustrate conditional dependencies. That would give a useful insight on how various factors are related to each other and through what kind of chains a change in some input variable transforms to the output.   Thank you.

display non measure, non dimension fields in drill down

Hi All, I created my fact table with more fields than just the foreign keys linking dimensions and the field(s) to be used as mesures.  I did this hoping that on drill down I would be able to see the extra fields so that the user would have access to detail information on the records making up the measure amount.  The extra fields do not appear on drill down.  How can I make them appear, or am I on the wrong track? Thanks for any help

Measure Dependent Calculated Measure

Hi Was looking to create a calculated Measure Which needs to be dependent on another column of the FACT table. I need to have the Average of the particular Measure based on the fact that this particular 'set' has a common Related dimension's The example would give a better picture. I have a set of dimensions like STUDENT, TEACHER, COURSE etc as my dimensions and the grades, class standing based on marks for the subject, percentage of marks scored, Percentile, Marks Scored,StudentID and the related PK's etc are the measures on my fact table Here I want the Avg of the marks scored by the Students based on the grade say is  A or B or C  I was looking to create a calculated measure by using MDX which was a follows (NOT THE CORRECT SYNTAX JUST THE IDEA) SUM  (MARKS SCORED)/COUNT(DISTINCT StudentID) WHERE GRADE = A As this would be measured against a particular subject or a teacher or a Course. I was hoping if I could get help in putting this in a proper SYNATAX   Thanks in advance.  

Please help with converting calculated measure.

Hi, I have to dimensions: [Currency] with members CAD and USD and [Convet to] with members None, CAD, USD. The measures involved in calculation are [Amount Billed] and [Amount Received] The problem calculation is defined pretty simple: MEMBER CURRENTCUBE.[Measures].[AR] AS (abs([Measures].[Amount Billed] - [Measures].[Amount Received])>0.1,[Measures].[Amount Billed] - [Measures].[Amount Received] ,null); and worked perfect without currency conversion . I've read some info about currency conversion and designed this calculation to convert: scope (leaves([Time 2])); scope([Convert To].[Convert To].&[USD],[Currency].[Currency].&[CAD]); [Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]); [Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]); End Scope; scope([Convert To].[Convert To].&[CAD],[Currency].[Currency].&[USD]); [Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]); [Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]); End Scope; End Scope; That calculation provides correct results for both [Amount Billed] and [Amount

processing measure group : memory error : the operation cannot be completed because the memory quota

Hi, I'm stucked with this problem. Untill last week, the cube processed without any problem. Since last week, I'm getting this error. I have been searching in different forums, and I tried some suggestions, like changing memory limit properties, ... It is getting worse.. So I reset all properties to default again. I am running SQL-Server + MS-AS 2005 SP2 on server with 4GB of memory. This is a dedicated server, nothing else is running on it. The fact table has +/- 14 million records, several dimensions en 2 measure groups. I don't have problems to process the dimensions, but when I try to process the cube or the measure groups of that cube separately , the error persists. I have changed the datasource view, and replaced the fact table by a Named query. Even when I put a 'WHERE datapart( year , fact_date ) >= 2009 ' clause to reduce the number of records to +/- 5 million, I'm still getting the error. I don't understand what is wrong, the cube always processed since +/- 2 years. As I said, I have found a lot of this kind of Issues on different websites, I have been trying to change some properties. But this still does not solve the problem. Could it be that MS-AS settings are corrupt somewhere ? Is it a good idea to re-install MS-AS 2005 + SP1 + SP2 ? Or is there another reason possible ? I really appreciate any kind of help, because I'm

Optimize Calculated Measure containing COUNT EXISTING

Hi, My goal is to change the text color of all cells that contain aggregated values. Currently I achieve it like this: I COUNT the members of all attributes of all dimensions. To be multi-select-safe I am using the EXISTING keyword. If the members count of at least one dimension is not 1 than the background color of the cell is changed. CREATE MEMBER CURRENTCUBE .[Measures].[SingleCellSelected]   AS iif ((COUNT (Existing ([Dim1].[Attr1].[ Attr1].MEMBERS ))=1) AND (COUNT (Existing ([Dim1].[Attr2].[Attr2].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr3].[Attr3].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr4].[Attr4].MEMBERS ))=1) AND (COUNT (Existing ([Dim3].[Attr5].[Attr5].MEMBERS ))=1),1,0),   VISIBLE = 0  ;    SCOPE ([Measures].AllMembers ); FORE_COLOR (this ) = iif ([Measures].[SingleCellSelected]=1,0,16744448); END SCOPE ; This approach works but it performs badly with attributes with many members. Do you have any idea how to optimize this? Thank you!

Measure - New Customer Count

I need to create a measure which counts the number of new customers for each time period. My fact table contains the customer number and its easy to create a distinct count of customers per month/week/year. I'm thinking I need to obtain the first order date for the given customer and compare if the select period is within the time frame, then include or exclude.  

How to create a Measure without aggregations

Hi all, The data in the fact table has columns which are precalculated ranks for the products based on sales. How to create them as measures in the cube without any aggregations. I selected the properties for measure as "No Aggregations" while creating. But I dont see any data. Then I tried to create those columns as seperate dimensions by doing self join to the fact table.In this way I get the correct data,but the performance is slow. Is there any other way to try out which has both data and performance. Please help me out. Thanks, Sam

Help about cascadin M2M with parent child hierachy custom rollup for one measure

Dear MSAS Experts, My name is Andre and i am writing my thesis about data quality which needs to be finished in about 3 weeks.:-( For my thesis i needed to develop a Schema which store data quality issues. This is a part of my schema which includes a cascading M2M: Facttabele: ErrorFact / ChecksFact / RULE ID / ... DT_RULE: Rule_ID / ..... RULE_ATTRIBUTE_BRIDGE: RULE_ID / Attribut_ID => M2M beetween Rule and Attribut(Rules can belong to multiple Attributes) DT_Attribut: Attribut_ID / .... Attribut_KPI_Bridge: Atribute_ID / KPI_ID => M2M beetween Attribute and KPI (Attributes can be used for multible KPI calc) DT_KPI: KPI_ID, KPI_Name, KPI_PARENT => Parent Child Since MSAS do not provide a Agrgatefunction I have a calculated Measure (lets say its called 'EC') which calculates the average of ErrorFact/ChecksFact. Now the problem I do have is that the 'EC' Measure should be aggregated within and only in the DT_KPI dimension that every child which belongs to the same parent should be muliplicated ('*') with each other till we reach the all member node. So if we have the following PC-Hierachy: -A --B ---C ---D --E The result of A would be (C*D)*E = A. B of course would show the result of C*D. On the other hand all the other facts should use the normal aggregation for this dimension. Also EC should be aggregated normal in all other dimensions. I tried usin

MDX Query Measure with time period

Hi! How to set up the period in MDX? I need the measure what would be seted up by default 4, 29… days from today. For axample Amount from current day (02.09.2010) till 4 days from today is 06.09.2010. As Result I have to get a report: Buy form Vendor Amount today 02.09.2010 Amount +4days 02-06.09.2010 Amount +29days 02.09-30.09.2010 A 10000 250000 333333 B 150000 222222 555555 C 666666 444444 1222222 Sincerely, Milena

how to process just one partition along with other measure group and dimension in SSIS package Analy

HI All, i have to process just one partition1  of measure group A ,along with this i suppose to process all the Measure group and dimension with the help of SSIS Package Analysis Services Processing task. Partition1 having a query which fetch data only for previous day only. what i have done i select partition 1 in process data mode,all other measure group in full mode and dimension in process update mode.   i haven't taken measure group of partition1 and also not taken cube in the processing list ,when i run the package ,it runs suceesfully but data not get uplaoded into the Cube.   kindly suggest what other measures should i take to update the data . Amit

Calculated measure between two dates

Following fact table - Amount - StartDate - EndDate - SomeOtherDate   If the user selects a date in the time dimension I want to build the sum of Amount where the selected date is between StartDate and EndDate. Like this   SELECT SUM(Amount) WHERE <date> between StartDate and EndDate   Is it possible to create a calculated measure like this.   Thanks.

MDX - ParallelPeriod using a semi additive measure

Hello, I'm having some trouble trying to get a figure for previous year when using a semi additive measure (LastChild). For a measure that uses Sum the process would normally be: SUM(PARALLELPERIOD([Time].[Fiscal].[Fiscal Year], -1, [Time].[Fiscal].CURRENTMEMBER), [Measures].[Whatever] )   But obviously if I do a sum against a semi-additive measure it gives me a massive amount.   To give a little background the data that is in the source table is already a summed amount so the table is: Date                    Category            Amount 01/04/2010           Blah                   3654   etc.   Is there any way I can achieve an equivalent of the above MDX using a LastChild member? The only other thing I can think of doing is to split the source so that (for the above) 01/04/2010 in the blah category would have 3654 rows and then do a sum of that in AS.   Any ideas??   Many thanks in advance :-) yayomayn
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend