.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

Replace pre-calculated Measures at "All" Level depending upon the Dimension Hierarchy Level

Posted By:      Posted Date: September 17, 2010    Points: 0   Category :Sql Server
Have an interesting Challenge; thought will reach out to you if you can help Currently I am working on SSAS 2008 OLAP Cube, whereby most aggregate level measures are pre-calculated using the C# Code as part of ETL and are stored in Data Warehouse as well as Measure Groups in Cube (MOLAP). These are mostly non-additive measures have to choose this approach for performance reasons, considering the complexity of the aggregations and data volumes.  Data  Volumes are Huge (about 300+ Million Rows/ per day),  98 % of measures are non additive / semi additive.  Cube is used primarily for advance analytics and will be eventually used for data mining like time series , what if analysis and scenario analysis  Excel is used as front end . Question is how can we replace the aggregate level data for various dimensions attributes (Totals and Grand Totals) from pre-calculated measures, those are also available in the Cube as measure groups? We are currently using Scope and Root statement which is not working as expected SCOPE ([D1].[H1].[A1], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A1],  > EndScope; SCOPE ([D1].[H1].[A2], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A2] > EndScope;   Thanks in Advance   Best Regards,   Dave

View Complete Post

More Related Resource Links

Dimension Security on some level and access to higher levels for Calculated/Scoped Measures



I'm trying to solve the following task:

I have a simple regular dimension e.g. calendar, there are some attributes: datekey (20100117, ...), month (201001, ...), year (2010).

There is a single user definded hierarchy called calendar:
datekey --> month --> year, the attribute relationships are configured accordingly.

There is one role that has only access to month 201001 (dimension data security - allowed memberset) - please be aware that I want to use "Visual Totals" or something simialr.

Now I have to calculate a measure: ([calendar].[calendar].[201001], [Measures].[sales]) / ([calendar].[calendar].[2010], [Measures].[sales])

How do I have to configure the security or calculate the measure that the sum of all months is used and by selecting the year only the Visual Total is shown?

Any hint is appreciated ;-)


Parent child hierarchy & MDX to fetch particular level & all members below it along with measures us

I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim

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

How to force a calculated member to sum from the leave level


Hi there:

Below is a snap short of my data set and hierarchy

Brand Product category Product Name Units Sale Product With Sale

Skiping a level in a hierarchy



Fact Table:

Tower count  region_id

12 1

23 4

32 2

45 3

.. ..

.. ..

DImension table:

region_id continentcode countrycode

1                NA                 USA

2                NA                      mexico

3             NA                     Canada

4            ASIA                    India

5          Europe       Germany


In my cube, I have added Region as one of the dimension,Continents and Countries are the levels in the hierarchy  and so if i want to select the values of all continents, i write the below query.This will sum u

How to create a wpf tree with multi-level hierarchy where the children are of the same type as the p


I have a structure of objects as below, where the child elements are of the same type as their parent.  The parent might also contain child elements of different type(s).

My object structure:

Main Site

Main Site

Sub Site

Sub Site

Sub Site


Need to access MemberValue of a dimension attribute regardless of level.


I have a Product dimension that has an attribute (Std Cycle Time) that is numeric. I need to use that value in a cube calculation.

How can I access the value regardless of how the data is cut?

I tried this method:

  [Dim Product].[Product - Std Cycle Time].CurrentMember.MemberValue
FORMAT_STRING = "#,0.000", 
VISIBLE = 1 ; 

But what I see in a Pivot Table when I pull this value in is 'ALL' unless I pull the Std Cycle Time attribute into the table.

I understand that behavior. If specific attribute is not pulled into the pivot table, SSAS defaults to the ALL member.

Question is I want to retrieve that Std Cycle Time to use it in another calculated member to compute a weighted average. Basically, I want to do this:

 //Compute the Weighted Average of the Std Cycle Time based on the runtime for the given product.

Display NULL value for all the dimension for a particular measure at the Aggregate(All) Level.


Hello All,

Please help me on this:

I have to display a measure value for all the dimensions as NULL at the aggregate/top/All level. For all the other dimensional levels, this measure value should be displayed. I used the following MDX, but it will hide the measure value for all the other dimensions(because while drilling through the other dimension, this condition will be satisfied by default):

SCOPE([abc].[abc].[abc].members, [MEASURES].[measure1]);      

 this = NULL;      




A Low Level Look at ASP.NET Architecture

ASP.NET is a powerful platform for building Web applications, that provides a tremendous amount of flexibility and power for building just about any kind of Web application. Most people are familiar only with the high level frameworks like WebForms and WebServices which sit at the very top level of the ASP.NET hierarchy. In this article I'll describe the lower level aspects of ASP.NET and explain how requests move from Web Server to the ASP.NET runtime and then through the ASP.NET HTTP pipeline to process requests.

Need help using control adapters at the machine level

Hi guys!

Let me explain our situation. I do web development at a university at which we have to meet strict accessibility guidelines. I've specially modified the adapters (as well as extended CompositeDataBoundControlAdapter to include GridView) for use on our site and to use with our global stylesheets as well as compiled the control adapters into a DLL.

The control adapters now all reside in the WebServices.CssFriendlyAdapters DLL.  (For example, WebServices.CssFriendlyAdapters.GridViewAdapter).

Currently, when someone drops an App_Browsers folder in to their site with the adapters specified, the adapters work. However, we'd like to be able to specify this in the C:\WINDOWS\Microsoft.NET\Framework\(version number)\CONFIG\Browsers directory so that the adapters work automatically without anyone having to move anything into App_Browsers for every site. To ensure that someone can still use the "old way" or will not be confused by their controls being adapted, I've written in code that uses the base rendering methods unless xhtmlConformance is set to "Strict" in the site's web.config file.

Anyway, on to the problem. When trying to compile the controlAdapters into the C:\WINDOWS\Microsoft.NET\Framework\(version number)\CONFIG\Browsers directory using aspnet_regbrowsers.exe (see http://msdn2.microsoft.com/en-us/library/ms229858.a

Wicked Code: Taking Silverlight Deep Zoom To The Next Level


With the help of Silverlight Deep Zoom and a remarkable control named MultiScaleImage, you can create scenes with many levels of zoom. Jeff Prosise illustrates with what else but the Mandlebrot set.

Jeff Prosise

MSDN Magazine July 2009

.NET Profiling: Write Profilers With Ease Using High-Level Wrapper Classes


Here Joachim H. Fröhlich and Reinhard Wolfinger show you how to get all the great functionality of the .NET Profiling API the easy way, with custom wrappers.

Joachim H. Fröhlich and Reinhard Wolfinger

MSDN Magazine April 2006

Test Run: Low-Level Web App UI Test Automation


As Web applications have become more complex, testing them has become more important. There are many testing techniques available to you. For example, in the April 2005 issue of MSDN®Magazine, I describe a simple JScript®-based system that can test a Web app through its UI by using the Internet Explorer Document Object Model.

James McCaffrey

MSDN Magazine October 2005

Test Run: Low-Level UI Test Automation


There are several ways to test a Windows®-based application through its user interface. For example, in the January 2005 issue of MSDN®Magazine (Test Run: Lightweight UI Test Automation with . NET) I described a lightweight technique for testing .

James McCaffrey

MSDN Magazine September 2005

Secure It: WS-Security and Remoting Channel Sinks Give Message-Level Security to Your SOAP Packets


As more organizations adopt XML-based Web Services, the need for message-level security has become evident. WS-Security, now supported in the Microsoft .NET Framework, addresses this need. Using the WS-Security framework, developers can implement channel sinks to intercept Remoting messages as they pass through the .NET Remoting infrastructure. The sink can read the message, change it, and pass it along. During this process, the message can be signed for added security. This article explains how to implement a Remoting channel sink that will modify the Remoting message by including a UserName token in the header, then sign the body using the token.

Neeraj Srivastava

MSDN Magazine November 2003

COM+ 1.5: Discover Powerful Low-Level Programming in Windows XP with New COM+ APIs


The new version of COM+ that ships as part of Windows XP includes APIs for low-level context programming. These functions allow you to create contexts that use COM+ runtime services, independent of objects and without registering anything in the COM+ Catalog. Designed for advanced COM+ developers who understand the COM+ context model, these APIs make it easy to integrate runtime services with code in nonstandard ways. This article explains how these low-level context APIs work, discusses when you'd want to use them, and provides a .NET-based wrapper to make it simpler to use the APIs from C#.

Craig Andera and Tim Ewald

MSDN Magazine April 2002

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