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

Top 5 Contributors of the Month
Post New Web Links

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

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

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

View Complete Post

More Related Resource Links

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

Create a banding dimension that groups by a calculated measure


I want to show a count of orders banded into different amount ranges., ie <1k, 1k-5k,6-10k, etc.  The cube  (SSAS 2008 r2 enterprise) has a reporting currency dimension so I need to do the banding dynamically as each order amount is converted into the selected currency before being grouped into the appropriate band.   I also want to filter by other dimensions such as product or region.  Given this, it is not viable to do the banding calculation in the underlying data source and I assume a calculated measure is most appropriate.

Your help on what this would look like would be greatly appreciated.


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

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

Need 2 Measure Groups for One Fact table



I have one fact table that contains all the measures.  The problem is that I want to have two measure groups that point to this one fact table.  Some measures would be in measure group A and some in measure group B but the underlying source still comes from the one fact table.  I haven't found a way to do this.

My solution currently is I have created another fact table that is an exact copy of the main one, that way I can create two measure groups.  The issue is performance, it takes 4 minutes to build and if I took one of those fact tables out it would be cut in half.

I would have thought there would be a way to create a measure group and drag what you want in there..but it seems that this isn't the case that you can only create measure groups based on how many fact tables you have.

Any help would be appreciated.


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,

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.

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

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.

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?

Measure groups not being processed when SSIS package run by agent job - why?



We have an SSIS package that builds a large analysis services cube by doing a "full process" of the cube.  The SSIS package normally is executed by a scheduled agent job.  This arangement has been in production for a year and had worked well until recently.  We use SQL Server 2008 and Windows Server 2008.

Recently, the SSIS package has been having problems when run by the agent job.  The agent job executes the SSIS package and the agent job reports that the job ended successfully.  However, the cube has not been usable because the measure group partitions are indicated as "unprocessed".  We also have determined that the pre-existing fact files in the ".prt" folder are being deleted by the SSIS package but are not being recreated by the package as they should.

This doesn't happen if we execute the SSIS package directly.  If we open the Integration Services server and run the cube-builder package, everything performs as it always has and the cube is fully processed.  When the SSIS package is run directly, it executes significantly longer than if run through the agent job, and we are convinced this is because the measure groups are being created.  We have repeated our testing enough to know that the measure groups always are proc

How to create a new measure group for distinct count measure?


There is a warning info on my distinct count measure saying "break distinct count measure to a new group to improve performence...", but When I create a new measure group, I cannot choose the fact table which contains the distinct count measure field, as there is already a measure group using this fact table.

Then how could I remove this warning info?

Regards! directfriends.net

Slicing a Calculated Measure by the Dimensions Used to Create It

I have a calculated measure defined like this:
CREATE MEMBER CURRENTCUBE.[Measures].[Reduced Count]
 AS sum(
When I run the MDX:
  [Measures].[Reduced Count] on 0,
    [Date].[Date Year].&[2011],
  ) on 1
from WH
I get the same number for all actions. That number is the correct total for the whole year. What do I need to do so that I can break down the reduced count and see how many for each of the valid actions? I am sure I am missing something obvious.

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