Analysis :: Calculation Based On Dimension Value

Oct 8, 2015

Need to resolve this calculation, which I would believe is something very common on SSAS environments. 

Like many companies, my company has different ways of calculating Sales and the two I want to focus are Sales Gross and Sales Net. 

At a high level, we calculate Sales Gross as Sales with returns, and Sales Net as Sales without returns.

We have an attribute called Order Type that has various types of orders a user can execute with my company. One of them is Returns. If you return something back to us, we record that as a return line on the sales table. With that, we can calculate that return, breaking data down by Order Type, such as:

Order Type Line Total

Mail Orders $ 776,655.44

Internet Orders $ 2,211,334.00

Call Center Orders $ 11,223,344.00

Credit Orders  $ (55,666.00)

Today, to calculate Sales Gross and Sales Net, we are creating two dimensions: DimSalesGross and DimSalesNet. 

To calculate Sales Gross, we leave the data at the natural state, not making any changes to mappings. 

To calculate Sales Net, we map Credit Orders to Call Center Orders at the ETL level, getting a Net value for sales (Orders - Returns), however, I doubt this is the correct way of doing. 

I would like to have a Line Total Net / Line Total Gross calculation, which would be based on the Order Type value.

Perhaps using a CASE statement in MDX? Is the above possible?

View 4 Replies


ADVERTISEMENT

Analysis :: Get Value Based On Max Of Date Dimension

Nov 18, 2015

I have a measure group product price, the measures are

1. Product Id
2. Price
3. Date

I have mapped the product id and date with product and date dimensions.I need to get the latest price of the product.For example:

Prod1, 100, 1/1/2015
Prod1, 300, 2/1/2015
Prod1, 250, 3/1/2015
Prod1, 150, 10/1/2015

So, i need the latest price. The latest price is 150.I new to MDX query. MDX query to achieve this?

View 3 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 :: How To Make Calculated Measure Behave Based On Dropped Dimension

Jun 25, 2015

i want to create a new measure that will behave based on the dimension dropped,ex. if i added the employee dimension only it will aggregate data from the #Calls Count but if i added the product dimension it should display # Product Calls at the product level and #Calls Count at the employee level as shown in the screen shot.

View 7 Replies View Related

Analysis :: Data Masking For Dimension Attribute Based On User In SSAS 2014 Multidimensional?

Jun 15, 2015

I am trying to implement data masking based on user login and not sure why this is not working. I have the dimensions DimBrand, DimProduct and DimUser. I should mask the BrandCode with 'XXXX' nothing but in the report all the BrandCode should appear but few of the code will be masked if the user is not belongs to that group. I have a fact table FactProduct in this. In the cube I created all these 3 dimensions and the fact table. I created a new dimension DimBrandMask and I separated the code over there with a relationship with the actual DimBrand dimension. In the cube a reference relationship is set up with the measure group. Created a role with read access.

In the dimension data tab of role I put the below MDX to allowed set.

NonEmpty([DimBrandMask].[Brand Code].Members, (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]") ,[Measures].[Dim User Count]))

And in Denied Member set i put the below MDX

IIF( (StrToMember("[DimUser].[Login Name].[Login Name].[" + UserName() + "]"), [DimUser].[Access Right].&[False]), NONEMPTY( [DimBrandMask].[Brand Code].Members,(StrToMember("[DimUser].[Login Name].[Login Name].["
+ UserName() + "]"), [DimUser].[Access Right].&[False], [Measures].[Dim User Count])),{})

Note I created one measure group from the DimUser table and the measure [Dim User Count] is used in the above query.

I am expecting some result like below

Brand      BrandCode           Count
Brand1      b1                       6
Brand2     XXXXX                  5
Brand3     XXXXX                 10

View 9 Replies View Related

Analysis :: Add Dimension To Cube Dimension Without Any Relation In Dimension Usage

Oct 26, 2015

When i add a dimension to the cube dimension without any relation in my dimension usage to any measure group my units are going down.However when i remove the dimension from the cube am getting the correct values.

View 4 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Analysis :: Hierarchy Based On Dimension Table Joined Multiple Times Against A Fact Table?

Aug 11, 2015

I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above).  When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer. 

Is there a way to build hierarchies within my Customer dimension based on the same Customer table?  What I want is to view the data in Excel and see the Customer dimension.  Within Customer, I want four hierarchies. 

View 2 Replies View Related

Calculation From Measure && Dimension, Please Help !!

Dec 16, 2005

Hello

I am a newbye with Analysis Services and am desperately trying to find a way to include a calculation between one of my measures (Teus), divided by the vessel capacity, where vessel is one of my dimensions (and is therefore not depended on other dimensions...)

Any ideas how I could implement that ?? This would help a lot, thanks for your help,

Aurore Bui.

View 2 Replies View Related

Do We Always Have To Create Name Calculation For Role-playing Dimension?

Jul 10, 2007

Hi, dear friends,

Just found that I am not able to ignore the name column property for role-palying dimension even though I only select one of the key columns for this dimension in the mining structure where the role-playing dimension is used? E.g I have a fact table (which is the case table for the mining structure) which is related to the role-playing date dimension. The schema is as following:

Dim_event_day_time_key

Dim_carrier_day_time_key

Dim_domain_day_time_key

.....................................

and other attibutes in the fact table.

When I dragged the 'hours' attribute from the dim_day_time table to the mining structure, if I left the key columns of 'Hours' as the above 3 columns, then I have to select a name column, by then I dont have a name column in the dimension table yet. Therefore I want to jsut select one key column as the key of that role-playing dimension in the mining structure, but then I am not able to ignore the 'name column' property as it still always asks me to select the 'name column' for that.



Therefore my question is: we are not allowed to select only one key columns in the mining structure for the role-playing dimension? And we will have to always go to the data source view to create a named calculation as the new column for the role-playing dimension?



Hope my question is clear for your advices and I am looking forward to hearing from you shortly.



Thanks a lot in advance.



With best regards,



Yours sincerely,

View 1 Replies View Related

Filtering Out 1 Time Dimension Based On The Selection In Another Time Dimension

May 19, 2008

Hi!

Need some help building a query that does the following :

I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)

In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]

Both Time Dimensions are Year -> Month -> Day and have the same Members.

I have every option available, using calculated Members and/or Measures to do this.

The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.

Appreciate all help!

Regards,
Stian Bakke

View 3 Replies View Related

Analysis :: Median Calculation Using MDX

May 28, 2015

 I have a fact table fct_line_details having two columns mtid, productivity

  mtid     productivity
  1           400
1           200
1          600
2          700
3         900

I want to calculate the median for each mtid in SSAS . (median for mtid 1=400 )

View 2 Replies View Related

Analysis :: Calculation On Row Level / If Value Is Above Or Less Than Average

Aug 3, 2015

I have a set of data and calculated already the average of a specific measure, incl. the Standarddeviation of this measure. Now I want to create a average measure of the values on row level which arehigher than [Avg]-[StdDev] AND lower of [Avg]+[StDev].As far as I understand I have to check on Rowlevel, which means on Date and Brand Level, if the value of the measure is above or lower than the Mean +/- StdDev.

SUM( FILTER( {[Dim 1].[Attribue].&[selected]},[Measures].[Fact Survey Count] > [Measures].[Mean + 0,5 StDev] AND [Measures].[Fact Survey Count] < [Measures].[Mean - 0,5 StDev] ),[Measures].[Fact Survey Count])

which leads to a wrong result. This one counts all rows if true.With Descendants I got a #Value-Error, probably because I did not understand how the calculations work. I planned to include this in a IIF calculation.

SUM( Descendants(
    {[Dim 1].[Attribute].&[selected],
    [Dim Date].[Date].[Date].CURRENTMEMBER}, Leaves), [Measures].[Fact Survey Count])

View 4 Replies View Related

Analysis :: Calculation That Excludes Filters

Jul 8, 2015

I'm trying to calculate the average number of sick days per person so that it can be broken down by person role or department or some other dimension. I have a calculation for sick days that works ok and is [measures].[sick days.I'm trying to create another calculation that is [total staff] but it doesn't work.

My final calculation will be [avg sick days] =  [measures].[sick days] / [measures].[total staff]

The bit I can't get to work is making it ignore any filters. For example I have a measure called [staff] and as you would expect it can be broken down by [person].[role] or [department].[department].

I'm trying to get [total staff] to return the total when it's used with [person] or [department]. I've managed to get it to work with [person] by using ROOT([Person]) but then I will need to do this for all different hierarchies that will use it.

View 12 Replies View Related

Mdx/analysis/reporting Service For Date Calculation

Apr 8, 2008

Hi,

I have a cube with the following
DimStartDate
DimEndDate
DimArea
FactDataCount

I need to output the following report

Area 5days 10days 30days
Miami 4 2 1
Memphis 3 3 2

1)Where do I do the dateDiff calculation, at report or cube level?
2)How do I work out which dates belong to the above groups? I'm assuming i have to check if the dateDiff lies between those numbers?

thanks

View 2 Replies View Related

Analysis :: Display Calculation For Structure And Attributes Only

Jun 29, 2015

I have a calculation that granularity is on a specific level and therefore would like the calculation to be only visible/calculate when with a specific dimension structure & attributes

SCOPE([Measures].[Complaint Rate]);
SCOPE([Item Dimension].[Item Structure].Members);
this=(IIF([Measures].[Sales Units]=0,NULL,(([Measures].[Count]/[Measures].[Sales Units])*1000000)));
END SCOPE;
END SCOPE;

This displays the calculation all the time even if no item attributes are selected, I only want the calculation applicable to the structure & attributes belonging to structure - I tried children, currentmember etc. but no luck - its probably something small I am missing. Tried this too

this=(IIF([Measures].[Sales Units]=0,NULL,(([Item Dimension].[Item Structure].CURRENTMEMBER,[Measures].[Count]/[Item Dimension].[Item Structure].CURRENTMEMBER,[Measures].[Sales Units])*1000000)));

View 4 Replies View Related

Analysis :: Edit Dimension DSV

Jun 10, 2015

I am unable to see my DSV when I try to edit my dimension. I have added some new fields in my dimension table and now want to use them in my cube dimension.

View 5 Replies View Related

Analysis :: How To Get Dimension Key Attribute In MDX

Apr 30, 2015

Many dimensions don't have unique members.  Instead, the dimension source data has duplicates at the leaf level: it's left up to SSAS to aggregate up to the actual leaf level used in hierarchies.

Every cube I've worked on in the past, a dimension is clearly defined in the source data, with uniqueness already present there: we don't make a dimension out of duplicated, sort of facty data.  This kind of design seems as weird to me as an unnormalised SQL database.

Here's an example to illustrate what I mean; I'll use that Adventureworks database.

We have a Geography dimension with a Geography hierarchy.  Levels go like this from top to bottom:

Country
State-Province
City
Postcode

The Geography dimension has a key attribute called Geography Key.  It's there in the cube design as a dimension attribute, but it's not in any of the hierarchies, so I can't query it in MDX.  But that's fine: it has the same cardinality as the lowest level (Postal Code), because the dimension has some kind of normal design.

In the cube I'm dealing with, it's all messed up.  Using the AdventureWorks example above as a parallel, someone made a Geography dimension with source data keyed on [PostalCode, ExactAddress], but only wanted the dimension granularity to be PostalCode.

This makes it very hard to debug why the data in this dimension is incorrect.  I can't match up the dimension members in the cube to the source data, because the dimension doesn't actually go down to the real leaf level!

So I have a dimension attribute called ExactAddressKey, but I can't query on it in MDX, because it's not part of any dimension hierarchy.  Unfortunately changing any part of this cube design is not possible, so I can't even experiment with settings and see what happens.

How I could get to the leaf level of the data imported?  Something like

Geography.Geography.TheInvisibleLeafLevel.Members.Properties('Key')

Or does this kind of dimension design result in SSAS discarding all the data that's more granular than the most granular attribute defined in any hierarchy - so that the data actually isn't there to be queried?

View 2 Replies View Related

Analysis :: Total Calculation With Filter For Parallel Period

Nov 30, 2015

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.

View 4 Replies View Related

Analysis :: SSAS Tabular - Preview Of Calculation Not Working

Oct 30, 2015

Does any1 know why preview of a DAX calculation does not work? I mean once you enter the calculation in the bottom of the table it doesnt´t show up.

View 8 Replies View Related

Analysis :: YTD Calculation For Multiple Hierarchies With SCOPE Statement

Jul 2, 2015

I am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure

If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.

SCOPE([Billing Date].[SalesCalendar].MEMBERS);
( [Aggregate].[AGGREGATE CODE].[YTD] )   
          = Aggregate({[Aggregate].[AGGREGATE CODE].DefaultMember}*{PERIODSTODATE([Billing Date].[SalesCalendar].[Sales Calendar Year],[Billing Date].[SalesCalendar].CURRENTMEMBER) } );

[Code] ....

However if I comment on one of SCOPE statement , other one works but both don't work simultaneously in context of different calendars.

View 2 Replies View Related

Analysis :: Total Calculation Incorrect When Using IF In Calculated Member

Oct 27, 2015

I cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?

Calculated Member:
     CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
        AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
     VISIBLE = 1;

It seems that Total is calculated without checking value in "AverageScore" for that month.

View 3 Replies View Related

Analysis Services - Dimension Problem

Dec 24, 2002

Trying to create a time dimension off of a large table. When I go with the default "start year at Jan. 1", everything goes fine. When I switch that to July 1, however, (drat those fiscal years, anyway), the construction of the dimension times out. Any suggestions?

View 5 Replies View Related

Analysis Services Dimension Question

Apr 16, 2004

I have a database with millions of users with email addresses. I want to create an email domain dimension that groups domains into all the big email domains (hotmail, aol, yahoo) and an "other" consisting of all the other domains. I can create a table with entries for the big domains, and get the grouping working but anything that is not a big domain will get thrown out rather than put into an "other" category.

Any idea how to get such an "other" category?

View 1 Replies View Related

Analysis :: Can Use CurrentMember In Dimension Security

Aug 19, 2015

I am trying to define the security based on Current Member.

Can i put following statement in the DimensionData - Allowed Members Set

EXISTS([Dim M].[Emp No].MEMBERS, [Security EMP].[Role].CurrentMember, "Uvw Security Emp ")

So that the list of EMP Nos that are visible changes when a different Role is selected (Filtered).

If not then is there any other way of achieving the same? What I want is that the list of EMP No that are visible should change when the user filters it using "Role" attribute in the "Uvw Security Emp" dimension. 

If I add the Role attribute in the Dim M Dimension I think it will unnecessarily calculate aggregations, etc.

View 2 Replies View Related

Analysis :: MDX For And Operator In 2 Date Dimension

Jun 4, 2015

I am in a situation to slice a fact in two date dimension .. my <g class="gr_ gr_73 gr-alert gr_spell ContextualSpelling ins-del multiReplace" data-gr-id="73" id="73">sql</g> code is like this 

select count(1) from fact_table  where end_date >=(selecteddate on filter) and startdate <=end_date .

The common date dimension is Start date. and the filter will be only on <g class="gr_ gr_168 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="168" id="168">end</g> date.

Below is the code so far

<g class="gr_ gr_228 gr-alert gr_tiny gr_spell ContextualSpelling multiReplace" data-gr-id="228" id="228">i</g> did, but <g class="gr_ gr_229 gr-alert gr_tiny gr_spell ContextualSpelling
multiReplace" data-gr-id="229" id="229">i</g> am not finding how to filter the start date <=end_date

   with member  [Measures].[X] as 

 Sum(
{[EndDate].[YQMWD].currentmember:NULL}, 
([Measures].[Count])) 
 select [Measures].[X]  on 0 ,
 [EndDate].[YQMWD].year.members on 1
  from Cube

What is the trick I can put so that I can filter those data where start date <=selected end date ....

View 3 Replies View Related

Analysis :: MDX Display All Dimension Records

Jun 15, 2015

If I have a dimension with 3 records like this:

BrokerDimId  BrokerNm
1  Broker1
2  Broker2
3  Broker3

And I have a fact table that has 2 rows that join back to the first 2 dimension rows like this:

BrokerDimId  Amt
1  $100
2  $200

I want to create the mdx so that I get all of the dimension rows, even if there is no record in the fact table for it and just default the amount to 0, like this

BrokerDimId  Amt
1  $100
2  $200
3  $0

Is this possible using MDX?

View 2 Replies View Related

Analysis :: Dimension Errors In Dropdown

Sep 1, 2015

I am getting the dropdown of the dimension folder twice, as i took printshot from client tool. In SSMS/bids also its showing this error. How to resolve it.

View 9 Replies View Related

Analysis :: Cannot Process Calendar Dimension

May 4, 2015

I'm building a cube for sales team , to test out I'm trying to process just one dimension called DimCalandar , when I try to process this dimension I get the following error ,

'Either the user abc/def, does not have access to database, or the database does not exist' ...

View 2 Replies View Related

Analysis :: Dimension Error In SSMS

Sep 2, 2015

I AM GETTING THIS ERRORS, and even when i drag and drop calculated measures, it gives error as" query error" with RED mark..

View 4 Replies View Related

Analysis :: Change Dimension Name Attribute?

Sep 8, 2015

I have 2 dimensions that pull their Facility Name from the same Location Dimension.  The business users want to change Facility Name in the Material Facilities dimension to “Material Facility Name”, but keep Facilities dimension attribute the same. What is a good way to go about completing this task.  

View 2 Replies View Related

Analysis :: Querying Dimension Members In MDX?

Sep 17, 2015

The Member_Key we can get by the method suggested in the post, but when we get the Member_Key, Id as Measures/members then performance is very slow.

Is there any way i can get the Member_key in a set in rows section.

Like

With SET [UniqueName] AS [Geography].[Country].CURRENTMEMBER.UNIQUENAME
Select Measures.Value on Columns,
UniqueName on Rows
From Cube

I want the value as just like

Value
Employees, UniqueName

View 2 Replies View Related

Analysis :: Dimension Processing Error

Mar 14, 2013

I'm getting this error during processing one dimension.OLE DB error: OLE DB or ODBC error: SQL Server blocked access to STATEMENT 'OpenRowset/ OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.; 42000.my dimension contains member from two datasource table.

and in another dimension i get error;Errors in the high-level relational engine. The 'dbo_vicidial_Users' table that is required for a join cannot be reached based on the relationships in the data source view.Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Staging User', Name of 'DimUsers' was being processed.

View 4 Replies View Related







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