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

Top 5 Contributors of the Month
Post New Web Links

Writeback for non-additive measures

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

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 ?




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.

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


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

cell writeback question, normal and calculated measures



I have writeback enabled for one of my cubes and it's working fine. I'm updating regular measures that are summed.

However I have a calculated measure that uses these regular mesures in it's calculation. 

But when the regular measures are updated via writeback, the calculated measure stays the same. Writeback seems pretty useless if calculations that take those measures into account don't see the updated values. Am I missing something here? Do I need to alter the mdx of the calculated measure or something like that?


Thanks in advance.


Calculated measures, calculated cells, and custom rollups issue

Hi,  I'm a little bit confused according to the purpose of Calculated measures, calculated cells, and custom rollups as they lead practically to the same goal, is there any differnce between those modes from a technical point of view. And for the last one, the custom roll up the question how to perform this, I need a walkthrough that lead me to undersand the way to set a custom roll up to a given dimesion, I visited microsoft site but the inforamtion that I have is poor, so is there any other source that exposes that as a step by step example? Thank youThe complexity resides in the simplicity

Cube Writeback SQL Server 2008 R2 (SSAS, write back)

CUBE WRITE BACK in SQL SERVER 2008 R2 Did anybody get the SSAS write back functionality to work against a decent sized datawarehouse? I'm not asking about a little demo but a significant sized footprint e.g. 14M Rows measures, 6-8 wired Dimensions with 8-10 attributes each. I setup a test server using a Dell XEON ( 2x4core) with 48 GB RAM hardware and the latest Sql Server 2008 R2 release. I used the What-If scenario in Excel 2010 and modified a higher level, hoping SSAS would push the values down. After about 15 minutes and after having used 48 GB memory (on average 5% CPU) Excel throw an error - short on memory - and stopped the action. I found the same behavior in Sql server 2005 and 2008 and was hoping this would work now but apparently not so. Here are my questions: Is anybody using this at all? how can one calculated (roughtly) how much memory is requiered? is there any paper that describes best practise? Thanks for any help in advance, Dirk  

writeback + excel 2010

Hi, I am trying to use the writeback functionality of SSAS 2008 to give the user the ability to enter planned data. I created MOLAP writeback partition and excel connection. It works. After some actions outside of the pivot table  "What-If Analysis menu" becomes standard(Scenario manager, Goal seek, Data table).  And it remains same after returning in the pivot table. Why?   Thanks..

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

SSAS2005 Writeback Woes

I wish to create a cube that can be written to. In MSAS2000 this was a doddle. However, in SSAS2005 all does not go so well. I have followed the instructions to the letter and also "Googled" how to do writeback in SSAS2005 but whenever I attempt to write to the cube (via ProClarity or XLCubed), I get the following error message "Errors in cell writeback. Cell writeback failed because one of the cells is secured. [Microsoft OLE DB Provider for Analysis Services 2005]". In order to proceed with my current project, it is important that I get writeback working correctly thus any advice will be appreciated.

The Measures Hierarchy is used more than once in a CrossJoin Function

Hi Folks, I am encountering a rather frustrating error in my SSRS report. It deals with MDX so I am posting this question in the SSAS forum. I have to create a report which should look something like this.   YTD1  YTD2 Product Measure 1 Measure 2 Measure 3 Measure 1 Measure 2 Measure 3 A B C D             I am writing an MDX using cross join to fetch this data, while the MDX works fine in SQL Server Management Studio, as soon as I run in on the MDX Editor in SSRS, I get the error, "The Query must at least have one axis". The MDX that I am using is WITH SET   [Top 10 Manufacturers] AS TOPCOUNT   ( { [Product].[Manufacturer].[Manufacturer]. allMembers}, 10, ( [Measures].[SALES VALUES(000)], [PERIOD].[MAT].&[MAT01]) )   SELECT NON EMPTY   CROSSJOIN( {[PERIOD].[Months in Year].&[1],[PERIOD].[Months in Year].&[2]}, {[Measures].[AVG SALES PRICE], [Measures].[AVG SALES PRICE CU], [Measures].[SALES VALUES] } ) ON COLUMNS, [Top 10 Manufacturers] on rows   FROM [MYCUBE] P.S. I need both the measures as well as the Hierarchy (MAT) on columns. Any help will be greatly appreciated. Thanks, Suman Sarkar  

Created Calculated Measures Permenantly with MDX

I did some research on how to create a calculated measure in MDX, i found that by using MDX to create a Calculated Measure, the measure will not be persistent in the cube but only query-scoped or session-scoped. is it possible to create a persistent calculated measure using MDX? if not, where is a good point for me to start looking into xmla to create the calculated measure?

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

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

Granting write permission to measures

I would like to define a non-administrative role in AS2008 that gives members write-permission on measures. The only way I was able to make it work was by putting an expression in the "Read/Write permission" on the Cell Data tab. However, since cell security has a performance impact I would like to avoid this. I had assumed that granting "Read/Write" access to the measures dimension would allow the user to update measures but this seems not to be the case. Am I missing something or is cell based security really the only way to grant write access?

semi additive measure

Hi, I have have started working on SSAS recently. And was reading about semi - additive measure. And the most important point that struck me was all semi additive use the selected function for the aggregation with time dimensions and sum with all others. So i decided to test it by building a very small cube with two dimensions i.e. Period and Product.  Period dimension has single hierachy with two attributes i.e. year and month.  where as my product dimension contains only one attribute hierarchy i.e. product. I have defined a measure called closing stock with aggregate function being set to lastnonempty. Note: My period dimension is of Type Time I process the cube and start browsing it using cube browser. I first drag period hierarchy on my rows and measure in data area and it properly shows lastnonempty for that particular year. I remove period hierarchy and drag product attribute on rows but rather than summing it up it still shows lastnonempty for all products. Is there something i am missing? Please let me know. Regards  Niks  

Scope and Time Calculations measures

Hi All I’m using a cube with standard Time Member calculations (YTD, MAT, ..). In my project I need to apply these calculations only to a subset of my measures (ex: 10 measures in total, 3 changes by YTD, MAT,.. selection, the others 7 remain with Default Calculation) I tryed to work on “Scope” statement: I don't put in "Scope" the measures that have to remain unchanged. But it doesn’t works. When I select something (YTD, MAT) measure out of scope returns error. Any idea? Note, I would avoid to forced all other measures to “current Period” Loris

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

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

Best way to implement security for measures in SSAS


Hi SSAS gurus,


I have a requirement where I need to implement security on some measures such that it is visible to a set of users and not accessible to another set of users. Now, I also have SSRS reports defined on top of these measures which are accessible by both the set of users. I want the reports to be displayed such that the measures should be visible to the users with access but invisible or N/A should be displayed when the other set of users access the reports. What would be the best way to go forward for this?

Cheers, Jason 
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