.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

Shell Dimension - Not Time based

Posted By:      Posted Date: September 15, 2010    Points: 0   Category :Sql Server
Hi   I am creating a shell dimension to create a hierarchy.  The idea is we want to know how much revenue we make from new accounts.  So I have created an account dimension and linked that to the Revenue fact.  I also created a Fact linked to the Date and User dims that will show the date the user was created.  By doing this, I was able to create a calculated measure that will show me total revenue, plus the revenue that is only for new players for a specific month: with member NewRev as aggregate( exists(DESCENDANTS([User Account].[User Account].[All], [User Account].[User Account].[User Account Key]) , [Date].[Date - Calendar Month].currentmember , "User Registration") , [Measures].[Revenue])   select {[Measures].[Revenue]  , NewRev} on columns , [Date].[Date - Calendar Month].[Calendar Month].&[201008] on rows from [Revenue] Sorry it is a bit messy, testing at the moment. What I would like to do is create a shell dimensions so I can create a hierarchy that will show new and returning players as members.  It would have an all level, or the user could split the revenue by this dim.  I set things up like the Time calculations shell dim, but am a bit stuck with how to create the mdx so that it will work with any measure. What would I change to do this? Regards Michael

View Complete Post

More Related Resource Links

Time Dimension Enhancement with Business intelligence Issue

Hi all, I want to add a year over year growth using the BI wizard (Time diemsion enhancement) but when I try to add this enhancement via the wizard then this last one has the button next disabled with a waning that says   A time dimension is required to enable this functionality. Ensure that you have a dimension of type Time, that contains at least one hierarchy with a level flagged as a time period. Inspite of the fact that I added that time dimsension with one hierarchy Time hierarchy Calendar Year Calendar Semester Calendar Quarter Time Key(With namecolumn defined as a named calculation that repsents the day with this format  yyyy, dd mm ) Me personaly I have a doubt about the last condition of the warning (with a level flagged as a time period) but I dont know exactly 1. If my doubt is right 2. What shoud I do to enhance the cube in this context using the time dimension enhancement The complexity resides in the simplicity

Create a dimension based on 2 fields

Hello I have a table as follow: No   Placestart    Placeend 1      DK                USA 2      UK                USA 3      USA              DK Now, I want a dimension called Country, which selects either of the rows where a value exist In SQL it would be ex (SELECT * FROM table WHERE placestart = 'USA' OR placeend = 'USA) So, when I select USA in the dimension all 3 rows are listed, as USA is included either in placestart or placeend. If I select DK row 1 and 3 is selected etc... Is this somehow possible?      

How can i update treeview root node every time based on the record entries

Hi all, I will have an mdi form and with a treeview which has it's initial property as visible =false. If i select an option from menu a child form will exists i will fill some details and click on save when ever click on save i will make the root node of the treeview to some name as File[batch count:0]. Now if i select another option from menu another form will appear and if i fill it with some details and click on save i would like to change the root node name to File[batch count:1] and a child node should exists with some name

Time dimension not grouping correctly

Hello, I'm new to SSAS but I did some reading and some training already. This is my third cube and I'm having trouble with my time dimension.. - first I browse the cube and drag the year and month to the columns section and it correctly renders only the times to which I have data - then I drag my workstation dimention to the rows section and it's also shows the wks fine. - Then I add my counter dimention to the rows section and it correctly shows up as a sub set of workstations. - The problem: When I drop my value fact it renders different value to each counter but show the same value for ANY time, including future dates. (time dimension generated with SSAS) Let me explain the cube, maybe you have seen something similar or could have some insight. Fact: A hourly based table with counters for my workstations. Columns: Datetime, wksid, counterName, and value (value aggregated by max()) Dimention Workstation: id, name Dimension Time: timeid, date, year, month, semester, etc. Dimension counter: id, countername All columns were connected to the correct related columns in the data source view. Why would the cube fail to correctly aggregate my facts by different dates? Any clue? Please let me know if you need any clarification.. Thanks in advance!

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

Getting Query Time Out Problem in Particular Dimension?

We are getting query time out problem in particular dimension in SSAS. can any one help on this?  

Changed to claims based authentication, now I can't access my site. Please help! Time is of the ess

I am in a pretty big bind. I have a sharepoint 2010 site, that was using classic windows authentication. It worked fine from the inside, and I was able to extend it to the outside and it was using https with an SSL certificate. However, my performancepoint reports and my external lists weren't working when the site was accessed from the outside. Apparently this is a known issue with using classic authentication on the outside, so I tried to switch over to claims based authentication. I followed this guide: http://blogs.technet.com/b/wbaer/ar...point-2010.aspx I obviously changed the contoso stuff to my domain name, and changed all of the config files. The problem is, now I can't access the site at all from the inside or the outside. Here is the error I get in my logs: code: An exception occurred when trying to issue security token: Could not connect to [url]http://localhost:32843/SecurityTokenServiceApplication/securitytoken.svc/actas.[/url] TCP error code 10061: No connection could be made because the target machine actively refused it . My Sharepoint Central Administration site gives me this warning: code: The Security Token Service is not issuing tokens. The service could be malfunctioning or in a bad state. Remedy Administrator should try to restart the Security Token Service on the boxes where it is not issuing tokens. If problem persists, f

How big is your time dimension ?

Hi, We have a time dimension which is used by around 90 columns out of 20 tables. We have a fixed time table which started in 1800-01-01 and ends in 2199-12-31. This gives around 150000 members in that dimension. That does hurt the performances and the users complain like why does the time start in 1800 ?. So we created a view a which says give the last 5 years and coming 2 years. Users are happy and the performance is fine. Until one day we added another system. That system has some "strange" dates in it. They are dates like 1900-01-01 ,1901-01-01,1966-02-23, 1995-04-31 but also 2017-01-01, 2019-01-01 or 2022-05-01 or even 2098-03-04. The guy who build the export says : I am not validating but only exporting what the user has entered. The guy who build the import says : I am only importing data and I validate only the type (dates like 2020-03-35 are rejected) So what should I do, make time big again and have angry users and bad performance ? I can make filters so that they won't show up but I don't like losing data and the check reports will fail. I can replace them with another date but I don't like that since I am manipulating data. I can convert them to unknown but I don't like that too. Any other suggestions ? And is a time dimension with 400 x 365 members big ? Constantijn    

Running sum calculation based off date dimension?

I have a fairly simple calculated measure which does a running sum of a particular measure : --This does a running sum of the Net Measure up until the previous date (basically a "Starting Count" for a period. Sum(PeriodsToDate([Change Date].[Year - Month - Date].Levels(0), [Change Date].[Year - Month - Date].PrevMember), [Measures].[Net]) The problem is I have added a second hierarchy in the date dimension that includes quarter called [Year - Quarter - Month - Date]. Is there anyway around not having to create a seperate running sum calculated measure to use this new hierarchy? The PrevMember in the initial calculated measure works, although it reports the wrong number when used in conjunction with the Quarter hierarchy because it gets the PrevMember in the other hierarchy. What is the best practice for this situation?Craig

Dimension member based on period

Hi, I have done several SSAS cubes in the past, but this is the first time I have run into this problem. I have tried searching for an answer online, but I am unsure of how to pose my question/search appropriately. After many hours of searching (someone MUST have run into this before), I thought I'd try the forum. I will be using SQL 2005 SSAS. The issue concerns a dimension that is linked to my fact table via a middle table that creates a many-to-many relationship. This middle table contains a date that the cube would need to be filtered on (which would be a further dimension, I assume). Note that I plan on allowing reporting on the cube via MS Excel (pivot tables). My table structure (highly simplified for testing purposes, it contains upwards of 15 other dimensions) is laid out as follows: --dimension = RecordableInjury CREATE TABLE [dbo].[safe_RecordableInjury](  [RecordableInjuryID] [int] NOT NULL,  [RecordableInjury] [varchar](100) NOT NULL,  [RecordableInjuryShort] [varchar](5) NOT NULL ) --fact = Injury (the measure on this table will be a distinct count of InjuryID) CREATE TABLE [dbo].[test_Injury](  [InjuryID] [int] NOT NULL,  [IncidentDate] [int] NOT NULL ) --joiner between fact table & RecordableInjury dimension CREATE TABLE [dbo].[test_InjuryRecordability](  [InjuryID] [int] NOT NULL,  [StatusDate] [int] NOT NULL,

Dimension Range over time

Is there a method for categorizing measures into ranged dimension attributes over time.  Consider the SQL:       SELECT date, sales = SUM(sales), segment = CASE WHEN SUM(sales) BETWEEN 0 AND 99 THEN 0 WHEN SUM(sales) BETWEEN 100 AND 199 THEN 100 ELSE 300 END      FROM tabl1      WHERE date BETWEEN '01/01/1900' AND '12/31/1900'   Date Sales Segment 1/1/1900 100 100 1/2/1900 150 100 1/3/1900 49 0 1/4/1900 175 100 1/5/1900 99 2 … … … Is it possible to apply this logic across a time range?  For example, this is on a daily basis, is it possible to do this on a monthly, yearly basis or for a range like '01/02/1900' - '05/25/1900' at monthly rollup? Month Sales Segment January 999 300 February 1 0 March 500 300 April 199 100 May 99 0 … … … I've done this using keys on the fact table; they aren't dynamic, but aggregate the measures into defined buckets January 1900, February 1900, etc.  My keys on the fact table are at the monthly level.

Time Dimension


HI All,


I have seen that in on our cubes, the time dimension is setup as a regular type dimension instead of time. How would it affect the users? What's the process to change it back to time dimension? Please advise..


Thanks in advance.

Please Help with Defining Calculated Measure based on Dimension Members



The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

Scope ([Measures].[NR Var], {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2006], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2007], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2008], 2, self_before_after)});
this=[NR]-[F NR];
End Scope;

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want the scope to affect as well.

So I ideally I would like to have something like:



case when {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

Range dimension - possible with Relative Time Periods?

Hi All,

I have a Date_Range dimension with members 7 days, 30 days and 90 days only.
Although all reporting needs could be well address using existing date dimension and
MDX filter or range operator I need a physical Date_Range dimension.

It seems possible to address my requirement (implementing Date_Range dimension) by Handling Relative Time Periods .
The blog post (by Chris) is rather old and I did not find updates on it or recent similar articles.

Please share your thoughts on how best to address this requirement.


Prakash Gautam

Add time to time dimension?



I have a time dimension in my cube that I've been using successfully for 2 years. The problem is probably a lack of forecasting in my part, but it stopped at september 30 th 2010 and I don't know how to "add time" to it. It is a table called Dimtime.time and was created automatically by Analysis Services 2005.

time dimension order month


hi guys,

i use this mdx query: "select {[Measures].[NoOfCustomers]} ON COLUMNS,
  Hierarchize(Union({[Occupation].[All Occupation]}, [Occupation].[All Occupation].Children)) ON ROWS
from [loans]
where Tail(Descendants([REPORT DATE].[All Report Date]))"

All Report Date









All Report Date











Thanks in advance


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?





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