.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

YTD values with two time hierarchies in SSAS

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

Hi All,

We have YTD business intellegence time calculations in our cube. Here we have set YTD calculations for two time hierarchies TimeMonthHierarchy - Year-Qtr-Month-Date and TimeWeekHierarchy - Year-Week.

When I use two hierarchies seperately I get accurate figures for YTD but when I use both these hierarchies together it do not work as expected. Also at every month start - YTD restarts.

Please look at the discrepancy below:

Sales (Data Types)







Current Year


View Complete Post

More Related Resource Links

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.   

SSRS Report based on SSAS Cubes doesn't show NULL values correctly

I have a SSRS report which is based on an SSAS Cube. In the Cube the formating of numeric fields that NULL values are properly shown as NULL values. In the Cube Browser and Excel this is shown correctly. However when I design the report already the query against SSAS shows these fields as "0" instead of NULL. Is there a way to fix this or is this a bug ?  

SSAS 2K5 - Server time dimension

Hi, I'm building a cube and I want to add a time dimension. I don't have any time table in my OLTP (Source) database. So I tried to use the wizards from SSAS to create a time (date) dimension. I have choosed the "Server time dimension" one. It did create all what I need with all the hierarchies. But when I tried to add time business intelligence with the wizard, it doesnt see my new time dimension. I have checked if the type was "time" for the dimension and It was ok. So I don't know why the Time intelligence can't see my time table .... btw, I start the Business Intelligence wizard from the cube.   Thanks

SSAS - Besides 2 big user defined hierarchies, plently of small hierarchies


Hi All

I am working on a project where we have to define an Organization Dimension (we defined a standard dimension not parent-child for performance reasons).

The company I am working for is using codeblocks at the lowest level of the hierarchy (combination of business unit, operating unit, etc...).

We created two user-defined hierarchies on that dimension

- One organizing the codeblocks Geographycally (we have attributes like Area, Subarea, CountryGroup, Country)

- One organizing the codeblocks from a Service perspective (ServiceLine, Subserviceline)

The table looks like:

Codeblock, Area, SubArea, CountryGroup, Country, ServiceLine, SubserviceLine, ...

An additional business requirement we have is to define plenty of very small hierarchies besides those two main hierarchies.

One example would be to have a hierarchy that combines values for Country(Switzerland) and Country(Germany). Of course it would be possible to create calculated members in the MDX Script but we have the requirement to be able to drill down from the sum to the individual countries. Since calculated members are not allowed to have children, they are not an option.

We would like to avoid using a parent-child dimension (I know the possibility of using a parent-child with multiple hierarchies).

One ugly option I found is to add dum

Problem with a calculated measure that works across different time hierarchies.


I was inspired to create a calculated measure which works with all available time hierarchies after reading this original post.


I created   a calculated measure called ‘Selected Hierarchy’ as follow which returns either ‘Admit’, ‘Received’ or ‘Discharge’ as the hierarchy is selected.


when NOT IsError(Extract ( Axis (0), [Admit Date].[Date Hierarchy]).Count ) then 'Admit'

SSAS adding a time dimension and using it



(Using SQL Server 2008 R2)

Im rather new to SQL Server and certainly to SSAS. AT the moment I am working on a project in which I need to implement SSAS on a SQL Server. The database originates from UniVerse and it does not have a time table I can use as a dimension.


I have found a script that will be able to set up the time dimension table, but:

-how do I add it to the database


-how do I make it available to be chosen as a dimension?





Multiple Parent Child Hierarchies in SSAS 2008 R2


I'm trying to create a dimension containing financial accounts in SSAS 2008 R2 where individual accounts can belong to one or more hierarchies.  I also need to be able to specify a unary operator since a leaf level account may be subtracted from one parent but additive to another parent.  For example revenues and expenses may be grouped one way for statutory reporting and a completly different way for management reporting and further belong to some arbitraty groupings that users find necessary.

I can't seem to find a way to have a leaf level member in a dimension more than one time in a Parent child hierarchy, and I can't seem to find any way to include the unary operator in a hierarchy where I use attributes to define the hierarchy.

Are there any work arounds that anyone knows about?  What I found from the help file was that multiple parent child hierarchies which supported in SSAS 2000 are not supported in 2008.  However there is no suggestion for what to do.

Any insight would be greatly appreciated.


Bill Webster

SSAS Cube to give an error when cube not available during refresh time



I am currently using the below query to know my last cube refresh time,

My cube refesh time is not fixed , I am trying to create a report which shows a status of cube availability i.e status should show red when cube is not available and when cube returns some value then it's green . How do I do that.

SELECT Cube_Name, Last_Data_Update


Compare the values of a measure with reference to a time dimension


Hi experts, i am a novice on Analysis Services and i have this necessity:

I want to create a calculated member that compare the values of a dimension with reference to a time dimension.

Example :

Sales Year 2009   Sales year 2010   %Var

I have found this model of calc below but i not know well Analysis Services.

How can i implement this function? My time dimension named [Tempo].[Anno].[Anno] and my measure named [Measures].[Importo Neg]


// Test for current coordinate being on (All) member.



[<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.

XML with PrimaryKey values to delete rows in database table at a time



I'm having a gridview in my aspx page with checkbox, User can select one or more than one record(s) at a time and clicks on delete button, Then i'm constructing an object of my DTO and adding all the checked row's Primary Key (DataKeyNames - GUID's in my case) to a serializable class and generating an XML and sending it as parameter to the Stored Procedure.

My Doubt is, can i bulk delete all the rows from database table those are present in my XML document??

If it works, I can do bulk update with XML only, i already did bulk insertion by generating an XML..

My Another Question is, Will it affect the performance? Each time when i construct an DTO object and adding it to the Generic List??

WHich one is better, I mean ObjectDataSource or SQLDataSource or my XML method??

Can anyone please help me out clarifying my doubts and also XML Delete Query..


My XML will be like this











Will bulk delete happen in the above case?



How to query SSAS cube based on a time span?



sorry, I'm new to SSAS. something i couldn't figure out, plase help me.

I have a fact table: Log   and a dimision table: Time

in the Time table, all the record are in date format detailed to hour. such as 2010-06-01 23:00:00

and in the log table, each log record has a time column.

Now I want to query based on two time parameters other program passed me.

e.g. , I want to know how many log records between 2010-01-01 02:00:00 and 2010-10-27 12:00:00.

but when i do the query, the problem is on the time spot '2010-01-01 02:00:00', maybe no log record avaliable.

so the query return nothing! The following is my query.

select NON EMPTY{[Log].[Subject].children} on 1,
NON EMPTY{[Measures].[Quantity]} on 0
 ( SELECT ( STRTOMEMBER('[Time].[Date].&[2010-05-01T23:00:00]') : STRTOMEMBER('[Time].[Date].&[2010-05-24T23:00:00]') ) on 0
   from [Db TMIC])

if Time.Date has no member of 2010-05-05 23:00:00, I got nothing!


I also tried use filter to get the nearest value, but still don't work.

select {[Measures].[Quantity]} on 0,

Filter([Time].[Date], [Time].[Date].CurrentMember < '2010-05-05T23:00:00') on 1
from [Db TMIC]


Can any one help how to solve the problem? To query based on two time values.

Values in repeating rows being replaced after opening the form a second time


I have a couple Infopath forms and recently they started behaving oddly.

In each of them there is a repeating row. In each row you enter a work order as the first value/column, this then filters the second column which is a drop-down with the various items on that work order. The remaining rows are then descriptions of those select items respectively. This information is all retrieved from a single SQL connection.

It all looks great, you click submit (It submits those values to a SharePoint list) and is then saved in the document library. However if you open the form after the values in each of the repeating row has reverted back to the first item for the selected work order.

For example: I enter work order 3344 which consists of items x, y, and z. In the drop down I can select item x, for the second item, I select y, and then z for the third for a total of 3 rows I then click submit.

If I open the form again the correct work order is still displayed but the three rows are all showing details for x, the first item in the work flow.

Does anyone have any idea what could be doing this? Thanks in advance!

How to send filter values to a PivotTable filter when using SSAS cube as data source?



I have created a custom filter web part that implements IWebPartParameters. I'm able to send values from this web part to and Excel Web Access web part and get it to set a pivottable filter based on this value.

Now, I want to do the same for an Excel Pivottable that's connected to an SSAS cube. However, I can't get the parameter value to set the filter. Is there some special format that I need to send the values in to be able to set a pivottable filter for a SSAS cube?


Check the if SSAS Roles property is used in excel connection string before returning the values



We are using Excel 2007 to connect to SSAS cubes. We have many roles for a user and all having different permission including different VISUAL TOTAL setting. We want to restrict the user so that he cannot see anything if he is not using the ROLES property in the excel connection string.

At the run time I want to know if the user who is using excel has used ROLES property or not.

Any help will be appreciated

Mr myName

SSAS Time Dimension (academic years, quarters) change over time


Apologies if I don't state teh question properly or am overlooking obvious answers - just started working with SSAS and barely beginner level.

I'm working on a cube to show student enrollments in different subject areas over time - for this purpose I'm only concerned about academic years and semesters.  In the underlying dimension table I have data like

1 2006          SUMMER  SUMMER 2006
2 2006          FALL  FALL 2006
3 2006          SPRING  SPRING 2007 (note that spring 2007 is part of the 2006 academic year)
4 2007          SUMMER  SUMMER 2007
5 2007          FALL  FALL 2007
6 2007          SPRING  SPRING 2008
and so on

I've set up a simple hierarchy with only two levels - ACAD YEAR->DESCR

For the cube I have one measure: 'Enrollments' which shows the sum of students who have enrolled

What I'd like to be able to do is show the f

How to set Session time out values


I deployed ASP.Net 4.0  app on ISS, I wanted to set the session time out value to unlimited or any admisible maximum value. How to achieve that?

Date values...How to add a time into the value (ie mm/dd/yyyy 23:59:00)?



I have a SQL query:

ALTER PROCEDURE dbo.GetDownloadDetails_Date
 (@StartDate DateTime,
 @EndDate DateTime)
 SELECT     StatusDate, ProgramName, UserFullName, LoginName, CostCenterCode, EmailAddress, Phone, Manager, ComputerName, SCCMSiteCode, Status,
                       FailureReason, PONumber
 FROM         Installs
 WHERE     StatusDate BETWEEN @Startdate AND @EndDate
 ORDER BY StatusDate

If the user selects a date range, the last day isn't populated, or if they select a single date, that date isn't displayed.  I know the reason as it's because the Statusdate is a DateTime...I manually entered, for the @EndDate 04-10-2011 23:59:00 for the @EndDate and the report generated.

So my question is how can I automatically update the @EndDate to include the time 23:59:00?

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