Analysis :: How To Show Measures Dynamically For Different Dimensions

May 4, 2015

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 ?

View 3 Replies


ADVERTISEMENT

Analysis Services - Dimensions && Measures

Aug 13, 2004

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.

please help!

View 1 Replies View Related

Analysis :: Show Dimensions Like Columns In MDX

Jun 25, 2015

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

View 14 Replies View Related

Analysis :: How To Show All Level Of SSAS Dimensions In Excel

Oct 28, 2010

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 Related

Calculating Median Value From Measures And Dimensions

May 24, 2007

I am facing some problem in calculating Median

I am trying to calculate the median value using one of the measures and a dimension value.



Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:



opid time median

1 55

2 23

3 23

Total 23



The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.



What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.



I am using a calculated field Median with the following expression.



MEDIAN

( [Dim Operation].[Dim Operation].currentmember.children ,[Measures].[Elapsed Time])



Thanks

View 1 Replies View Related

All Measures Show No Values

May 29, 2008



I made a number of dimension and calculated member mods and notice that all now no base or calculated measures display values, they just show blanks in the browser and query window. To try to isolate, I've
(1) removed every dimension and calculated member
(2) verified there are no defaultmembers for dimensions
(3) verified there are no dimension attributes set to IsAggregatable = False

(4) verified there are no deploy or script errors
(5) fully processed every dm and fact
(6) verified there are dim and claim records loaded with values in all appropriate columns

When I process the cube, it shows that rows are read, etc... All that looks normal. Just when I browse or query, all measures display as blanks or nulls.

What could cause this behavior? Any ideas on how to futher isolate? I can go to a backup but very concerned that this can happen with no error messages or warnings, so I want to find the cause.

View 4 Replies View Related

Analysis :: Drilling Down On Cumulative Measures

Oct 1, 2015

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

View 5 Replies View Related

Analysis :: MDX Need To Filter Measures Based On A Dimension

Oct 26, 2015

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

Here's the DESIRED output........

View 3 Replies View Related

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

View 3 Replies View Related

String Measures In Analysis Services Cube

Feb 22, 2006

Hi,



I am having the requirement where I have to use string as measures. Is this possible in Analysis services 2K.

Any help on this will be greatly appreciated

View 23 Replies View Related

Analysis :: Swap Measures According To Dimension Selection

Oct 9, 2015

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 ?

View 3 Replies View Related

Analysis Services - Dimensions

Oct 23, 2002

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

Analysis :: Calculated Translated Measures - Underlying Data

Oct 7, 2015

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.

View 4 Replies View Related

Analysis :: PeriodsToDate Function Return Nothing In Calculated Measures

Aug 28, 2015

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
                )

[code]....

View 2 Replies View Related

Analysis :: Joining Multiple Dimensions In MDX

Jun 13, 2015

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?

View 4 Replies View Related

Analysis :: Large Tabular Cube With Lots Of Measures - Keyboard Stuck?

Apr 24, 2015

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.  

View 6 Replies View Related

Analysis :: How To Create Parallel Period For Multiple Measures In SSAS 2012

Mar 27, 2012

I am trying to create a calculated member for parallel period function using ssas 2012. I have 10 measures for which i need to create parallelperiod. 

I can successfully create for 1 measure but when i add multiple values to it it breaks. Below is the sample i tried for multiple measures:

sum(ParallelPeriod([Date].[Calendar].[year],1,[Date].[Calendar].currentmember) ,
([Measures].[Revenue],[Measures].[Expenses]))

View 10 Replies View Related

Analysis :: Filter Measure On Intersection Of Dimensions

Jul 24, 2015

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.

View 4 Replies View Related

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 ?

View 6 Replies View Related

Analysis :: Cube Performance Versus Many-to-Many Dimensions

Sep 22, 2015

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?

View 3 Replies View Related

Analysis Services: MDX - Date Ranges With 2 Time Dimensions

Aug 31, 2004

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

View 3 Replies View Related

Analysis :: Add Descriptions To Attributes In Role Playing Dimensions

Jul 28, 2015

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? 

View 2 Replies View Related

Analysis :: Ignore Unrelated Dimensions In Tabular Model

Jul 11, 2013

how to set 'Ignore Unrelated Dimensions' property in Tabular Model.

View 3 Replies View Related

Analysis :: Measures Showing No Rows In SSAS Browser After Cube Processed Ok And Calculate Command Is There

Jun 10, 2015

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

View 4 Replies View Related

Analysis :: Unable To Browse Data When Adding Multiple Dimensions?

Jul 5, 2015

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.

View 6 Replies View Related

Analysis :: How To Give Permission To Read Dimensions Members In Hierarchy

Jun 9, 2015

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.

Is this doable?

View 4 Replies View Related

Analysis :: Linking Multiple Fact Table At Different Granularity To Same Dimensions

Jul 15, 2015

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.

View 2 Replies View Related

Analysis :: Fact Tables Appearing As Dimensions In Tabular Model

Jul 6, 2015

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 Related

Power Pivot :: How To Convert Measures As Text To Actual Measures

Jul 5, 2015

I have an old model that unfortunately had to be re-establish. 

In order to save time, I thought that I can export all my measures and paste it as measures in my new model. 

I used the following technique to export the measures from the old file: [URL] ....

How to use the output and create the identical measures in my new model, without the need to manually write each one of them?

View 7 Replies View Related

Analysis :: Dimensions Attributes - Drag All Or Some Specific Attributes

May 24, 2015

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?

View 2 Replies View Related

Analysis :: Partitioning Sets Of Data Dynamically

Jul 17, 2015

I have a situation where i have a transactional fact table which consists of date, row type, order number and value.  Simple example below

Date, RowType, OrderNo, Value
01-May, New, A1, 100
01-Jun, Change, A1, -10
01-Jul, Invoiced, A1, -90

What I need to be able to do is somehow select based on a day, the total value of open orders.  I have tried to do this in the database but it becomes fixed and quite cumbersome (this is a simplified example in reality i have line information and line component information).I am not hugely skilled with MDX and SSAS but know there are some semi-additive functions i want somebody to be able to pick a day and have the total value of only open orders.

View 2 Replies View Related

Analysis Manager Can Not Show All Data

Jan 18, 2003

Hi,

I use MSsql server 2000 and analysis service for create cube.But when I drilldown many Dimension on Analysis manager it can not show all level.
If I drilldown some level it can show data but when I drilldown many level so it have many row (I test about 60,000) it can not show result.
How can I show all result in Analysis manager or other tool ?
Thank you :D

View 1 Replies View Related

Analysis :: Only Show Maximum Row In Row Grouping

Jun 15, 2015

I have a dataset that shows the client ID, visit date and number of positive visits performed.

For example

47 - 01/01/2015 1
47 - 01/05/2015 1

50 - 01/01/2015
1
...

I only want to show the row with the maximum visit date in my dataset. My current code looks like :

SELECT NON EMPTY
NonEmpty(
NonEmpty(
{[Point of sale].[Client id].[Client id]}
* [Point of sale].[POS id].[POS id]

[Code] ......

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved