Analysis :: Drillthrough With Measures From Two Measure Groups
Nov 4, 2014
I'm trying to show measures from 2 measures groups in a drillthrough. Obviously, it's not possible with a standard drillthrough action, but I still hope that I can somehow achieve this with the ASSP GetCustomDrillthroughMDX function.
Speaking in AdventureWorks2008R2 terms: Imagine I have a pivot table with Product Categories in filter (say, filtered on Gloves) and "Internet Sales Amount" as measure. From context menu in Excel I can call the drillthrough action which shows me the individual sales records. I would like to show in drillthrough additionally "End of Day Rate" measure from "Exchange Rates" measure group.
One option would be to join FactInternetSales with FactCurrencyRate and make EndOfDayRate a physical measure in the "Internet Sales" measure group. This is a pretty huge overhead for my scenario and I'd like to avoid this.
Another one would be to call something completely external for a drillthrough (for example, a SRRS Report).
we are having an existing cube in that we need to update with new measures . The Measure groups are added to the cube as linked object. so when we are updating the measure group it is throwing the exceptions as follows..“Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of 'SalesActual', cannot be verified against the source object.”
We have hidden few measure groups in cube for time being, where Users can browse the cube with Excel pivot. But, All these measures can be seen from Excel pivot in 'Show fields related to' drop down.
Need to remove the hidden measure groups from showing in Excel pivot and to remove 'All' option in 'Show Fields related to', So that users may not get confused by seeing all the measures. Can we achieve this.
We have a cube that has financial data in it. There are report actions on cells. I want them to be able to drill through the the supporting data stored in the fact table and all the measures I am showing are calculated measures.
Users connect to this cube from Excel 2007. If they use a "Filter" in Excel and do not select "All" or "One" of the members of the filter hierarchy, then the action dissapears. Microsoft explained this is because that cell then has a calculated cordinate and they can't determine the action to perform.
Has anyone experienced a need to do this? Can you provide me alternatives?
Thanks
[Edit]
The functionality I am looking for is not available in the BIDS either (Well it is to a point--if you select the first 2, 3, 4...members in the filter, the action will still appear. It only reads the first one however. If you select the members starting with any but the first, the action disappears).
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 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 have a cumulative measure in Tabular, and when I filter by a date in SQL it brings back all rows up to that date. However, when I drill down on any of the numbers, it only brings back rows related to the filtered date, and not the cumulative rows. Is there any way round this at all? Major obstacle for me at the moment...
I have set up a cube running under MSSQL Server 2000 Analysis services and have created reports in Excel 2002 using pivot tables and linking into the cube as an external data source.
The pivottable works fine and I can slice / dice as usual, the only thing that doesnt work is the drill through into AS. I receive and error message saying "Cannot show detail data for that selection...".
Now I know that the drill through works, as when I create a report using the Analsyis Services Excel Add Inthe drill through works fine, its just how do I get it working using the pivot tables ?
fyi - the reason I dont want to use the Analsyis Services Excel Add In for all the reports is because I have to deploy this to XX number of users, who wont have admin rights on their machines etc etc....
Is there any VB that I could use to perform this drill through and return the results.....or easier ??
I'm working on an SSAS 2012 OLAP cube with around 100 dimensions and a single measures group. When I try to run a simple DRILLTHROUGH query such as:
DRILLTHROUGH MAXROWS 1000 SELECT FROM [Cube] WHERE ([Measures].[Fact_Count])
I get an error as below.The specified query is too complex to be evaluated as a single statement. If I remove the majority of the dimensions from the cube I can run this query successfully. Looking at the limits set for analysis services I should be well within these.
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.
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 am pretty new to MDX and am having trouble getting what I need out of this MDX query. Some business rules:
Gross Amount applies to all clients, whether Type A or Type B. I always want to return Gross Amount.Some clients are Type A, some are Type B, some are both, and some are neither.There are Type A Net Amount and Type B Net Amount values for all clients, but I only want to display the Type A Net value if the client is a Type A client, only Type B if the client is a Type B, or both for both, and neither for neither. I would like to return blank/null, not $0.00, for those values that should not be displayed.
Here's the basic query.
SELECT { [Measures].[Gross Amount], [Measures].[Type A Net Amount], [Measures].[Type B Net Amount] } ON COLUMNS, NON EMPTY {[Dim Client].[Parent Client Code].[Parent Client Code] * [Dim Client].[Child Client Code].[Child Client Code] * [Dim Client].[Is Type A].CHILDREN * [Dim Client].[Is Type B].CHILDREN } ON ROWS FROM ClientInfo
I've 2 measures that I would like to swap according to which member in a dimension are selected by the user:Ex.
[Measure].[A] [Measure].[B]
Dimension called TEMP with 2 members (HOT and COLD).So if the user choose HOT it should show [Measure].[A] and if the user choose COLD it should show [Measure].[B]If the dimension is not included in the analysis by the user or if no dimension attribute are choosed then it should default to [Measure].[A].How to do that as Calculated Script in the SSAS cube ?
I'm having an issue running the clustering algorithm in the data mining view of Visual Studio. The databases connect properly and the data subsequently loads. However, upon clicking on the "Mining Model Viewer" tab, I receive the following error message:
Errors in the metadata manager. The D Msample ~MC cube has no measure groups. Errors in the metadata manager. An error occurred when loading the D Msample ~MC cube, from the file, '\?C:Program FilesMicrosoft SQL ServerMSSQL.2OLAPDataewDM_sample.0.dbD Msample ~MC.2.cub.xml'.
We are not using a data cube, so I am assuming that this file is being called through the clustering algorithm. Furthermore, I have run the same process on different systems successfully. The only difference I can detect is that this error resulted on a 64bit system.
I am trying to develop a cube with translated calculated measures in it. I have also translated the all underlying measures/dimensions being used in action for a header.
I can see my translated measures and dimension in excel but when I go to Underlying data, I see default member's names not the translated one. Please note that I have translated all underlying members.
I am new one in MDX. Our PeriodsToDate function does not return any value. We have set type property of our Date Dimension as time.Actually CURRENT MEMBER does not return a valid value. So our PeriodsToDate function fail.
With MEMBER [Measures].[YTD Actual] AS Aggregate ( PeriodsToDate ( [DimDate].[CalendarHierarchyDateLevel].[Calendar Year] ,[DimDate].[CalendarHierarchyDateLevel].CURRENTMEMBER )
large tabular cubes (SSAS 2012 SP2), with lots of calculations, the keyboard seems to be stuck, one cannot type or make any modifications to any measures, even after closing a project.
The only workaround is to completely restart the machine. Using Visual Studio 2010 Shell.
Are there any possibly corruptions or calc limitations that one should be aware of?
Notable that this never happens with any small cubes, but just with the largest cube that we have, that has probably tens of calcs.
I've created a cube and it processed fine. The calculate command is there. The measure that I'm attempting to run in the SSAS/Visual Studios browser is simply a count rows measure. When I drag the measure to the window, it says no rows available. If I click on the filter that allows nulls, the only change it makes is that it goes from no rows available to "NULL".
I have a master report that uses the "Jump to report" property to drill through to a detail report when the user clicks a report column value. This all works great and the users love it. However, when I click the brower's "Back" button to return to the master report, the master report is rerun and I am returned to page 1. If I drilled through on page 25 of the master report then I have lost my original position and I must navigate back to page 25. The users don't love this "feature"!
Is there a way I can return from the drill through detail report to the master report without using the browser's back button and losing my original position in the master report?
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.
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.
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
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.
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.
