.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

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

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

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.

View Complete Post

More Related Resource Links

semi additive measure in an other dimension that time




I would like to prevent aggregation of customer measure on product dimension. But customer is aggregeable on time dimension !. it is a semi additive measure but in another dimension that time dimension.

in this mdx example (Adventure works), how it is possible to prevent aggregation of customer measures (preferable in the MDX script of the cube) to force customer to be null when calculated members are evaluated.


In this example the result should be null for customer for [Product].[Product Model Lines].[R

Segregating Measure values based on the dimension value into separate columns.


Hi Hopefully you can help me with another problem I'm having.  I'm trying to design a regular SSRS report using a cube as data source here is where I run into difficulties.  This is a financial type report so it has attributes such as Actuals, Forecast,Plan etc.  These attributes reside in a cube in a dimension called "PROCESS".  The measures is of course dollars.  So what I need is to somehow segregate dollars for actuals into one column forecast into another etc etc in my query designer so my dataset has 3-4 distinct dollar columns on which I can build my report. I thought I could accomplish it by using a calculated member functionality but I can't find anything there that will let me filter the values by "PROCESS" dimension.  I've also tried to create separate datasets filtering dollars the way I need them in each however report will not let me have fields that belong to two different datasets.  Any thoughts on how to solve this issue.  Hopefully using GUI instead of MDX editor. 


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

display non measure, non dimension fields in drill down

Hi All, I created my fact table with more fields than just the foreign keys linking dimensions and the field(s) to be used as mesures.  I did this hoping that on drill down I would be able to see the extra fields so that the user would have access to detail information on the records making up the measure amount.  The extra fields do not appear on drill down.  How can I make them appear, or am I on the wrong track? Thanks for any help

Dynamic Dimension with Aggregate Values

Hi, I have an specific requirement to make the measure value as an dimension. Let me explain my problem in brief. I have a fact table with dimensions like Time, Products etc and having single fact table with two measures. I have to create a calculated measure which shows the average of Measure 1 (here used calculated measure because there are couple of other calculations involved). And other two calculated measures. when I drill down with Products dimension for Calculate measure 1, it shows the average value for each products. Now, I want this calculated measure values (includes Product dimesnion drill down) as a Dimension and based on this value, I need to show the value of other two measures. For example: when the dimension products is used for drill down the values displayed will be like this and in this I need CM1 to be another dimension Products CM1 CM2 CM3 P1 0.10% 20 1 P2 0.20% 40 2 P3 0.30% 80 3 P4 0.40% 70 4 P5 0.50% 30 5 P6 0.60% 110 6 P7 0.70% 120 7 P8 0.80% 130 8 P9 0.90% 86 9 P10 1.00% 65 10 when CM1 is used as a dimension it should show the value like this CM1 CM2 CM3 0.10% 20 1 0.20% 40 2 0.30% 80 3 0.40% 70 4 0.50% 30 5 0.60% 110 6 0.70% 120 7 0.80% 130 8 0.90% 86 9 1.00% 65 10 How can we create the dynamic dimension with the aggregated values? Any assistance will be greatly apprec

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.   

MDX Query Measure with time period

Hi! How to set up the period in MDX? I need the measure what would be seted up by default 4, 29… days from today. For axample Amount from current day (02.09.2010) till 4 days from today is 06.09.2010. As Result I have to get a report: Buy form Vendor Amount today 02.09.2010 Amount +4days 02-06.09.2010 Amount +29days 02.09-30.09.2010 A 10000 250000 333333 B 150000 222222 555555 C 666666 444444 1222222 Sincerely, Milena

how to process just one partition along with other measure group and dimension in SSIS package Analy

HI All, i have to process just one partition1  of measure group A ,along with this i suppose to process all the Measure group and dimension with the help of SSIS Package Analysis Services Processing task. Partition1 having a query which fetch data only for previous day only. what i have done i select partition 1 in process data mode,all other measure group in full mode and dimension in process update mode.   i haven't taken measure group of partition1 and also not taken cube in the processing list ,when i run the package ,it runs suceesfully but data not get uplaoded into the Cube.   kindly suggest what other measures should i take to update the data . Amit

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!

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Compare initial and final values using javascript

My Page consist of  8 text box and Button named update...Initially when the page is called values are filled in to the textbox from the DB;here is wat i require1. at intial page load i want the values in of 8 textbox to stored in array in javascript .2. when pressing the button update, i need a method (javascript) to comapre the initial on load values with new values(ie if values of textbox is changed ) so           that the text box background color should displayed in different color..In Simple words ..i would like change back ground color of text box of changed values in the text box when clicking button update.please note that i would like this to be performed  by OnClientClick method of button

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

How to measure WCF start up time ? ( WCF service .NET 3.5 hosted in IIS6 under win 2003 )

Hi ,   I have relative big and complex WCF (.net 3.5)service hosted in IIS 6 .  When IIS worker goes down , it takes a lot of time for service to wake up.  My question is how can I measure what exactly takes time ? Or its just service compilation time and nothing could be done here ? If I know what takes time I will try to optimize it .    Thanks. 

Shell Dimension - Not Time based

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

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?  

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    

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.
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