Analysis :: SSAS Calculated Members For Each Week Of Month
Jul 6, 2015
I have a date dimension with structure/hierarchy (year - month - wkofmonth - day) & measure count
This works fine when I insert wkofmonth in columns & count in values but when I add another measure it duplicates per week
Product CountWk1(jan) CountWk2(jan) CountWk3(jan) CountWk4(jan)Â SalesVolume
1Â Â Â Â Â Â Â Â Â Â Â 1Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 2Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 0Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1000
Is there something I can do on the structure & do 4 calculated members pending how many weeks in a month, is there a simpler way where the next measure does not calculate by columns field.
If calculated member for each week, how do I do this, any example code?
I'm trying to do a currency conversion with an MDX statement in my Cube SSAS 2012.
Here is my script :
      SCOPE( LEAVES([Entity]) ); SCOPE( LEAVES([Time]) ); SCOPE( LEAVES([Currency])); SCOPE( [Account].[Account].[Total ACCOUNT].members) THIS = ([Measures].[Value],[Currency].[Currency].[Local])*([Measures].[Value],[Account].[Account].[Fx Rate]); END SCOPE; END SCOPE; END SCOPE; END SCOPE;
The problem is I want to exclude frome the scope Currency my local Currency in order to make the conversion only if a currency (€,$, £) is selected. I tried the following syntax but it always return a "MDX script is not valid" :
I'm working with the statistical functions Stdev and Median with calculated members. The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself). Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :
stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value])Â returns answers in the thousands when it should be more like 2.5
When used with a query, there would only be a single date member and a specific parameter member. The total number of fact records is between 200 and 500 with values that range between 0 and 150. This is the version that gives me answers that resemble the total sum of the [Measures].[Value].
If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....
Do anybody know if Analysis Services 2005 allows cashing (storing) calculated members ?
There is a cube in Analysis Services 2000 with a calculated member based on some running sums. The performance of MDXs is too slow because running summs are being recalculated for each member of time dimension on each call.
I think that caching calculated members might help me and hope that this feature exists in Analysis Services 2005 .
I have a Calculated Member in SSAS that I need to adjust based what the current member is.Â
The code is below
CASE WHEN [Measures].[End LIS] = 0 AND "HELP" THEN CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE Â ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2) END
In English - i need this to translate to -Â of End LIS is 0 "AND the current member is the current month and current year" THEN carry onÂ
CASE WHEN [Measures].[End LIS] = 0Â AND [Dim Date].[FSCL_YM].[Month Nm].currentmember.membervalue = Format(now(), "yyyy")+"]&["+Format(now(), "M") THEN CASE WHEN [Measures].[Beginning LIS] = 0 OR [Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS] = 0 THEN NULL ELSE Â ROUND([Measures].[Disconnects]/(([Measures].[Beginning LIS] + [Measures].[Beginning LIS] + [Measures].[NETACTIVATIONS])/2) * 100 ,2) END ELSE ROUND(([Measures].[Disconnects] / [AVERAGELIS] * 100) ,2) END
I've been working with SSAS for a good few years now but I keep bumping into this problem - my users are trying to build a measure that is based on a calculated attribute and finding it difficult to work out how to write the MDX to do so. Intuitively, they thought a Calculated Member would work, but I don't think a Calculated Member is quite the same thing from my understanding.
So, here's the scenario.
We have a Product Dimension. We have a Measure that is the Number of days the Product took to make, e.g. 5 days. We also have a Product Count measure that counts the number of Products.
The user would like to write a calculated measure that works out the number of products that took <5 days, 5-10 days, 10-15 days etc.It would be easy to write a set of calculated measures for each of these bandings, but the user wants effectively a single dynamic attribute to use in the calculation in order to automatically distribute these values across the columns in their pivot table.
Is this even possible? I was thinking I could build an attribute on the Product Dimension in the ETL to do this quite easily, but the user wants to be able to change the bandings on the fly by changing the MDX for the attribute, rather than go back to the developer every time.
I'd like to add a yesterday dimension member to a new dimension, like a "Time Utility" dimension, that references the second last day of non empty data in a cube.
At the moment, I'm doing this:
Code Snippet
create member [MIA DW].[DATE TIME].[Date].[Yesterday] as [DATE TIME].[Date].&[2007-01-01T00:00:00]
select [Measures].members on 0, non empty [DATE TIME].[Date].members on 1 from [MIA DW] But the [yesterday] member does not seem to belong to [DATE TIME].[Date].members?
So I guess there's two questions:
1) Can I have a new empty dimension which contains all these special members like "Yesterday" or "This Week" and "Last Week" (these last two obviously refer to a set of Dates)
2)How come the Yesterday member is not returned by the .members function?
In my cube there are two measures which are used in different calculations.Now I'm need to show in report if there any months in data when both or even another one of the measures is not updated (value = 0 or NULL).
how should I create the calculated measure for that?
I have tried in mgmt studio to plan this but I'm in a loop of errors.
I need a Select sentence that return me the first week of the month for a given week.
For example If I have week number 12 (Begins 2015/03/16 and Ends 2015/03/22) I need that returns 9, I mean Week number 9 wich is the first week of march (having in mind @@DATEFIRST).
I only need give a week number of the year and then returns the week number of the first week of that month.
Hi... I'm trying to make a calculated member but I want it at the last level only, with the others I want that shows the sum of the previus :confused: .... Somebody can help me????
I have a reporting services report that I'm creating from an Analysis Services cube. I created two calculated memebers. When I put either one of the calculated members in the report and try to render the query or generate the report, I get an error: Memory Error: Allocation Failure: Not enough storage is avaliable to process this command. I have 21 GB of hard drive space.
I have created a few calculated members under one dimension (meaning the parent dimension is not Measures, but other dimensions). It can be showed in the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS Office 2k, xp, even 2003). Is there any solution to display the calculated members (as with the dimension) in Excel PivotTable? vba code needed? service pack needed?
Hi, I created a cube with 4 calculated members. Data is ok within cube browser. When building report in Excel with Pivot Table or with Analysis Services Excel AddIn, I can't have access to these 4 calculated members. I'm using a olap connexion. What did I do wrong ? Thanks a lot for ur suggestions. Vincent
I've read the MSDN documentation about creating calculated members and states that we can only create session scope or query scope members.
I need to create a static calculated member, one that stays there until it's deletion... That is possibel using Analysis Services, but what about with MDX or DSO?
I have an analysis services 2005 cube with a lot of Calculated Members (for example I have a "Sales Volume in kg" and a "Sales Volume in squaremeters (m²)" and a calculated member "Sales Volume in g/m²" with (kg * 1000 / m²), and so on. This Measures should be shown with a hierarchical Product Dimension in which I can drilldown from a level1 to a level 4.
I can select them all with a MDX Query in the Data-part of reporting services. In the layout-part, I have a matrix-control which should show the values in the above form. The value-part of the matrix has the form "sum(field)". I know, it's not correct, I believe I should use "AVG(field)".
When I use non-calculated fields, all works ok. With calculated fields, I get the error
[rsAggregateOfMixedDataTypes] The Value expression for the textbox €˜textbox3€™ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type.
It's the same with sum and avg (as the error message tells me). But how can I use such fields in reporting services? My users wants a report with the product hierarchy vertical and the salews volume g/m² horizontal so that the values are correct on every level the drill in. In Excel, OWC or other analysis clients, it's no problem to do this, but I can't find a way, doing this in reporting services.
Hi, It's a long story i'll try to make short, I am running a MDX query through Integration Services, this query is very very memory intensive, so intensive I get a System.outofmemory error. The workaround for this was getting the data in smaller ranges. this works fine in Management Studio.
select { many measures and calculated members} on columns, [Cuenta].[Cuenta].&[1]: [Cuenta].[Cuenta].&[10000] on rows from DB WHERE [Tiempo].[Mes].&[2007-09-01T00:00:00] : [Tiempo].[Mes].&[2007-11-01T00:00:00]
Now, in Integration services the optimal way of running a MDX query should be through an OLEDB source, but as it's stated in many sites around the web there is an error that hasn't been fixed. So I moved to a Datareder which is much slower, but works... kind of... to get the whole data out of the cube I am getting it in ranges which I should change with parameters, but MDX doesn't support parameters so I have to change the whole query using an expression.
Finally the problem with this is that the query I am running is longer than 4000 characters which is the limit for an expression on integration services. Here is when someone told me to shrink my query by using named sets, and this is what i don't know how to do, is there any way of grouping all the calculated members I have created in one named set or something alike.
I created some calculated members for a dimension. They are set to be visible and when i connect to the cube itself I can see them with both Excel and ProClarity as clients. I added them to a couple perspectives (by checking their boxes down at the bottom of the Calculations list) and deployed. There were no errors or issues. However from both Excel and ProClarity I do not see the calculated members of the dimension.
The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states.  Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state.Â
So far what I've got is:
CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL; SCOPE (Measures.[weighted measure state and above], Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE), Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE), ... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;
This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.
A very basic MDX question. Using the following from AdventureWorks as an example:
Code Snippet WITH MEMBER [Measures].[Big Cities] AS ( [Geography].[City].&[New York]&[NY] , [Measures].[Internet Order Count] ) SELECT [Measures].[Big Cities] ON COLUMNS , [Customer].[Total Children].Children ON ROWS FROM [Adventure Works]
How would I re-write the [Measures].[Big Cities] calculated member so it included both [Geography].[City].&[New York]&[NY] and [Geography].[City].&[Los Angeles]&[CA]? Assuming there is no logical connection between these members, and that they are non-contiguous. Also, in my real example, I cannot move [Measures].[Internet Order Count] to the WHERE clause, it must remain within the calculated member.
I am trying to calculate the number of the week within a month of a given date. in other words, given a date I need to find out whether it's the 1st week or 2nd or 3rd or 4th or 5th week of that given month.
for instance let's call my function "weekOfMonth()":
weekOfMonth(4/19/2004) should return 4 (it's the fourth week of april) weekOfMonth(4/7/2004) should return 2 (it's the second week of april) etc ..
Running a SQL 7 system on a Windows 2000 server using Access 2000 on clientmachines as a front end. System administrator currently reboots the serveronce a month. Yesterday we had some weird thing with the database whereusers were getting ODBC errors when trying to access it. Rebooted theserver, everything was fine. Suggested to the sa that he reboot the serveronce a week. He said he already does it once a month, and that's sufficient.My POV is that: a) doing it once a week might prevent situations such as theone yesterday; b) even without situations like the one yesterday,performance may be degrading over the course of the month, without our beingaware of it, and rebooting once a week might help performance.Any thoughts on this?Thanks!Neil
I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.
WITH MEMBER [Measures].[New Measure] ASÂ IIF( ISEMPTY ([Measures].[Measure 1]), NULL, SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))
I've got a dimension built from a fact (whatever that's called?) ... it's a date interval field, i.e. 0-5 weeks, 6-10 weeks 11+ weeks. How do I sort these members in the respective order? Looks like this currently:
The problem lies in the fact that I don't have any secondary attributes to order it by, i.e. it's not a physical dimension where I can use a key for the 3 members. I was hoping I wouldn't need to create a separate dimension to get round this.
We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
