.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

Writeback expression question - Weighted Allocation (SSAS 2008 SP2)

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :



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

View Complete Post

More Related Resource Links

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  

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]

DR Setup for SSAS 2008

Hello Guru's, I'm new to SSAS env. and want to set up its DR env. Can anybody guide me with any good strategis and implementation practices. We are using SSAS 2008 on 2 node Cluster Server. Thank you in Advance.- Nilesh

OLE DB Error: Network-Related or Instance-Specific while Deploying SSAS 2008 Cube

Hi, I encountered the below listed error while attempting to deploy SSAS 2008 DB: Error 1 OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [5]. ; 08001.  0 0  I am running a standalone MSSQL 2008 named instance on Windows Server 2008 R2. Here are the steps I have taken but yet the issue is not resolve: 1) Enable Remote connections on the server 2) Enable TCP\IP 3) SQL Browser is Running 4) Set Firewall Inbound Exceptions for SQL Browser, Default Port 1433, UDP Port 1434 5) Using Management Studio I can successfully connect to the MSSQL named instance from the app server 6) I can ping I MSSQL box from the app server 7) Read and implemented all steps in the MSDN post below: http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx Thanks for your help.

SSAS newbie question

Hi Guys, I come from a SAP BW background, and i was wondering if i need to learn SSIS and SSRS together with SSAS in order to get a job? A insight into the current job market would also be helpfful. Thanks!

How to read string end with question mark in regular expression?

Hello, I'm facing problem when I read string which is ended by question mark. I use in my code as the following. \"?(?<myfield>[^\"]?.*[^\"?]?)\"? If string is ended by question mark, it's not working. Please help me to solve my problem. Thanks. Co Coe

Shared dataset in SSRS 2008 R2 running on SSAS 2005

I am trying to create a report in SSRS 2008 R2 using SSAS 2005 as a datasource. My dataset has a date parameter. When I create an embedded dataset in my report everything works fine, but when I create a shared dataset with the exact same mdx query, I get the following error: An error occurred during local report processing. The definition of the report 'xxxx' is invalid. The Value expression for the report parameter 'DateCalendarHierarchy' contains an error: [BC30034] Bracketed identifier is missing closing ']'. Does anybody have any ideas? Is this a bug? Thanks, Louis

AdomdDataAdapter.Fill returns memory error when querying an SSAS Cube(2008)

For certain SQL's I will get the following error when calling the Fill method on an AdomdDataAdapter object. "Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed."   Snippet of Code: DataSet ds1 = new DataSet(); AdomdCommand acmd1 = cn1.CreateCommand(); acmd1.CommandText = "SELECT ..."; AdomdDataAdapter ad1 = new AdomdDataAdapter(acmd1); ad1.Fill(ds1);   Now when we run this query in Ms Sql Studio we don't get any errors but the query returns 1 row with over 40,000 columns. I'm guessing the # of columns might be an issue but not sure.   When doing a Google search it appears that this problem was found in the 2005 and fixed in that version. Have any of you come across this problem?  

SSAS 2008 - Memory Error

During our unit and load testing of the SSAS database using VSTST 2010 Ultimate; we are getting a memory error with some of our MDX queries (below).  Futher analysis showed: The adomdDataAdapter "Fill" command is our error point The error is occuring when the result data set has getter than 16,384 Columns; actual returned column count is 41,339 columns - business requirement does not let us change this query at this time. The MDX query works fine in MS Excel 2007 The MDX Query works fine in MS SQL Studio 2008 How can we resolve the unit test data set with such a large column count? Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed. In reference to thread: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/4220adbb-438c-42e7-9612-56aebf8df435/ Thank you, Craig  

Excel 2007 Pivot table OLAP- SSAS 2008 Error

Hi, I do have a strange issue with Excel 2007 pivot table report; the data source is SSAS 2008 cube. When refresh the excel 2007 report data using the 'Refresh All' or 'Refresh' button available under the Data menu, the below error is thrown. "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." After some analysis, the report works well on the below workaround, 1. Remove all the calculated measures from report (3 measures in this scenario) 2. Remove one row label (dimension attribute) from report But the measures, attributes are valid one from cube and it is required fields in the report. So, understand that removing few items is not at all a solution. The below workaround also works. 1. Check the 'Defer Layout Update' option from the Pivot Table Field List window. 2. Click on the Update button After that Unchecked the 'Defer Layout Update' option and apply the filter, etc in the report. The report works very well. But every time, we need to use the above work around after open the excel report. I have NO clue on this issue. Please help to resolve this issue. Thanks, Jey

SQL server 2008 r2 Data sync question???

Sql 2008 r2 data sync, does it involve a db hosted by microsoft or does that involve us hosting the sync functionality locally?  I know we have db's local that need to be sync'd across a wan but does that happen in a db hosted in the ms cloud or does that have to do with a sync functionality that runs locally to keep my db's sync'd?   Thanks, SL

2008 R2 GroupsBeforeRowHeaders as an expression

Hi there Is it possible to set this value dynamically as an expression? I have a table that needs to appear differenty depending on what 'side' of a sheet of a document it is on, and so toggling the row headers from 'left' to 'right' (by manipulating this integer value) would be of great benefit. Or indeed any workarounds that would save me making two versions of this tablix Many thanks  

SSAS 2008 Dimension root values changes after incrementally loading the dimension with 'process_up

The Dimension root  members (below 'ALL') changes after incrementally loading the dimension with 'process_update'.  How do I prevent that from happening ? There was no changes to data of the underlying table of the dimension.   

Can SSAS 2005 run with Sql Server 2008 database engine?

Are there any known issues of running SSAS 2005 with SQL Server 2008 database engine?

SSAS 2008 attritube hierarchy doesn't group records and repeat rows

We are having problems with the dimension attributes in lower level hierarchies not grouping under 1 single level. Here is the hierarchy: VP    College      Department         Departmental Course            Course Level               Course Number The top 3 levels are grouping correctly without duplicate rows and they don't have compoud keys. The lowest 3 levels are not grouped correctly and they have compounds keys because the attributes are not unique by themself. The result of the hierarchy looks like this: VP Academic Affairs    Business School of Management        International Business School            Mgm101                 Lower                       Course Number 123            Mgm101                Upper                

SSAS 2008 Metadata Repository data - User Knowledge base

Hello, We have the SQL Server 2008 Analysis services instance with some databases, cubes, Dimensions, Measure groups, partitions, etc. The business users are find difficulty to search for a particular measure. I mean, it is difficult for them locate a measure from the available cubes; again finding the respective Measure Group/ Folder is not an easy task for them. Also, they need to know the source system of a particular attribute in a dimension, formula for the calculated measure and etc. So, we have planned to develop a knowledge repository, which needs to store the business metadata for the SSAS databases. The user needs to be access this information using the SharePoint site and should help them locate and understand the data. The User Interface needs to be user friendly too. I need your valuable suggestions for the design of this system. Note: I have referenced the below articles http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/ http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/c610850f-16b4-4eaf-9b91-a2d32816f40e

Unable to set defaultmember via MDX in SSAS 2008 R2

Hi,   I've migrated my SSAS cube from 2005 to 2008 R2. in 2005 I could use the mdx (alter cube currentcube ...) to set the default member based on attribute property of another dimension. With 2208 R2 I'm unable to set the value. The mdx that I'm using in 2005 is as follows: alter cube currentcube update dimension [Time].[Time Hierarchy], default_member = tail(filter([Time].[Time Hierarchy].[Day of Month].members, [Time].[Day of Month].currentmember.membervalue  = CDbl([Cube Defaults].[Default Values].Properties( "Default Time Key" ))), 1)(0); This used to set the default member to whatever the Default Time Key attribute is set to. For ex. 01-Jan-2010 or 01-Jun-2010 etc.  This doesn't work in 2008 R2 anymore and the default member for the Time dimension is set to "All". Any ideas? Do I need to change something in the above script to get it working? All help would be very useful! - Sourabh  
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