.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

create calculation sas cube

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

how do write calcuation in ssas for caculated columns, here's syntax i have sql..

select (BIRTH_DT from dbo.HM_MEMBERSHIP

where (datediff(year,BIRTH_DT,GETDATE())) between  30  and 39

View Complete Post

More Related Resource Links

Errors attempting to process SSAS Cube. I get Either user does not have permission to create a new o

Hi All, One of my production server deployed cubes i am not able to process the cube.When i tried to process the cubes i will get an Error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Either the 'NA\gsad478' user does not have permission to create a new object in 'JNJAUSYSVR01', or the object does not exist.  (Microsoft.AnalysisServices)   Over the source server i do have sysadmin access and also  i have added read only role of particular cube. I right click on the database and tried to create new database of SSAS that also thrown with same error. Please help me in this regard.   Thanks, Gangadhar

create a calculated member in a cube in SSAS

Hello   I’m trying to create a calculated member in a cube in SSAS and was hoping someone could help.   What I’m looking to do is count the number of occurrences of a value in a field.  I have Status field that contains New, Renewals and Enquiries and another field that contains a count, the problem I have and the reason for this query is the count is set to 0 for Enquiries.   The tSQL equivalent would be something like...   Select Date, Status , count(Status) From aTable Where Status = ‘ENQ’   And to summarise, what I’m looking to do would look something like the below   Date Status Count Commission Apr-10 NEW 1 100 May-10 NEW 1 100 May-10 REN 1 50 Apr-10 ENQ 0 0 May-10 ENQ 0 0 Jun-10 REN 1 50 May-10 NEW 1 100 Just looking at the ENQ by month. Date Count of ENQ     Apr-10 1     May-10 1     Jun-10 0       Any help gratefully received.

CREATE GLOBAL CUBE statement - losing hierarchies due to slicing

Hi,   I have a SSAS cube from which I generate local cubes with the MDX statement below:   CREATE GLOBAL CUBE MyLocalCube   storage 'MyLocalCube.cub'   FROM [MyCube]  (          MEASURE [MyCube].[Measure1],          MEASURE [MyCube].[Measure2],           DIMENSION [MyCube].[Dimension1],          DIMENSION [MyCube].[Dimension2]          (             LEVEL [HLevel1],                     LEVEL [HLevel2],                     LEVEL [HLevel3],             MEMBER [Dimension2].[Hierarchy1].[HLevel2].&[MemberX]       ),         DIMENSION [MyCube].[Dimension3]  )   The problem is that this statement generates a local cube file which works fine but all other hierarchies defined in Dimension2 are not included. I cannot find the syntax for adding them manually. So, in other w

Improving performance of UPDATE CUBE by executing preceding CREATE CACHE for the same cell / subcube

Hi All, Recently I came across one of suggestions by Microsoft Advisory consultant regarding optimization data write-back pattern for the SSAS cubes. It was recommended issuing CREATE CACHE command following by UPDATE CUBE for the same cell/subcube definition: CREATE CACHE FOR [MYCUBE] AS ("Cross join of all the dimension members defining subcube to be updated") UPDATE CUBE [MYCUBE] SET ("Cell") = 100, ("Another Cell") = 200,... etc. for every cell. This seems quite weird, but based on our benchmark the entire process (CREATE CACHE and UPDATE CUBE) takes less time than just UPDATE CACHE. Folks, can someone advice on the insights of the process? Shell this approach to be suggested for an enterprise-level planning application? Regards, -h

how to perform calculation and KPI in cube?


Hi ,

I have a sales cube in that i want to perform some calculation on sales column. 

How to do that?

how  to perform calculation and KPI in cube?



Cannot create offline cube in Excel via http



we have an Analysis Server and it is accessible via http and that is all working fine, but if you try to create an offline cube on a PC which is not located in our company network it ends up every time in a failure like "OLE DB Error:...The specified host is unknown. .; either can not connect to the server..."

Any ideas? It seems so to me that for creating the offline cube, the connection string is broken or something like that...



Cannot create MDX calculation with one variable in the calculation from a leaf level


I am new to MDX/SSAS and have a problem with setting up two calculations. I have spent several days researching, endless rewriting of MDX queries, and did not find an answer (or didn’t recognize the post as an answer to my problem :)


I need help with two calculations.

One, a calculation, let’s call it  [Dollar Variance], that computes variance at all levels by this formula:

([Actual Work Billings] - [Budgeted Work Billings]) / [Work Days in Month] = [Dollar Variance]


The problem is that while the [Actual Work Billings] and the [Budgeted Work Billings] are summed at the particular dimension intersects chosen, for the variance to be calculated properly, the

"WHERE" in Cube calculation


Hi Everyone, I am trying to put a where clause in the Cube calculation but not getting it to work.

Here is the actual calculation which works:


([DimTime].[TimeHierarchy].CurrentMember .Lag (1),[Measures].[Actual Hour])* 1.25

Now, I want to add a where clause to this calculation which looks something like this:

WHERE ([Company].[Department].CHILDREN - [Company].[Department] .&[Research)


Can anybody please advise? Thanks a lot in advance for your help guys.

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

What is best way to create a measure that applies to 2 or more measure groups in a cube?


I'm designing an SSAS cube with 2 measure groups, each representing a business process related to Orders.  The fact table for the first measure group (Order Fact) is loaded when an Order is placed, and the fact table for the 2nd measure group (Order Check Fact) is loaded when a Check is cut to pay for the Order.  In addition, a third measure group (using Order Fact) has been created to calculate a Distinct Count of Orders (the measure name is [Number of Orders]).

Here is what the Dimension Usage tab looks like (all Regular relationships):




Distinct Count Orders

How to create a calculation in a list - Calculate the number of days between two dates


I have a calculated column in a list called Days Open. This column contains the number of days between today (Sysdate) and another column called the Effective Date. Therefore, if Sysdate is 11/16/2010 and the Effective Date is 10/14/2010, how do I calculate the number of days between the two dates?

I tried using this formula: =TEXT(Sysdate-[Effective Date],"h") and then I tried =INT(Sysdate-Effective Date) & TEXT(Sysdate-Effective Date, ":hh:mm")

Neither formula worked. I hope it is something real simple that I am missing here. Any help you can provide will be highly appreciated!

SSAS 2008 - Occupancy Calculation Cube?


Hi there,

Previously I posted my probelm into T-SQL forum section. Here is link http://social.msdn.microsoft.com/Forums/en/transactsql/thread/b02d37b4-d36e-42cc-b1f4-3c00abadfdd4

I have SQL 2005 table like

Room      AdmissionDate    DischargeDate
R1             2010-01-25           2010-01-28
R1             2010-01-28           2010-01-30
R1             2010-02-15           2010-02-17
R1             2001-02-20           NULL         
R2             2010-01-31           2010-01-31
R2             2010-02-10 

Create a Cube dimension - Reusability of code


Hi, Hope all are had a good week end.

I do have a question in mind. In this scenario, the aim is to create a new dimension in a cube with some hierarchies to be defined in it (includes update DSV too). Let us consider this cube as Cube A. We have another cube 'Cube B' , which has the similar dimension in it, the important thing is that they both are in different database.

During the cube development in BIDS 2008, is there any way to reuse the code of already available cube (Cube B) dimesion (XML code)? If yes, please share the best practices to do the same.

Thanks, Jey



Hi experts,


I was writing the below MDX query


WITH CELL Calculation [Final] FOR '([Measures].[Status Sum],[date].[Fiscal Year].allmembers,leaves([Customer]))'
AS Aggregate({NULL:[date].[Fiscal Year].currentmember},[Measures].[Status])
SELECT [Measures].[Status Sum] ON 0,
{[Date].[Fiscal Year].allmembers} ON 1
from [Adventure Works]


and it gives me the following result 


	Status Sum
All Periods   	89898
FY 2002	    4260
FY 2003	    10799
FY 2004	    87377
FY 2005	    89898


Now I want to recreate the same calculation in the cube using the CREATE CELL statement and this is what I came up with


CREATE CELL Calculation CURRENTCUBE.[Status Sum] FOR '([Measures].[Status Sum],[date].[Fiscal Year].children,leaves([Customer]))'
AS Aggregate({NULL:[date].[Fiscal Year].currentmember},[Measures].[Status])


This gives me the correct result when I view the [Status Sum] measure against all the years. But when I look for a particular year like FY2005, the query scoped cell calculation (WITH cell calculation) gives me 89898,

Cube Calculation get Members of second last Level of a parent-child hierarchie


Hi everyone,

I have a parent-child-hierarchie, where the different branches has different lengths; for example the fist one is:

Level1 - aLevel2 -aLevel3 -atotal

and the second:

Level1 - bLevel2-bLevel3 - bLevel4 - bLevel5 - btotal

Now I need the memebers of the second last Level, so in the examle: aLevel3 and bLevel5

How can I get them?

Thanks in advance for your help!


Is cube limit the number of calculation member?



I develop new application and user can create new calculation member. So some one can tell me "how many Calculation member a cube can contain?"


Much thanks.

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