SSAS 2008 R2
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 want to filter a measure based on another measure (both are measures on the same FACT table).Distinct Number of Users HAVING.User Cost above 0.
I tried doing having but because it’s two measures and not a tuple with a dimension it writes an eror.My query is something like:
SELECT
 [Measures].[Distinct Number of Users ]
having [Measures].[User Cost] >0
 ON
COLUMNS,
[code]....
Â
Please note, that I want it in a MDX query not needing to change the cube or DWH table.
I'm trying to create a percentile rank function based on the standard WIKI version:
I've seen Brian Knight's article here, but that only deals with percentile.
Where I'm struggling is getting the count of members in a set using a measure, in the current context on the same hierarchy, as the filter expression. I'm using the comparative set as in belonging to the same geographical location, and therefore associating by another attribute.
So, cl as below:
MEMBER [Measures].[RegionPercentileCount] AS
Count(
Filter(
NonEmpty(
descendants(Ancestor(
[Supplier].[NameMap].CurrentMember,
[Supplier].[NameMap].[Region]),
[Supplier].[NameMap].[Supplier Id]),
[Measures].[ActiveMeasure])
, [Measures].[ActiveMeasure] <
([Supplier].[NameMap].CurrentMember,
[Measures].[ActiveMeasure])))
Using the same measure and context hierarchy is always going to be equal, and therefore the count is always zero. Its almost as if I need a nested context for the FILTER which allows me to use enumerate the set on the same hierarchy whilst maintaining the external reference.Â
I'm thinking that perhaps I'm going to have to create another hierarchy and use that as the filter set and reference through StrToMember or similar.
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.Â
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
The user wants to be able, using excel, to apply a filter to all measures in every measure group. I though that I can create a dimension with a single level with two members, let´s say "on" and "off" and depending on the selected member and using an IIF statement decide which formula applies to the calculated measures.
I have serious doubts about the performance and for this technique because I am thinking as a .Net developer and not as a cube developer. Maybe it is better to resolve it scoping the measures but I cannot figure it out.
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.
HI all friends
I have a Fact table like this
DIMENSSIONSMEASURES
------------------------------
idempidprodcutsalessalesws
I just want make the next query with MDX
SELECT sales
FROM myFactTable
WHERE salesws > 0
Note that salesws is a measure not a dimmension
How it would be in MDX? I need a lot of help
Regards!
I have a delima.
I have a dataset that needs to return "Fatalities Involving drivers age 15-17". For years 2003 , 2004, 2005.
So i set up 3 different filters: Year : 2003,2004,2005
Person type: Driver
Age : 15,16,17
Now this is fine for getting a crash count and fatalities (for teen drivers). But i want the fatalities for everyone ( All Person Types) and (All Ages) ...how do i go about this. All i get right now, is the fatalities for Drivers 15-17.
Please help..im confused!
I am looking to filter the query using the free text predicate Contains for one of the column that have dimensions of the items. The query is something like
Select * from sampletable
where contains(filtercolumn, 'Near("*cm , *cm , *cm , *Kg.",10,True)')
Data in the filtercolumn is something like 64cm x 25cm x 35cm = 10Kg.
The query do not return any data.I also tried running the below query to see if cm* can be searcher for, but with no result again
Select * from sampletable
where contains(filtercolumn,, '"cm*"')
If I do something like below, it does return all columns that have 35cm within the text.
Select * from sampletable
where contains(filtercolumn,, '"35cm"')
We have a fact table where one of the measures in that fact table is invoice balance. Some of our reports requirements list that they only want to see the sum of those invoices where the balance is greater than X dollars. How do I go about filtering calculations based on a measures value?
View 3 Replies View RelatedIn Analysis Services is there a way you can change a private dimension into a shared dimension without having to recreate it
View 1 Replies View RelatedI 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.
please help!
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
[Code] .....
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 ?
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 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?
Any way to create a measure that filters the second column to mimic the behavior of only filtering for slicer for the Open Pool Date values as per the image below. Ultimately, I need to create a measure that only includes accounts that were opened 6 months prior to the month row context.
Trying to get the values in the second column to only include aging accounts 6 months prior instead of 12 months prior.
Trailing6Month Conversion:=CALCULATE([TOTAL LTD Converted Amount] ,Filter(Settlement700,Settlement700[OpenDatePool]>=RELATED(DimDate[Trailing6MonthsEnd])))
Trailing6Month LTD Conversion Amount:=CALCULATE([Trailing6Month Conversion],DATESINPERIOD(DimDate[FullDate],LASTDATE(DimDate[FullDate]),-6,Month))
Hi,
I have a Time Dimension which allows me to select a specific YEAR, or YEAR & QUARTER or YEAR & QUARTER & MONTH, or YEAR & QUARTER & MONTH & DAY.
Is there any way that I can have a range of dates?
Is it possible to have 2 time Dimensions for example which did the following:
a start: Year|Month for example
(>= Year|Month )
.......and......
an end: Year|Month
(<= Year|Month )
Thus I would be able to select a range of dates/months.
Do you know if this is possible to write this inot the dimension?
Thanks,
David
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?
View 5 Replies View Relatedhow to set 'Ignore Unrelated Dimensions' property in Tabular Model.
View 3 Replies View RelatedI 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.
View 6 Replies View RelatedI 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.
Is this doable?
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.
View 2 Replies View RelatedI am very new to MDX. I have the sales amount measure and date dimension.
I need two measure sales value for the below condition. I am going to display this result in SSRS.
1. For between from date and todate
2. For last week range
Any example on how to output more than one measure in an MDX query. Lets say I want to display stdev(x) and average(x) (or any other two measures)?
View 7 Replies View RelatedI am getting same value all over how to get unique values for each measures.
how to resolve it.
The data attached below is from a Fact table. When this data is browsed in the Cube the end user is only interested in value of Measure 1 when it is not equal to zero. Measure 1 is a base measure .how to suppress the value 0 for Measure 1 in the Cube.
DimesionKey1
DimensionKey2
DimensionKey3
Measure 1
Measure 2
Measure 3
[code]...
 I have a dimension called as DIM1 which has list of all measures and has an attribute called as ATTRMDX Formula. The formula will be like
([DIM1].[ATTR Measure Code].&[M1],[Measures].[ATTR MEASURE VALUE])+([DIM1].[ATTR Measure Code].&[M2],[Measures].[ATTR MEASURE VALUE]).
I want to pass this formula to a calculated measure as given below -
MEMBER [Measures].[FormMeasure] as ([Measure].[ATTRMDX Formula].currentmember.MEMBERVALUE)
but I get the string value itself as output, but when I put the formula as a string in the calculated measure I obtain the value.
I am trying to count a set as a calculated measure, when this set is called directly in the row , it returns fast, but when i try to count the set as calculated measure(so i can slice with another dimension) the query keeps running forever.
The queries are below
select
{} on 0,
nonempty
(
{([Transaction].[RPC Count].&[1],[Transaction].[Account ID].[Account ID])}
,
{([Account].[PAYMENTSTATUS].&[0],[Account].[Account ID].[Account ID])}
) Â on 1
[Code] .....
I have a wide fact table that I'm feeding to an SSAS cube. I was advised that splitting the measure group into two will improve performance when querying the cube.
I cannot find any documentation that supports this, in fact I get a blue curved line suggesting that I merge the measure groups since they have the same dimensionality and granularity.
I guess the best practice is what the blue line states, but without knowing the internals of SSAS I can undestand that a smaller measure group may be easier to handle, or create more specific aggregations for.