.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

Write-Back table generation and Measure Expressions

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

Hi Everyone,

It would be interesting to see if anyone can tell me a couple of things about how SSAS works. If not, at least it would be very nice if anyone could hook me up with a SSAS product team member, who can shed some light…

1. Write-Back Tables

How does SSAS create a write-back table for a partition? More specifically, if you have a role-playing dimension (I’ll use an example with a Date), you will get two Date keys with _ suffixes. How do these suffixes get generated? Is there any logic which SSAS applies consistently? This would be very helpful when moving data from the write-table to the fact table.

2. Measure Expressions

There are two conflicting blog posts by Christian Wade and Teo Lachev about how Measure Expressions work. According to Teo Lachev, Measure Expressions get calculated at runtime, while Christian Wade writes that the calculation results are stored on disk (assuming during processing). It would be good to confirm this as it may matter in some specific cases (Currency Conversions with heaps of data).


Boyan Penev --- http://www.bp-msbi.com

View Complete Post

More Related Resource Links

Data Points: Common Table Expressions


In T-SQL, views and derived tables serve similar purposes and have their advantages. But when working with SQL Server 2005,there's a third option--using Common Table Expressions (CTEs). John Papa explains.

John Papa

MSDN Magazine October 2007

Model Binding the values in an HTML Table back to Controller / Model


Currently, I am working with ASP.NET MVC1 and am still learning about Model Binding and how values from a View are passed back to the Controller / Model. Specifically, I want take an existing Model, create a Table and populate the Rows of the Table, allow the user to edit some fields and pass it back.

In my example, I have a Class called "Ingredient" which has 4 public accessors:  Name, Barcode, Amount, and Unit

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<IEnumerable<MvcACE.DomainModel.Entities.Ingredient>>" %>

<% using (Html.BeginForm("SubmitOrder","MyController"))
{ %>

<% if (Model != null)
foreach (var item in Model)
{ %>
<td><%= Html.Encode(item.Name)%></td>
<td><%= Html.Encode(item.Barcode)%></

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  

How to create back-up table

how can i create a backup table for my base table.my base table name is FAB when ever user store the data into FAB table same records should be stored into back-up table automatically.how can i acheive this.i am really thank full to you if any one provide me solution.  

Need help with dynamic table generation

I am building a test script to help myself figure out dynamic SQL.  My goal is to generate column names from an existing table query.  To work in this direction I made the following test: declare @sql varchar(1000) declare @int int set @int = 1 set @sql = 'declare @test table (' set @sql = @sql + 'testfield varchar(10),' while @int < 5 begin  if @int < 4   Begin    set @sql = @sql + ' field' + CAST(@int as varchar(10)) + ' varchar(10),'   end  else if @int = 4   begin    set @sql = @sql + ' field' + CAST(@int as varchar(10)) + ' varchar(10))'   end  set @int = @int + 1  end select (@sql) exec (@sql) insert into @test (testfield, field1, field2, field3, field4) values (1,2,3,4,5) When I run this query I get the following from the select (@sql) query: declare @test table (testfield varchar(10), field1 varchar(10), field2 varchar(10), field3 varchar(10), field4 varchar(10)) This looks good.  When I run the exec statement, this returns 1 Row affected, so, that seems to work.  However, when I run the insert I get this error: Server: Msg 137, Level 15, State 2, Line 23 Must declare the variable '@test'. I know that GO statements will effectively create batch dividers between statement groups, but, I haven't yet learned

How to extend this query to write the results back to a database?

FOr this query I have four tables I would like to access. SELECT StockA, SUM(Score) AS Total FROM dbo.ScoresNew_LongPrice WHERE [When] >= @StartTime AND [When] < @EndTime GROUP BY StockA ORDER BY SUM(Score) DESC _LongPrice, _ShortPrice, _LongVolume, _ShortVolume I would like to improve the above query to return not just the results from LongPrice, but four columns, of sum(score), from each table, if possible. And, if that is possible, I would also like to have the query then update another table with its results.  Not a new table, but just updated to an existing table. Anyway, I can do some stuff in T-SQL but this seems over my head.  Any help would be appreciated. (The four tables all have identical structures)

Need 2 Measure Groups for One Fact table



I have one fact table that contains all the measures.  The problem is that I want to have two measure groups that point to this one fact table.  Some measures would be in measure group A and some in measure group B but the underlying source still comes from the one fact table.  I haven't found a way to do this.

My solution currently is I have created another fact table that is an exact copy of the main one, that way I can create two measure groups.  The issue is performance, it takes 4 minutes to build and if I took one of those fact tables out it would be cut in half.

I would have thought there would be a way to create a measure group and drag what you want in there..but it seems that this isn't the case that you can only create measure groups based on how many fact tables you have.

Any help would be appreciated.


how to perform write back in cube?



I want to perform write back operation in my sales cube.


The 'Measure expressions' feature is not included in the 'Standard Edition' SKU


I'm following the project in Analysis Services Step by Step and have this trouble when I'm trying to deploy My First Cube in Chap 03. I'm not using Standard Edition. I'm using Developer Edition. What do I have to do to be able to continue?


How do I write System::StartTime to a table?



I am trying to very simply insert the System::StartTime into a file.

I have placed a watch on this system variable the contents of this system variable within a watch appear valid, e.g. ; -

SYSTEM::STARTTIME {18/10/2010 14:30:27} DATETIME

I created an Execute SQL Task.

I mapped the variable name SYSTEM::STARTTIME  as a parameter within a data type of DBDATE.

Entered the SQL Statement; -


INTO dbo.CustomSSISLog (StartTime)

HOW to write the one insertion Procure for multiple table Records in sql server 2008


i m facing the Proble

i want to insert values into multiple table in one insertion Procedure is it Possible?

see my info

Table name UserPersonal columns

            ([UserRegform_pkID] PK


second table userRegForm columns

            ([UserRegform_pkID] PK

SSRS 2005 Dynamic Table Generation



I am attempting to build a report that allows the user to select the value of 4 different parameters. The final parameter allows the user to select multiple values. My question is as follows. Based on the users selections, is there a way to generate the required number of tables dynamically?

For instance, if the input for the final parameter is A, B, & C can the appriopriate number of tables be generated for the same dataset (Sales) without having to build a table for SalesA, SalesB, and SalesC? Or do I have to build the maximum number of tables and use an expression to determine visibility based on the parameters. Note: max number of possible tables is more than 100.





Combobox write back


Hi All. I have the following code in XAML

<DataGridTemplateColumn Header="Type" Width="100">
   <TextBlock Text="{Binding RC_Type}"/>

Aggregations based on Dimensions dragged for Calculated measure from different fact table Using MDX



 I am a newbie to Mdx, i am working on education domain. I have a requirement to calculate student counts based on different fact views.

Especially for state,district and school levels we created three different fact views based on applying some conditions on original fact table.

Now i am using only one calculated measure named "Student Count" in my cube. My requirement is, when i drag my district dimension student count count based on district dimension should appear in my measure, if drag both districts and schools together then school facts student count should appear in my count, if i didn't drag any of the two dimensions then state level count defaultly aggregated.

For this, i thought write an mdx basing on dimension i am dragging, so i started with scope function for getting a different fact view based on different fact view,

Scope([Districts].[Districts],[Mesures].[Student Count])

this= ([Districts].[Districts],[Mesures].[District Student Count])

end scope;

like wise for school dimension and so on, but i am getting same value for both of the cases.

could any one help me out in getting solution for this. Is there anyother way other than creating new cubes for each level.

Thanks in advance. Any suggestions are appreciated.


Lakshman A N, S

SerialPort does nothing more than echos back what I write.


I have a laptop with a modem.  The OS is Windows 7.

When I use a hyperterminal program, everything works fine.  For example, when I type "atz", it replies "OK."  When I type "atdt 9,15555555" (not a real phone number), it dials into the modem.  So we know that the modem and the hyperterminal work.

Here is my code for SerialPort


SerialPort port = new

port.PortName = "COM3"
port.BaudRate = 1200;
port.Parity = Parity.Odd;
port.DataBits = 7;
port.StopBits = StopBits.One;



port simply echos back what I write.  Does not throw any kind of Exception, but just echos ("atz" returns "atz", "atdt 9,15555555" returns " "atdt 9,15555555" without dialing the number) .  In fact, even non sense lines such as "merry Christmas" simply echos back the same string.

I've downloaded source code from tutorial sites, and it does the same.  I've also tried various things besides just using WriteLine.  I tried using the BaseStream object of Ser

In SSIS, can I write the value of a variable to a SQL table?


This seems like it should be easy to do, but I can't figure it out.

In my Control Flow I have an Execute SQL task that populates an ADO Recordset variable. That works fine, the variable holds a list of server names.

Then I have that connected to a Foreach Loop Container, which loops through the server names, connects to each server, reads some info, and writes that to a table. That also works, except for those servers that it can't connect to.

So here's what I'd like to do: before I connect to the server, write the value of the variable (which contains the server name) to a table in SQL, along with getdate() so I know when it started trying to connect.

How do I just write the value of a variable to a table? If I just use an OLE DB Destination, there are no input columns.

My best thought was to use a Script Component and somehow get that value, and then somehow make it an output column, which I then connect to the OLE DB Destination as an input column??

If I do this in a C# Script Component:



it works just fine, so I know my variable is there an available.

Any suggestio

getting at non-measure data in fact table


I am creating a cube with a single fact based on order payments.  THe fields order# and seq# in the fact table have no related dimension.  The payment amount in that fact table is the measure I'm using.  So I have several dimensions - date, location, customer, etc....not a problem but for some reports I would like to pick up the order number.  So my question is given two non-measure fields in a fact table, how do I make them accessible in my cube?  I am thinking that I need to create a dimension based on my fact table but not sure so I'd rather someone set me straight first.

Thanks for your response. 

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