.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

SSAS2005 Writeback Woes

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
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.

View Complete Post

More Related Resource Links

CTYPE woes



I'm getting the old "Object reference not set to an instance of an object" error on this code:

nonqueryCommand.Parameters.Add("@comment", SqlDbType.VarChar, 500)

nonqueryCommand.Parameters("@comment").Value = CType(FormView1.Row.FindControl("CommentsLabel"), TextBox).Text

"CommentsLabel" is a textbox, despite it's name (and I've tried the CTYPE ot Label too).


Any ideas?



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

web.config woes (membership)

Hi,I'm really struggling to set up membership for a new site and was hoping you could help me out. I've set up the database using aspnet_regsql.exe and I can connect to the db with the connectionstring I've set up. The WSAT, however, says "there's some kind of error" without specifying anything. The weird thing is that I've even tried copying the contents of an "old" site and just changing the parameters of the connection string, but still no luck.Thankful for any help!<?xml version="1.0"?><configuration> <connectionStrings> <add name="EcotechConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=Ecotech;Persist Security Info=True;User ID=EcotechUser;Password=prince44" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.0" /> <authentication mode="Forms"> <forms loginUrl="~/Account/Login.aspx" timeout="2880"/> </authentication> <membership> <providers> <clear/> <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName=&quo

adding mor tables to datasource view causes writeback performance problems


I've got a very strange behavior in one of our SSAS databases. We had performance issues so i've tried to put only the essential parts into one OLAP Database

the result is:

Just by adding more tables to the datasource view the writeback performance drops from 0,2sec to 3 sec. No changes to the cube, no changes to the dimensions.
The t-sql statement fired for the writeback remains the same.
Any idea?



Solution for Writeback Limitations


Writeback is an excellent feature in Excel 2010 but it lacks the ability to get back to the original value once its been changed.  Writeback is ideal for forecasting but if someone overwrites/writebacks the forecast on an "actual" cell, the values for the actual number is lost and cannot be retieved short of deleting the writeback data.  As recommend to me by an Microsoft SSAS member at TechEd, I split the data up into a dimension called Scenario containing the "Actual" and "Forecast" memebers.  With this, one can see the actual number and forecasted number side by side and can forecast using the forecast member.  The problem now is that one cannot aggregate on these 2 cells.  The thought would be to create a calcuated member that check does essentially, ISNULL("Actual", "Forecast").  This new "Verified" member works, but now the issue becomes rolling up the data.  Since this member's condition is not scoped, the "Verified" value rolls from the lowest level to the top.  Is it possible create the member to be conditionalized so that it only uses the ISNULL condition at the lowest level, but then rolls up properly?

Let me try to illustrate the issue...

Scenario = "Actual"

Date     Store

SSAS2005 Cube - Watch Customer on two points in Time



I need some help on the following problem:

I have got a customer status, let's say N for New, A for Active, I for Inaktive.

When a customer buys the first time his status will be N.
When he buys a second time his status changes to A.
After some time without an order his status is set to I.

This status changes are recorderd in a history table.

The question:

How many customers with status "N" at date(t1) and how many of those customers have status "N", "A" and "I" at date(t2).

e.g. on 01. Jan. 2010 i have 500 customers with status "N".
On 01. Jul. 2010 i want to know how many of those 500 customers from the 01. Jan. have status "A".
Let' say 200 have status A.

Output should be t1_U = 500 and t2_A = 200.

Would it be posible to do answer this question in a cube?
I have tried out some things but didn't get an plausible result.
With 2 messures (one for point1 in time, a second for point2 in time) it will not give me 200 in the second messure, but 220. I think a there is missing a relationship between the two messures on customer base.
So at point 2 there are 20 more customers with status "A" on point2 in time thats right, but they didn't exist on poi

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 ?




update panel woes... question for superstar AJAX gurus



This has got me completely confused... not sure if I've been at it too long and am missing something, or if this is a bug?

I have a webform with an AJAX update panel. In the panel there are 2 controls. One is a textbox, the other a listview.

I'm using the ClientScriptManager to take advantage of the "OnKeyUp" event to cause an autopostback.

When text is entered into the textbox, it runs the textchanged event, which puts data in the listview.

So far so good. All works fine with one exception.

Problem is that with IE8 the textbox loses focus on the SECOND text entry, even though it's specified in my code to focus on textbox after the textchanged event fires. And with Firefox, it doesn't cause the textchanged event to fire at all.

I don't get it??

Here's my code...

    Sub Page_load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load


        TextBox1.Attributes.Add("onKeyUp", Page.ClientScript.GetPostBackEventReference(TextBox1, ""))

    End Sub

    Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As EventArgs) Handles TextBox1.TextChanged

        Listbox1.Items.Add(New ListItem("ListboxItem", "ListboxValue"))

        TextBox1.Text = ""


many-to-many mssql schema, linq2sql, objectdatasource with SelectedIndex and delete woes


Hi All,

Here's the setup (excuse no code the setup doesn't post well):


table > link table > table (M:M)

link table is composite key with constraints


linq2sql DataContext of above schema


object datasource bound to Business Logic class (exposing the LINQ2SQL class methods) (ObjDs1)

ListView with DataSourceId to above objds (Lv1)

object datasource with control parameter aimed at above ListView SelectIndex (ObjDs2)

ListView aimed at above objds (Lv2)


select item in Lv1 > postback > Lv2 displays correct records for the M:M

On Delete (heres the fun) the delete fires, the data is deleted and constraints work (no error) BUT Lv2 still displays the deleted data...

I have called Page_PreRender Page.DataBind() just to make sure every control in the heirachy is rebound but to no avail...

I also noted that the OnDeleted event in the ObjDs2 has an AffectedRows of -1.... but the data is definitely deleted.


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.


Correlation Token & Create Task Woes


I've created a State Machine work flow.
In State1 I create a Task (Task1). 
The workflow then goes to state State2 and State2  goes  back to State1.
Now when State1 runs through creating Task1 again I get a correlation token error.
I would like it to create a new Task1.

I'm not sure how I should be going about this.
The Correlation Token for Task1 is set to 'workflowToken'.  Inside the MethodInvoking code for Task1 I set
Task1_TaskID = Guid.NewGuid();
It creates a task the first time the flow passes through State1. It's on the second time that it passes through State1 that it crashes.

Am I doing anything wrong?

COM Event woes

After straightening out problems with my MSDN subscription (once again
:(,) hopefully I can now get some help with this issue.
I have made an in-process COM server that will have a number of clients
connecting to it. In one case, a client (written w/ unmanaged C++) will
always be the first to connect, thus creating the COM object, and call
an exposed method with a string parameter. I have this part working, so
my COM interface would seem to be correct.
In another case, some number of clients (written w/ managed .NET) will
subscribe to events that will be triggered by the value of the parameter
in the first case above (actually the result of an algorithm with said
parameter as input.) I'm having problems here.
I have the following questions:
1. Since the clients in case 2 will be .NET clients, is it necessary
that the COM server register itself with the ROT? That is, must
subsequent client connections (beyond the initial) make a call to
Marshal.GetActiveObject? I would think so, but experience in this area
is not deep.
2. The pattern that I'm trying to follow looks like this:

Is Writeback available in SQL Standard



The feature comparison for SQL Server states that dimension writeback is only available with enterprise edition. Can you add a Writeback partition for fact data with standard edition or do you need enterprise edition for fact writeback as well.


Richard Mintz

Cube Writeback with weighted allocation working strangely - creating large negative values against f




I've got a cube (2008) with 2 MGs: Actuals and Budgets.

The Budgets partition is Write-Enabled.


On Excel 2010 the user will select the Actual Date for 2010-07, and will set the Budget date to 2011.

After seeing what the YTD actual figure looks like he enters the budget for 2011.

It is supposed to take that budget figure and split it between the funds that have actuals against them (using weighting).


Here's the problem:


SSAS allocates inflated figures against those funds, and then, so that the sum of those values will equal the originally entered amount, it puts a large negative value against the first Member in the fund dimension (I experimented with this by deleting the first member in that dimension. It just allocates the negative value against the new member!).

Why isn't it just allocating the values based on the ratio that it gets from that Actuals calc?



The UPDATE statement on the trace looked like this:


UPDATE CUBE [Forecasting]
SET  (
        [Actuals Date].[Year-Quarter-Month-Day Hierarchy].[Calendar Year Month].&[2010-07]
        , [Budget Date].[Year-Quarter-Month-Day Hierarchy].[Calendar Year].&[2011]

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

Writeback: showing differences between old and new values


I'm looking use writeback to display what if information on a cube. Putting the new values into the cube isn't a problem but having done so I would like to be able to see the difference between the old and new values.

As far as I can see from the documentation this isn't possible from a naive query, once the new value is there that's what you see. And searching here and elsewhere turns up nothing useful. Have I missed something?

Is there a recommended approach to achieve this? The sort of thing I'm envisaging is being able to an axis in pivot table view that shows : before, after, change.



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