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

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

cell writeback question, normal and calculated measures

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


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.


View Complete Post

More Related Resource Links

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

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?

Use Report Action as default drillthrough on a calculated cell in Excel

Hi, I am using SSAS 2008 R2 and Excel 2010. Is there a way to override the default behavior from the cube to force an excel pivot table to perform a report action rather than a drillthrough when I double click a cell that is a calculated member? Currently, I get an error message. Truth be told, I wouldn't mind making all the double clicking in my pivot table cells pop a report for the user since the drillthrough formatting is so primitive, but I really need to know how to do it for the calculated cells. Thanks, James

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.

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

Calculated field Sorting Now Working , but have performance question


 Thanks to mitja.GTI.

My sorting is working ...

although i had to take the table that has my calculated column and make a view (so i could filter) and then bind the gridview to the view.

My question is as this.

1.) in order to get my calculated field i had to load my data into a table (as suggested)

2.)Add a column to the table and perform calculations

3.)make a dataview from the tables because i could not get the Gridview.datasource= ds.tables("loads").select("Deadhead <= " & sngRadius

4.)the largest of the 3 tables my query is based on contains about 7000 records. The way i have the logic & programming now everytime a new "truck location" is put in the textbox1 and "find loads/button1" is clicked. It calculates the deadhead miles of all 7000 records before filtering. I am filtering based on the deadhead miles.

Does anyone have any recomendation about how to speed this up a bit.

Also in order to get paging to work i had to rebind/filter the data again ... means looping through all 7000 records each time paging is called. This seems terribly ineffecient as i am already forced to use access instead of SQL.


Help with Calculated Measures


Hi All,

I need to calculate adjusted Revenue based on Total Revenue - Tax - COGS.

But my Tax/COGS are based on Product Types, Partners, etc...


                 Tax       COGS

Partner 1     10%      5%

Partner 2      5%       10%

Partner 3      5%       0%


My questions are as follow:

1. Is this kind of calculation best done in MDX or TSQL?

I have about 280K rows per month, and 28 calculations per row (e.g. c= a x b is 1 calculation, then d = c * 0.1 is another), so about 8 Million calculations.  It's done using Excel 2010 currently.

2. How do I deal with zero or empty cell, will MDX speed up the performance?

3. How do I do this kind of calculation in MDX?  I don't know how to setup dimensions with Calculated Measures.

The books I have seen so far shows me easy calculations like [Measures].[Total Sales] - [Measures].[Discount] but each combination of Partner/Product

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 ?




LinqToSql: Calculated Field Question...


Hi There,


Im currently using LinqToSql to get a list of groups from the database. (tblGroups)

In code, im iterating through my collection (tblGroups) and counting sub-elements of tblGroups and storing the whole object in a DTO (simple object) with an additional property (UserCount).

When these are stored in a simple object, the UserCount is worked out by looking through each group and counting the users inside and making sure they fit a certain role.


This starts to hamper performance when I have so many groups and i would like to take all of this to the database layer and work it all out initially.


My question is:

What is the best way to achieve this? I would ideally like to remove the DTO as this adds a whole new layer. Could I add a new property to the database (like a calculated field) that is populated everytime this info is request as say: a stored procedure?





Named Sets vs. Calculated Members and the Filter Field Conundrum (Year-to-Date measures by company c


I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

Question regarding Calculated Member Grand Totals


I have a Calculated Measure that was inherited. The logic is:



"[Time].[Time Hierarchy].[Month].&["

+ VBA!CStr(year ([ForecastMonth].[Month].

CurrentMember.Properties("Key0")) )

+ "]&["

+ VBA!Cstr(Month([ForecastMonth].[Month].


+ "]"

),[Measures].[Regular Cases])

If the [ForecastMonth].[Forecast Month].[Month] is assigned

Calculated measures limit?

Is there a limit to the number of calculated measures a cube can have?  Is there a performance hit after so many measures?

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 ;-)


Writeback expression question - Weighted Allocation (SSAS 2008 SP2)




I'm trying to nail down the behaviour of writeback.

The Expression needs to change depending on how the value should be allocated.


E.g. If I want to spread a value to the leaf level funds then:



[Fund.Currentmember, Measures.Value] / [Fund.All, Measures.Value]


I've noticed that the expression should cater for each dimension that needs to be spread.

So if it is by fund, and by product then it should have:


[Fund.Currentmember, Product.CurrentMember, Measures.Value]


[Fund.All, Product.All, Measures.Value]


I've also found that if I need to allocate a value to an actual leaf level value, then the denominator should also carry a current member for that dimension. (I don't want to divide the value of the current member by the all level)


Eg. If I want to capture a value against a particular fund, then the weighted expression would look like this:


[Fund.Currentmember, Product.CurrentMember, Measures.Value]


[Fund.Currentmember, Product.All, Measures.Value]


What I'm trying to figure out is this:


What if I want to spread a value by a grouping attribute?

Example, if there was a Fund Group attribute that held multipl

RadioButton inside GridView, How to get it work as normal

Did you tried before to drag a RadioButton control inside a Gridivew templatefield, and then you attempt to select these RadioButtons , you will notice that the behavior of RadioButton control will be changed and it will work just like the behavior of checkbox control! the user will be able to select more than one radiobutton in the grid!

Some basic MVC question

  1. In regular asp net I can run (debug) the application but I can either "view in browser" that gives me the option to view the application and write code on same time.

In MVC I can't find this option.

  1. What is the basic DATA MODEL for working with SQL DB.

I mean with no any framework (entity framework, Sub sonic, Link to SQL etc')

I look for basic application that works with data but with no fw.

  1. What is the popular DB framework that working with data.
  2.  I am looking on  mvc series


C# Soup To Nuts  (the best series ever)


I watch some of the MVC video, seems that the music store is good but I have to wait to the other part.

Does any one know and recommend   a Microsoft MVC tutorial?





Web Site to Web Application Question


I've been building web sites and for my next project I will be creating a web application.  I use a 'BasePage' class in all my projects but, since web applications do not contain the 'App_Code' folder, what is the best place/practice for the 'BasePage' class?



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