.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

same measure total differs in same cube

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server

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

View Complete Post

More Related Resource Links

Plot Running total charts from SSAS Cube

Environment: I am using SQL Server 2008, I have installed SSIS, SSAS and SSRS. Objective: Create a Effort V/S Defect plot in SSRS 2008 Approach: 1. Created the SSAS cubes with Effort and Defects data by timeline (rollup of data based on Week ending date) 2. Created SSRS project and linked the dataset to SSAS cube. But i am unable to plot a Effort (X-Axis) v/s Defects (Y-Axis). As soon as i drag the measures into the query designer in dataset properties, the effort rollup will break. Following is the data that has been created in SSAS. I need to plot a graph of Hours v/s defects (Running total). Please guide me on how to plot the graph using SSRS 2008 from the already created SSAS cubes Week Of Hours Defects 10/24/2009 8   11/7/2009 63   11/14/2009 68   11/21/2009 80.5   11/28/2009 139   12/5/2009 221.25   12/12/2009 131.75   12/19/2009 124.5   12/26/2009 61.5   1/2/2010 73   1/9/2010 153.5   1/16/2010 149.5   1/23/2010 196   1/30/2010 163   2/6/2010 155.5   2/13/2010 178   2/20/2010 138   2/27/2010 161.5   3/6/2010 189   3/13/2010 191.9   3/20/2010 240.5 9 3/27/2010 260.2 5 4/3/2010 214.5 13 4/10/2010 274.6 24 4/17/2010 200.5 15 4/24/2010 227 9 5/1/2010 237.05 18 5/8/2010 190.5 12 5/15/2010 156.25 6 5/22/2010

MDX Measure * Total Measure


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

How to update Cube.Measure "DESCRIPTION"



How to update DESCRIPTION from $System.MDSCHEMA_MEASURES without re-deploying and re-processing the cube.

I'm using the Cube.Measure DESCRIPTION propertie to let the end user know what the measure meens, I update this propertie offen, How to do this without needing to redeploy en reporcess the entire cube ?

many thanks,

cube total doesn't equal star total


Hi.  One measure of mine is defined as a data type of money in my star.  There are many occasions where a single value can go out to 4 decimal places.  But by design the sum of this measure across the entire fact table goes to at most two decimal places.  SQL returns a sum of $1,123,219,801.94000000 when I take sum across a subselect in which the measure is cast as decimal(20,8).   When I drag the measure to my pivot area in the cube I get $1,123,219,801.09.

Why?  What are my options for consistency between the two?

My metadata for this measure in the cube shows a display format of "currency" and a source data type of "double".    

Alter measure group: Impact on cube processing?

Hi All,

I have a XMLA script to alter source column (<ColumnID>) of a measure. I would like to know
following regarding the script:

1 - Do I have to 'Process Full' the cube or other processing options are applicable?
2 - The cube takes 2 hrs to process. Will there be reduction in process time processing the cube
    after running the script or will it take the whole 2 hrs?
It running cube on production so I would like to minimize the impact of the alter script.

Thanks in advance for any help.

Measure Error in Cube


Hi All,

One of our cubes has one measure showing #Error in SSMS and #value! in Browser. This is not a calculation and is coming straight form the fact table. The value shows up correctly in fact table and the cube processes without any errors. Any ideas?


Thanks in advance.

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

Obect reference error when using "Last Value" cube measure


Hi, I'm getting a pretty sweet object reference error when I change one of my measure from "sum" to "Last Value":

"Object reference not set to an instance of an object"

I get it when I try to process the cube from VS (SSAS 2008).  Once I change it back to "sum" the cube processes fine.  I have another cube, all else equal, that processes fine with a last value measure. I also tried "Last non-empty value" but that also failed.  Any thoughts?

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.

What is best way to create a measure that applies to 2 or more measure groups in a cube?


I'm designing an SSAS cube with 2 measure groups, each representing a business process related to Orders.  The fact table for the first measure group (Order Fact) is loaded when an Order is placed, and the fact table for the 2nd measure group (Order Check Fact) is loaded when a Check is cut to pay for the Order.  In addition, a third measure group (using Order Fact) has been created to calculate a Distinct Count of Orders (the measure name is [Number of Orders]).

Here is what the Dimension Usage tab looks like (all Regular relationships):




Distinct Count Orders

Issue with Report Model on a Cube (multiple measure groups)


Hi All, 

I have was asked to built a report model on the cube for some users and being new to report builder 3.0. 

I am having a weird issue. Lets Say I have following DIMS and FACTS. I have a cube built on top this Dimensional model. 

Two measure Groups/mearsures  : ClassFact/ClassHours and FacilitatorFact/FacilitatorHours.

used Many to Many relationship for FacilitatorDIM and ClassFact measuregoup. I now browse the cube it works excellent. 

The problem Starts

Now a report model is built on top of it, then in the query wizard when I pull both groups and then FName the Classhours(measure) Disappears. 

Does Report Modes supports Many to Many or am I missing something? Any help will be appreciated. 




ClassDIM (ClassID, ClassName) 

FacilitatorDIM ( FID, FName) 


ClassFact( ClassID, Classhours) 

FacilitatorFACT(FID, ClassID, FacilitatorHours)


Sample Data: 



SELECT     ClassID = 1 , ClassDurationHours = 100.0

FROM       ClassFact


SELECT     ClassID = 2 , ClassDurationHour

Show grand total running total in GridView Footer

In many scenarios we need to display Running total as well as Grand total in GridView footer. In this post i will try to explain in an easy way that how we can display running total & Grand total in a GridView footer combindly. To explain this solution using an example here i use a sales order report. The report contains all sales order amount in a tabular way. I will use a GridView to display sales order amount and use GridView footer to display Running total & Grand total. Let we have a customer table with id and name column plus an orders table with OrderID,CustomerID,OrderDate & Amount. Now our goal is to show all customers order with amount as well as page wise running total & grand total. Note that running total is necessary when you enable paging in a GridView where as Grand total is always you can consider.

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

UDP Delivers: Take Total Control Of Your Networking With .NET And UDP


UDP, the lesser-known sibling of TCP, is used for DNS resolution, SNMP network status, Kerberos security, digital media streaming, VoIP, and lots more. Learn how to put UDP to work for you.

Yaniv Pessach

MSDN Magazine February 2006

Total newbie question: Calling jQuery in an UpdatePanel?



Please forgive this ignorant question - I've used ASP.NET AJAX a good deal but I'm new to jQuery.  Specifically, is it possible to call or create jQuery events on the fly via an UpdatePanel?  For example, calling a jQuery modal or animating a Label (or <div> contained Literal), etc.

If anyone has any advice or could point me to some easy tutorials on mixing ASP.NET AJAX and jQuery, I'd greatly appreciate it. 



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