Analysis :: Parallel Period Sliced By Other Dimensions
Aug 4, 2015
I have make a calculated member for previous period of an given date range. The previous period is the same date range from the previous year, and I have managed to achieve that with the calculated member:
Create member currentcube.[Measures].[PrevPeriod] as
(ParallelPeriod( [Start Date].[Cal Hierarchy].[Year], 1, [Start Date].[CAL Hierarchy].CurrentMember), [Measures].[Count]);
This member returns the correct result as long as my query uses the time dimension, which makes sense... but I also need to show results sliced by other dimensions in bar charts that do not display the time dimension. For example, I have a dimension with only 3 members called [Region].[Area].[AreaName].
The result set for the bar chart needs to look like this:
[AreaName]Â | [Count]Â |Â [PrevPeriod]
East           |   43     |      56
West          |   53     |      95
But the [PrevPeriod] only returns values if I include the time dimension. I essentially need to sum the results of the time dimension/AreaName/[PrevPeriod] tuple down to just Areaname/[PrevPeriod] for whatever date range may be involved.
I don't know if this is significant to the issue, but the client tool that generates the bar charts builds the query with the date range as a subcube in the FROM statement. If the [PrevPeriod] is outside of the subcube that is still OK, as long as the time dimension is included in an Axis on the final select statement, so at least I know I am not suffering from the members inside the subcube. I've also found in SSMS that it makes no difference if I make the query a subcube, or put the date range in a where clause instead; I still get NULL for [PrevPeriod] without the dates.
I can't imagine that this is an unusual situation, so I hope I've explained it adequately! What is the recommended technique for summarizing a Parallelperiod by dimensions without displaying the time/dates ?
I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.
Our client wants to report on their trade volume for last year as compared to the current quarter. For simplicity let's pretend they have a report where they have a two key measures:
is based on outlook - that is, for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until the current period, and forecast for the current and future periods.
[Trade Volume - Tons MTD] is based only on actuals - that is for any period we are reporting on the trade volume will be reported as actuals that have been loaded up until and including the current period, and 0 for any future periods.
If Feb09 is our current period, and we are using quarter on the time dimension (where quarter 1=Jan09,Feb09,Mar09) and we have the following data:
This is a problem, because the comparison with their current results ([Trade Volume - Tons MTD]) with what they 'forecast' ([Trade Volume - Tons]) is not based on the same period of time - we are comparing the sum of two periods versus three periods.To solve this we changed the reporting period to be monthly granularity, and now select Jan09-Feb09 as our range (as opposed to having a quarter granularity and selecting Q1,2009 in the example above).
This works well and produces the expected results:
However, this introduces a secondary problem: we are doing a prior year calculation on the Trade Volume also, so the users can compare how the actuals are comparing to the same period last year.To do this we use the following formula for the prior year calculation:
Prior Year Actuals=([Measures].[Trade Volume - Tons], ParallelPeriod([Time].[544 Hierarchy].[Period Year],1,[Time].[544 Hierarchy].currentmember))
The problem is as soon as we move from quarter granularity to (monthly granularity AND select more than one monthly period) the Prior Year Actuals calculation produces a an error "The MDX function CURRENTMEMBER failed because the coordinate for the 'Period Year' attribute contains a set".So, ParallelPeriod does not like it when currentmember is a range (Jan09,Feb09) rather than a single period (Jan09).
I have a problem where I have 3 three measures in a virtual cube: "Actual", "Budget" and "Full Year Budget".
The dimensions I have are: - Account No_ / Name - Cost Code - Sub Cost Code - Time/Dates - Budget Name
Both "Actual" & "Budget" measures need to be filtered/dimensioned by: - Account No_ / Name - Cost Code - Sub Cost Code - Time/Dates (exclusive to "Actual", "Budget")
Thus have put these in one cube
AND "Full Year Budget" needs to be filtered/dimensioned by: - Account No_ / Name - Cost Code - Sub Cost Code - Budget Name (exclusive to "Full Year Budget")
THUS have put this as one cube…
I then created a virtual cube, with the 2 cubes thinking that the dimensions I created in the original cubes would only filter the measures of the original cube measures in the virtual cube. ...BUT all dimension filters in the virtual cube filter all measures in the virtual cube, irrespective of which dimensions were created with the original cubes.
I'm new to MDX, and most of the time I customize existing queries rather than writing new ones. I currently have a MDX query like this
SELECTÂ [Measures].[Fees Billed] Â Â Â Â Â Â Â Â Â on 0, except([Age].[Day Buckets].members, {[Age].[Day Buckets].[All], [Age].[Day Buckets].&[Unknown]}) Â on 1 FROMÂ Â Â Â MyCube WHERE ([Fiscal Period].[Fiscal Year].&[2015], [Customer].[City].&[Auckland] )
Which brings the fees billed by age buckets where the customer's city is Auckland. I also have another dimension called [Sales Agent] with a member [City] in it, and there is a member in [Customer] called [Customer].[Sales Agent]
I am trying to retrieve the same information where the customer's sales agent's city is Auckland rather than the customer's city.
If it is SQL, I will join Customer and SalesAgent on Customer.SalesAgentUno = SalesAgent.SalesAgentUno and bring in the desired data. Any way in MDX to do this?
I need to show the dimensions of my model like columns in the result. I have this query
with member [Measures].[Customer] as [Customers].[Customer].CURRENTMEMBER.Name member [Measures].[UCs] as [UCs].[UC].CURRENTMEMBER.Name member [Measures].[Order Type] as [Order Types].[Order Type].CURRENTMEMBER.Name member [Measures].[UC Dates] as [UC Dates].[UC Date].CURRENTMEMBER.Name
Actually I want to do distinct sum on a measure group, please find the below table as sample
XL Measure group LKÂ Â Â Â OKÂ Â Â Â Â Amount 1Â Â Â Â Â Â Â 10Â Â Â Â Â Â Â Â 100 1Â Â Â Â Â Â Â Â 11Â Â Â Â Â Â Â Â 100 3Â Â Â Â Â Â Â Â Â 30Â Â Â Â Â Â Â 250 3Â Â Â Â Â Â Â Â Â 31Â Â Â Â Â Â Â 250 3Â Â Â Â Â Â Â Â Â 32Â Â Â Â Â Â Â 250
For the above measure group two dimensions have relationships, One is L dimension which is having relationship with XL on LK and One is O dimension which is having relationship with XL on OK. If I drag L dimension attributes  it should show results as below
LK LName Amount 1    A        100 3    C         250
But above results are coming as below
LK LName Amount 1    A        200 3    C         750
If I drag O dimension attributes along with L dimension, it should show results as below.
LK  LName  OK     OKName  Amount 1        A        10      XYZ        100 1        A        11      UVW       100 3        C        30      PQR         250 3        C         31     KLM        250 3        C         32     TUV        250
I used formula Measures.Amount/Measures.Count, this formula is not showing correct results when I don't drag any dimensions, it is showing results for All member as 425, but it should show as 350.
So I made a same change ([L].[LK].Currentmember, Measures.Amount)/([L].[LK].Currentmember,Measures.Count), this worked fine but performance is very low and so stopped working on this.
Atlast I did the measure group like this
LK    OK     LAmount  OAmount 1       10        100       100 1        11        0           100 3         30       300       300 3         31       0           300
I want to show Measures.LAmount when only L dimension is querying and want to show OAmount when both L dimension and O dimension are querying. Is this possible ?
Is it possible to filter out a measure only at the intersection of Two dimension members? I have a date dimension, Â a Hospital dimension and a wait time measure.
For Example, is it possible to filter out Wait time for Bayside Hospital for the Month of June 2015?
I want Wait time to continue to be displayed for all other months and roll up into the totals without the filtered value.
I have a cube with 2 many-to-many dimensions where a special mdx query needs about 5 seconds. When I resolve the many to many relationships by multiplying the data in the fact table the query needs 21 seconds.
In general do many-to-many dimensions slow down query performance of a cube?
Without the many-to-many dimensions of course the fact table has much more rows. Could this be the reason for the performance loss?
how to tweak query performance of a cube in general?
I have a requirement to set Description values for our cube dimensions and attributes.Â
I've done this for regular dimensions but I cant seem to find a way for role playing dimensions. I can set the base dimension descriptions but not the 'clones'. Is this possible?Â
The all-level of dimensions doesn't show up in the PivotTable Field List? I have reports where I want to show one member of a dimensions compared to the total of the dimension (and not the total of the members shown). But I can't select the ALL-level. Is there any way to do this?
I have one dimension and one measure group. I deployed and processed the cube. Now I am able to browse the data. Now I added one more dimension. I deployed and reprocessed again the Cube. Now I am not able to see any values. Â I am getting like below.
I am using a "Client" dimension that includes a "Holding - Client" hierarchy. I have to make sure, that only the appropriate roles may access appropriate members from this dimension, but I only have the information which role may access which ClientID - I do not have the information which HoldingID should be accessible. Also, "Client" is used as the key column of the dimension with "ClientID" and "HoldingID" as key columns. The hierarchy is strict, no client may belong to multiple holdings.
I cannot seem to find the right MDX for the allowed member set. My MDX expression would need to look like this:
[Client].[Holding - Client].[Client].&[*]&[123]
In this example I want to give access for client &123, no matter the holding, so &1&123 and &2&123 would be allowed.
I am modelling two fact tables of Actuals and Budget which are at different granularity, Actuals are at day, customer and product sub category level. Budgets are at month, Region and Product category level.
Month, Region and Product Category is present in Date, Region and Product Category dimension respectively. I have only three dimensions as Customer, Product and Date. Linking those dimensions to Actual Fact table is not an issue, what is the best way and options are there to link budget fact table to those three dimensions.
I built my first tabular model and see that my fact tables are also appearing as dimensions. In Multi dimensional mode i could choose which are the dimensions. How do i do that in tabular model.
I am creating an SSIS Script Task that will be used to process SSAS dimensions and partitions and ideally log the details of each in a table. Any info on the benefits or drawbacks of using the built-in SSAS parallel processing as opposed to doing it manually in a multi-threaded "Parallel.Foreach" loop using the .NET AMO library.
In my testing, when I use a Parallel.foreach loop, I am able to obtain and log information about the object such as end time and time to process immediately after each object is processed. Â This allows me to keep a history of processing time for each object:
public void processDimensions(Server Server, Database Database, ProcessType processType) { Parallel.ForEach(Database.Dimensions.OfType<Microsoft.AnalysisServices.Dimension>(), d => { DateTime beginTime = DateTime.Now; try { d.Process(processType);
[code]....
If circumventing the built-in SSAS parallel processing is not best practice I'd like to know in advance before I go too far down that path.
I would like to create calculated measure which should give me closing holdings at all levels(week ,Month,Quarter and year levels.) whichever i pull in the browse pane.
Below calculated measure would show the holdings for whatever the level you have specified.
([Measures].[Holdings], ClosingPeriod( [DIM BI DATE].[Calendar].[WEEK], [DIM BI DATE].[Calendar].CurrentMember ))
But this measure shows the value for only week attribute.
for month,([Measures].[Holdings],ClosingPeriod( [DIM BI DATE].[Calendar].[Month], [DIM BI DATE].[Calendar].CurrentMember ))
But my aim is to create a calculated measure to give closing value for any level.
I have a monthly time period dimension representing average number of students for each month. At the yearly aggregate level I don't want it to sum up the avg number of students from every month because that number is incorrect. I would like it to use the number of students from the most recent month as a roll up. Is that possible to configure in SSAS?
calculating a rolling median over a period of 3 years.
I already calculate median and I've tried to calculate rolling median over a period of 3 years as below.
 MEDIAN([Date].[Year].CurrentMember.Lag(3):[Date].[Year].CurrentMember,[Measures].[median])
What this does is, it calculates the median of the medians over the period of 3 years. But, what I'm looking for is the overall median of the underlying measure over a period of 3 years.
What I have now:
Year1 - 41,52,73; Â Median1 - 52 Year2 - 6,9,12; Â Median2- 9 Year3 - 24,68,89; Median3 - 68 Overall Median of 9,52,68 - 52
What I need:
Year1 - 41,52,73; Â Median1 - 52 Year2 - 6,9,12; Â Median2- 9 Year3 - 24,68,89; Median3 - 68
Overall Median of 41,52,73,6,9,12,24,68,89 is 41Â
Which works fine for all my other calculations except this, where I need a distinct count. CNT is a calculated measure. The browser would look like this:
Category Year1 Year2 Â Â ..... MDX what I have now MDX what I need
I'm using a DW from Northwind database to build a cube to do some analitical taks. I already create the cube and now I am "cleaning" the dimensions. I'm having some difficults to understand the logical off this part. The reason is that When I create the Data Source View, I only import the Foreign Keys that connect the Dimensions to Fact_Table. I have to drag the attributes of Dimension from Data Source View to the tab attributes?Â
Imagine this:
I have the following dimension:
Dim_Customer: Customer_ID Name_Customer Job_Function Date_of_Birth Contact Address City Country
When I create the cube only Customer_ID appears in attributes tab, it's normal?Â
One more question:
I don't want to create a hierarchy like:
Customer ID -> Name_Customer Customer ID -> Date_of_Birth Customer ID -> Address Customer ID -> City Customer ID -> Country
My idea is to create the following hierarchy:Â
Name_Customer ->Â Date_of_Birth ->Â Â Address ->Â Â City ->Â Country
But the first hierarchy that I show is always appears to me. Do you know what is happens?
I'm using an olap cube in a report (dash board). The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that. Once the report is opened the users want to select another period. Then I want to put period as a parameter. But that is not possible since the parameter check box is no longer available when using a MDX filter expression.
Hi, I have noticed the the cubes that we have here use shared dimensions. For almost all cubes(5-6) there are at least 4-5 common dimensions. According to what I have been preached so far, the shared dimensions are so that you can reuse them. That is not what is practised here. for example. cube1 has somedim1, dim2_c1, dim3_c1... cube2 has xyzdim1_c2,xyzdim2_c2,dim3_c2..
dim3_c1 and dim3_c2 are the same dimensions, one for each cube. I don't know if I am missing something. Shouldn't the use the same dimensions? Could there be any reason for this. pls. advice.
I am new to SSIS and I am investigating using the Slowly Changing Dimension transform.
The data source that I receive is a daily snapshot of the external source system table. I need to store the history of the entity attributes (Type 2 SCD) and I am using the Start / End Date mechanism.
When an entity (identified by the business key) is no longer received in the source snapshot, I would like the data flow to update the End Date of the current row to show that the entity has now expired.
Does anyone have any suggestions for a good way to achieve this ?
NB: Changing the source system extract to include and flag expired entities is not an option for me.
This is my first task in attempting to populate a fact and dimension table from SSIS. I have a Fact table Sales and dimension tables Customer and Location. The data I am getting to fill this structure is in one file. where each record contains the sales information as well the customer information and location details on the same row.
I am using the SSIS to fill this structure by using the slowly changing dimension for the Customer dimension.
I am filling the customer dimension by using the slowly changing dimension. If I have 2 records having same BusinessKey but each with a different first name, where first name is set as a changing attribute, it is creating the customer twice in the table. But shouldn't it create one record with the most recent first name? or am I miss using the SCD.
I have another conceptual question, I am not sure which is the best way to fill my fact and dimension. Should I fill the customer and location dimensions first through 2 different loops on the data and then fill the fact table and map to corresponding dimensions? Or should I do like a for each loop on each record and for each record fill the dimensions and fact simultanously?
I have a question regarding a proble with two dimensions I built.
The first is named Account and contains approx 40k records. The second dimension is named Contact and contains the emps from the Account dim and contains approx 58k records. In the cube I also have two measures, One is a count of courses a Contact has taken. The second measure is a count of certifications a Contact may have earned.
The Account dim table has an AccountKey primary key and the Contact dim table has a ContactKey primary key and an AccountKey foreign key to the Account table. The key fields are not operational keys. They are surrogates. Both Contact and Account dim tables have as the first 10 or so records values that are used as parent groupings in the Cube dimension. For instance.
key = 1, name value = 'A-C'
Each proceeding value has the parent grouping's key value set as its parentkey.
The fact table contains both the AccountKeys and ContactKeys and an ItemId that corresponds to a specific course or certification. This itemid is used for the measures in the Cube
That's the background... here's my problem.
Using BIDS or Mgmt Studio, whenever I add the root dimAccount level (actual account names) as a row and then add the root Contact level (Contact names) as another row and drill down to a specifc Accounts contacts, everything locks up. I have only one measure in the data pane. The fact table only has about 20k records in it. I would think this should return data instantly. If I browse the cube with any other comination of dimensions besides the Contact and Account dimensions, the cube runs fine. It is just the combination of Account and Contact. I am getting really frustrated as I cannot figure this out.
I am rusty at SSAS so forgive me if I left out any pertinent info.
I am relatively new to SSIS/SSAS. I have searched the forums but cannot find an answer to my question.
I created a cube in SSAS and have deployed it. Now I am trying to use SSIS to populate the cube. I have setup a DS that points to the SSAS instance - it uses OLEDB Provider for Analysis services 9.0.
When I try to use a data flow task OLE DB source to truncate the dimension/cubes I do not see the DS in the list to select?
I am finding it hard to get into the SSIS way of organizing the processing.