.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

Measures for Additive vs Non-Additive Data. What is cube design best practice?

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

I'm looking more for SSAS 2008 best practice design advice, rather than for an answer to a specific question (although I have a specifc set of examples).

First issue:  Creating a non-additive measure group and an additive measure group.  We have some fact data in our current cube that is additive, and some fact data that is non-additive; all stored in the same fact table.  We do not currently have measures implemented that reflect this aggregation distinction.  Question:  Is it considered good practice to segregate additive and non-additive fact data into a) different fact tables and/or b) different measure groups?  My thought is that it would be an acceptable design approach, but am looking for feedback.

Second issue:  Non-additive fact data is only available at a non-leaf grain.  The example here is that we have non-additive fact data which is only available for the 4th or 5th levels of our 6-level geography dimension.  Our solution has been to create a custom geography branch, which now essentially serves as our 'aggregation treatment' for non-additive fact data.  I don't believe it's a good practice to have the geography dimension serve this function because we end up having to create a custom geo member for each non-additive fact data element.  Question:  What is considered best practice

View Complete Post

More Related Resource Links

SSAS 2008 - I cannot seem to have semi-additive and normal additive measures in the same measure gro

I have a measure group with both conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty). The cube processes them quite happily, and I can view and analyse (slice) them fine, but when I analyse them both together, then the conventionally aggregated measures (aggregation type SUM) always dissapears in favour of the semi additive measures. This is not the case if I analyse conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) from different measure groups.Why does this happen, and is there a way to overcome this by pref not creating seperate fact sources for the conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) currently in the same fact source.

Pipeline Data Cube Design Question



The Analysis team wants to analyze sales pipeline data. Our CRM system(Source system) send the Data Warehouse team transaction as the Product moves through the different stages.


To mention a few: Creation, CallMade,LeadEngaged,CreditCheck,PapersReceived,Closed.There are more than 100 tracking statuses like mentioned above.


Sample transactions fact table:


Writeback for non-additive measures


I have a fact table with some measures that are additive, and some measures that are non-additive. In my application (planning/forecasting type), I would like to let the users modify the non-additive measures too , possibly at non-leaf levels, persist the changes and make them available to other users of the cube. The logic for spreading the non-leaf level changes is application-specific.

Given that the writeback ability exists only for additive measures, what are the recommendations for developing custom application enabling changes to non-additive measures  ? Is there a way of directly writing the changes at the leaf cells to the relational source and refresh the cube ?




Usability in Practice: The Tao of Screen Design


This article describes methods for designing screens in a user interface and the technology frameworks that support screen design.

Ambrose Little, Charles B. Kreitzberg

MSDN Magazine September 2009

Patterns in Practice: Incremental Delivery Through Continuous Design


The end goal of software projects is to deliver value to the customer. Software design is a major factor in how successfully a team can deliver that value. The best designs are a product of continuous design rather than the result of an effort that tries to get the entire design right up front. This approach lets you strive to apply lessons learned from the project to continuously improve the design, instead of becoming locked into an erroneous design developed too early in the project.

Jeremy Miller

MSDN Magazine August 2009

Best Practice: An Introduction To Domain-Driven Design


We give you a gentle introduction to designing and evolving rich domain models as part of integrating Domain-Driven Design (DDD) into your coding efforts.

David Laribee

MSDN Magazine February 2009

Patterns in Practice: Design For Testability


Designing testability into your app means smaller tests that are cheaper to create, easier to understand, faster to run, and much simpler to debug.

Jeremy Miller

MSDN Magazine December 2008

Data Binding: Give Your Everyday Custom Collections a Design-Time Makeover


In this article, Paul Ballard shows how you can provide data binding support for your custom collections to enable sorting, searching, and editing in as simple a manner as possible. In addition, he shows how to make all of these features available in the Windows and Web Forms Designers, just like an ADO.NET DataSet.

Paul Ballard

MSDN Magazine August 2005

Best practice? Accessing subset of data based on user


I have a DB and associated Asp.net 3.5 web app. The database holds information on our clients. We're adding client access functionality and I need to know how best to restrict user access to data only pertaining to their client.

The clients are stored in a hierarchy and each user can be associated with multiple clients. The user should only be able to access the data for their associated clients and any sub clients thereof.

My initial thought is to add a collection of clients to their profile, then use that to filter all queries; however, my gut instinct tells me that using the profile for such a security function is inadvisable.

Could anyone provide any insight on a best practice for this? I'm sure it must be a pretty common need.


Retrieve cube data directly from Relational database - is this possible?

I have a cube created in SSAS R2 2008. Now i would like to retrieve some data from this cube, using MDX language, and then to process it in Relational SQL database! Is this possible? I am new in SSAS, please help!  

IS There is any way out to process only one partition of cube and data of other partition remain ava

HI ALL. Please let me know is there is any way out to process one partition only,while data of other partition remain available in the cube with the help of SSIS

Cube Refreshment with Data updated in Table

HI All, I have cube which is MOLAP,and i have created SSIS package to process the cube,now i want to start the job as soon as data got uploaded into the Relational database. Please guide me in this as i am new to ssas. Regards Shraddha

Browse SSAS Cube Data From Your iPhone

Does the world want an iPhone app that allows you to connect, browse and pivot SSAS cube data?  What features would that app have?  Is there existing apps that are close?  Looking for others thoughts on this concept.  Thanks in advance!

Data-Bound Design: Create a Silverlight 4 Web Part for SharePoint 2010

When building custom SharePoint 2010 solutions, it makes a lot of sense to take advantage of Silverlight 4 on the front end. We'll walk you through the process of building a Silverlight Web Part with Visual Studio and Expression Blend.Paul StubbsMSDN Magazine September 2010

"Non-additive" target value in KPI. How to present it in a scorecard or Pivot Table.

Hi.. I would like to have some help in designing the following solution. It musn't be to hard, but i can't figure it out yet.. I have 2 tables: Group and Worker. Table Group has GroupID and GroupTarget. Table Worker has WorkerName, GroupID, WorkerTarget and Amount. The relation between tables is GroupID, as each worker belongs to a group. Now I want to take this to a pivotTable, to present KPI for each worker, and the value of the indicator is calculated comparing the Amount vs the WorkerTarget. Also I setup a single hierarchy in the PivotTable, where the parent is the GroupID, and when displayed, you can see all the Workers belonging to that group. Now the problem is, Everything is fine when I want to see the actual group total amount value, as I just have to SUM all the Amounts of each Worker, and I can see the GroupAmount. But.. ¿How can I display the GroupTarget in the PivotTable in the same column as the WorkerTarget? I don't mind a solution using SSAS or PerformancePoint, but my idea is in SSAS to put some kind of formula in the KPI target, so when I'm in a level of the hierarchy, the target is WorkerTarger, and when I'm in the parent, the target is GroupTarget. Is there some kind of formula like this??   If not, any other idea will be much apreciated.. Thanks a lot.. regards.. Canario O.

change data source of cube

how to change data source of cube?

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