.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

MDX - ParallelPeriod using a semi additive measure

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
Hello, I'm having some trouble trying to get a figure for previous year when using a semi additive measure (LastChild). For a measure that uses Sum the process would normally be: SUM(PARALLELPERIOD([Time].[Fiscal].[Fiscal Year], -1, [Time].[Fiscal].CURRENTMEMBER), [Measures].[Whatever] )   But obviously if I do a sum against a semi-additive measure it gives me a massive amount.   To give a little background the data that is in the source table is already a summed amount so the table is: Date                    Category            Amount 01/04/2010           Blah                   3654   etc.   Is there any way I can achieve an equivalent of the above MDX using a LastChild member? The only other thing I can think of doing is to split the source so that (for the above) 01/04/2010 in the blah category would have 3654 rows and then do a sum of that in AS.   Any ideas??   Many thanks in advance :-) yayomayn

View Complete Post

More Related Resource Links

SSAS 2008 - I cannot seem to have semi-additive and normal additive measures in the same measure gro

I have a measure group with both conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty). The cube processes them quite happily, and I can view and analyse (slice) them fine, but when I analyse them both together, then the conventionally aggregated measures (aggregation type SUM) always dissapears in favour of the semi additive measures. This is not the case if I analyse conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) from different measure groups.Why does this happen, and is there a way to overcome this by pref not creating seperate fact sources for the conventionally aggregated measures (aggregation type SUM) and semi additive measures (LastNonEmpty) currently in the same fact source.

semi additive measure

Hi, I have have started working on SSAS recently. And was reading about semi - additive measure. And the most important point that struck me was all semi additive use the selected function for the aggregation with time dimensions and sum with all others. So i decided to test it by building a very small cube with two dimensions i.e. Period and Product.  Period dimension has single hierachy with two attributes i.e. year and month.  where as my product dimension contains only one attribute hierarchy i.e. product. I have defined a measure called closing stock with aggregate function being set to lastnonempty. Note: My period dimension is of Type Time I process the cube and start browsing it using cube browser. I first drag period hierarchy on my rows and measure in data area and it properly shows lastnonempty for that particular year. I remove period hierarchy and drag product attribute on rows but rather than summing it up it still shows lastnonempty for all products. Is there something i am missing? Please let me know. Regards  Niks  

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

Semi-Additive Annual Comp Measure Puzzle


Environment: SQL Server 2008 (not R2) Analysis Services; Panorama Novaview

I've created a factless fact table that records employee information for a payroll warehouse. Users can use dimension attributes such as Annual Compensation, Full Name, etc. to find out information about employees. There's currently only one measure called "Headcount" which is a distinct count of an employee ID (unique to the employee). There's a row for each day for each employee and the employees attributes describe how he/she looked on that day (using type 2 SCDs).

Users have requested that they see the sum of the annual compensation. This seems more like a semi-additive measure than anything else. For example, let's say a department has 3 employees for the year of 2009, each with annual comps of 20;30;40 (thousand) respectively. In the actual relational table, there will be a record for each employee for each day (point in time information). If annual compensation is added as a measure, then the measure would be = Annual Comp * No. of Days in 2009. That means, we'd have 20*30*40*365 ---- this is not what they want to see. They want to see a total of $90,000.

How could you do this?

Here's some more info about this:


CLR Inside Out: Measure Early and Often for Performance, Part 2


In the second of a two-part series, Vance Morrison delves into the meaning of performance measurements, explaining what the numbers mean to you.

Vance Morrison

MSDN Magazine May 2008

CLR Inside Out: Measure Early and Often for Performance, Part 1


In this month's column, get the inside scoop on how to build performance into your apps from the start, rather than dealing with the fallout after you deploy them.

Vance Morrison

MSDN Magazine April 2008

Decision tree dependency measure

The dependency network viewer executes the stored procedure System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph which yields some integer measure that represents the strength of influence of input variables on the output variable. How this measures are calculated (information gain, chi-square, correlation etc)? It seems as if conditional influences are not taken into account: if A and B are two major factors which impact variable C, but A and B are strongly correlated so that C given A is not dependent on B, dependency algorithm will still depict B as the second major factor. Am I right? So there is no analogy of tests like conditional chi-square, conditional mutual information or partial correlation?   It seems to me that MS Data Mining lacks some kind of Bayesian Networks algorithm wich would illustrate conditional dependencies. That would give a useful insight on how various factors are related to each other and through what kind of chains a change in some input variable transforms to the output.   Thank you.

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

Measure Dependent Calculated Measure

Hi Was looking to create a calculated Measure Which needs to be dependent on another column of the FACT table. I need to have the Average of the particular Measure based on the fact that this particular 'set' has a common Related dimension's The example would give a better picture. I have a set of dimensions like STUDENT, TEACHER, COURSE etc as my dimensions and the grades, class standing based on marks for the subject, percentage of marks scored, Percentile, Marks Scored,StudentID and the related PK's etc are the measures on my fact table Here I want the Avg of the marks scored by the Students based on the grade say is  A or B or C  I was looking to create a calculated measure by using MDX which was a follows (NOT THE CORRECT SYNTAX JUST THE IDEA) SUM  (MARKS SCORED)/COUNT(DISTINCT StudentID) WHERE GRADE = A As this would be measured against a particular subject or a teacher or a Course. I was hoping if I could get help in putting this in a proper SYNATAX   Thanks in advance.  

Please help with converting calculated measure.

Hi, I have to dimensions: [Currency] with members CAD and USD and [Convet to] with members None, CAD, USD. The measures involved in calculation are [Amount Billed] and [Amount Received] The problem calculation is defined pretty simple: MEMBER CURRENTCUBE.[Measures].[AR] AS (abs([Measures].[Amount Billed] - [Measures].[Amount Received])>0.1,[Measures].[Amount Billed] - [Measures].[Amount Received] ,null); and worked perfect without currency conversion . I've read some info about currency conversion and designed this calculation to convert: scope (leaves([Time 2])); scope([Convert To].[Convert To].&[USD],[Currency].[Currency].&[CAD]); [Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]); [Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])/validmeasure([Measures].[Cdrate]); End Scope; scope([Convert To].[Convert To].&[CAD],[Currency].[Currency].&[USD]); [Measures].[Amount Received] = ([Measures].[Amount Received],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]); [Measures].[Amount Billed] = ([Measures].[Amount Billed],[Convert To].[Convert To].&[None])*validmeasure([Measures].[Cdrate]); End Scope; End Scope; That calculation provides correct results for both [Amount Billed] and [Amount

processing measure group : memory error : the operation cannot be completed because the memory quota

Hi, I'm stucked with this problem. Untill last week, the cube processed without any problem. Since last week, I'm getting this error. I have been searching in different forums, and I tried some suggestions, like changing memory limit properties, ... It is getting worse.. So I reset all properties to default again. I am running SQL-Server + MS-AS 2005 SP2 on server with 4GB of memory. This is a dedicated server, nothing else is running on it. The fact table has +/- 14 million records, several dimensions en 2 measure groups. I don't have problems to process the dimensions, but when I try to process the cube or the measure groups of that cube separately , the error persists. I have changed the datasource view, and replaced the fact table by a Named query. Even when I put a 'WHERE datapart( year , fact_date ) >= 2009 ' clause to reduce the number of records to +/- 5 million, I'm still getting the error. I don't understand what is wrong, the cube always processed since +/- 2 years. As I said, I have found a lot of this kind of Issues on different websites, I have been trying to change some properties. But this still does not solve the problem. Could it be that MS-AS settings are corrupt somewhere ? Is it a good idea to re-install MS-AS 2005 + SP1 + SP2 ? Or is there another reason possible ? I really appreciate any kind of help, because I'm

Optimize Calculated Measure containing COUNT EXISTING

Hi, My goal is to change the text color of all cells that contain aggregated values. Currently I achieve it like this: I COUNT the members of all attributes of all dimensions. To be multi-select-safe I am using the EXISTING keyword. If the members count of at least one dimension is not 1 than the background color of the cell is changed. CREATE MEMBER CURRENTCUBE .[Measures].[SingleCellSelected]   AS iif ((COUNT (Existing ([Dim1].[Attr1].[ Attr1].MEMBERS ))=1) AND (COUNT (Existing ([Dim1].[Attr2].[Attr2].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr3].[Attr3].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr4].[Attr4].MEMBERS ))=1) AND (COUNT (Existing ([Dim3].[Attr5].[Attr5].MEMBERS ))=1),1,0),   VISIBLE = 0  ;    SCOPE ([Measures].AllMembers ); FORE_COLOR (this ) = iif ([Measures].[SingleCellSelected]=1,0,16744448); END SCOPE ; This approach works but it performs badly with attributes with many members. Do you have any idea how to optimize this? Thank you!

Measure - New Customer Count

I need to create a measure which counts the number of new customers for each time period. My fact table contains the customer number and its easy to create a distinct count of customers per month/week/year. I'm thinking I need to obtain the first order date for the given customer and compare if the select period is within the time frame, then include or exclude.  

How to create a Measure without aggregations

Hi all, The data in the fact table has columns which are precalculated ranks for the products based on sales. How to create them as measures in the cube without any aggregations. I selected the properties for measure as "No Aggregations" while creating. But I dont see any data. Then I tried to create those columns as seperate dimensions by doing self join to the fact table.In this way I get the correct data,but the performance is slow. Is there any other way to try out which has both data and performance. Please help me out. Thanks, Sam

"Non-additive" target value in KPI. How to present it in a scorecard or Pivot Table.

Hi.. I would like to have some help in designing the following solution. It musn't be to hard, but i can't figure it out yet.. I have 2 tables: Group and Worker. Table Group has GroupID and GroupTarget. Table Worker has WorkerName, GroupID, WorkerTarget and Amount. The relation between tables is GroupID, as each worker belongs to a group. Now I want to take this to a pivotTable, to present KPI for each worker, and the value of the indicator is calculated comparing the Amount vs the WorkerTarget. Also I setup a single hierarchy in the PivotTable, where the parent is the GroupID, and when displayed, you can see all the Workers belonging to that group. Now the problem is, Everything is fine when I want to see the actual group total amount value, as I just have to SUM all the Amounts of each Worker, and I can see the GroupAmount. But.. ¿How can I display the GroupTarget in the PivotTable in the same column as the WorkerTarget? I don't mind a solution using SSAS or PerformancePoint, but my idea is in SSAS to put some kind of formula in the KPI target, so when I'm in a level of the hierarchy, the target is WorkerTarger, and when I'm in the parent, the target is GroupTarget. Is there some kind of formula like this??   If not, any other idea will be much apreciated.. Thanks a lot.. regards.. Canario O.

Help about cascadin M2M with parent child hierachy custom rollup for one measure

Dear MSAS Experts, My name is Andre and i am writing my thesis about data quality which needs to be finished in about 3 weeks.:-( For my thesis i needed to develop a Schema which store data quality issues. This is a part of my schema which includes a cascading M2M: Facttabele: ErrorFact / ChecksFact / RULE ID / ... DT_RULE: Rule_ID / ..... RULE_ATTRIBUTE_BRIDGE: RULE_ID / Attribut_ID => M2M beetween Rule and Attribut(Rules can belong to multiple Attributes) DT_Attribut: Attribut_ID / .... Attribut_KPI_Bridge: Atribute_ID / KPI_ID => M2M beetween Attribute and KPI (Attributes can be used for multible KPI calc) DT_KPI: KPI_ID, KPI_Name, KPI_PARENT => Parent Child Since MSAS do not provide a Agrgatefunction I have a calculated Measure (lets say its called 'EC') which calculates the average of ErrorFact/ChecksFact. Now the problem I do have is that the 'EC' Measure should be aggregated within and only in the DT_KPI dimension that every child which belongs to the same parent should be muliplicated ('*') with each other till we reach the all member node. So if we have the following PC-Hierachy: -A --B ---C ---D --E The result of A would be (C*D)*E = A. B of course would show the result of C*D. On the other hand all the other facts should use the normal aggregation for this dimension. Also EC should be aggregated normal in all other dimensions. I tried usin

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