Analysis :: Calculate Members At Certain Level In One Hierarchy As Sum Of Corresponding Members At A Certain Level

Jun 4, 2015

Problem setting is a geography dimension with multiple user defined hierarchies in SSAS 2008. 

Ex.: 
Hierarchy 1 (political territory): level 6 --> level 5 --> level 4 --> level 3 (state) --> level 2 --> level 1
Hierarchy 2 (sales territory): level 4 --> level 3 --> level 2 (sales region) --> level 1
...
Hierarchy 9

The relationship between state and sales region is n:1, i.e. one state belongs to exactly one sales region, and one sales region can consists of one or multiple states.  Unfortunatly I can't define this attribute relationsship in the dimension because it would lead to a diamond-shaped relationsship without a user-defined-hierarchy to back it up. So far that isn't much of a problem, user don't drill down from sales region to state. But now I want to define a calculated member that multiplies a measure from the main measure group with another measure from a weighting factor measure group at the state level and above. The granularity attribute of the geography dimension in the dimension usage tab of the weighting factor measuregroup is the state. 

So far what I've got is:

CREATE MEMBER Currentcube.Measures.[weighted measure state and above] AS NULL;
SCOPE (Measures.[weighted measure state and above],
Descendants(geography.[political territory].[all member],3,SELF_AND_BEFORE),
Descendants(geography.[salesterritory].[all member],2,SELF_AND_BEFORE),
... Descendants(geography.[hierarchy 9].[all member],1,SELF_AND_BEFORE)); this = sum(existing(geography.[political territory].state.members), measures.[main measure group measure] * measures.[weighting measure group measure]);END SCOPE;

This works from a functional point of view, but is rather slow when querying any other hierarchy than the political territory hierarchy, because SSAS first goes down from the state level to the key attribute of the geography dimension, and then aggregates from there to the sales region.In other words, I want SSAS to resolve the relationsship (which state belongs to which sales region) through the dimension, and not through the fact, and apply the calculation afterwards. Like some kind of currency conversion, but only from a certain level upwards.

View 5 Replies


ADVERTISEMENT

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

SQL 2012 :: Load Consolidated Members Into MDS Staging Table - Hierarchy Name

May 28, 2015

I'm attempting to load some data into an explicit hierarchy in MDS 2012 via the staging table and struggling with the HierarchyName field. Specifically I'm loading data into stg.[Entity Name]_Consolidated and using the exact name of the explicit hierarchy I've set up in the front end web application.

Originally my hierarchy was labelled "Reporting Hierarchy" and when loading the data into staging using this name then running the batch from the Import Data screen I can see the error message "Error - The HierarchyName is missing or is not valid.". I've checked the table mdm.tblHierarchy and can see that the name there is exactly as it was in the staging table and have since renamed the hierarchy as "Reporting_Hierarchy" with the same results.

View 0 Replies View Related

Analysis :: How To Insert New Members In Quantity

Jul 10, 2015

Enterprise 2014 SQL Server - SSAS

I have a need to  insert 50 - 100 new members each week. 

Is there an SSIS procedure that will do this? 

View 3 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 :: Find Date Ranges On Members And SUM

Jul 24, 2015

I've got two measure groups with a dimension (Dimension A) that is related to one measure group but not the other. I want to find the date ranges of the members of an attribute in Dimension A in Measure Group 1 and apply that to Measure Group 2 in a calculation. This way I can find the sum of a measure from Measure Group A that falls within the time periods of the attributes in Measure Group B. Part of my MDX for this new calculated member starts like this, but doesn't work.

WITH MEMBER [Measures].[New Measure] AS 
IIF( ISEMPTY ([Measures].[Measure 1]), NULL,
SUM(([Date].[Hour].[Hour], [Dimension A].[Attribute].[Attribute]), [Measures].[Measure 2]))

View 3 Replies View Related

Analysis :: Exclude Members From A Scope - MDX SSAS?

Sep 27, 2013

I'm trying to do a currency conversion with an MDX statement in my Cube SSAS 2012.

Here is my script :

       SCOPE( LEAVES([Entity]) );
SCOPE( LEAVES([Time]) );
SCOPE( LEAVES([Currency]));
SCOPE( [Account].[Account].[Total ACCOUNT].members)
THIS = ([Measures].[Value],[Currency].[Currency].[Local])*([Measures].[Value],[Account].[Account].[Fx Rate]);
END SCOPE;
END SCOPE;
END SCOPE;
END SCOPE;

The problem is I want to exclude frome the scope Currency my local Currency in order to make the conversion only if a currency (€,$, £) is selected. I tried the following syntax but it always return a "MDX script is not valid" :

SCOPE( LEAVES([Currency], Except (LEAVES([Currency],[Currency].[Currency].[Local]);SCOPE( [Measures].[Value] , Except ([Currency].[Currency].members,[Currency].[Currency].[Local]);SCOPE( [Measures].[Value] , Except (LEAVES([Currency],[Currency].[Currency].[Local]);SCOPE( LEAVES([Currency] - [Currency].[Currency].[Local]);

View 5 Replies View Related

Analysis :: Sorting Dimension Members From Fact

Dec 2, 2015

I've got a dimension built from a fact (whatever that's called?) ... it's a date interval field, i.e. 0-5 weeks, 6-10 weeks 11+ weeks. How do I sort these members in the respective order? Looks like this currently:

The problem lies in the fact that I don't have any secondary attributes to order it by, i.e. it's not a physical dimension where I can use a key for the 3 members. I was hoping I wouldn't need to create a separate dimension to get round this.

View 5 Replies View Related

Analysis :: Limit Date Dimension Members Dynamically?

Jul 7, 2015

We have a date dimension which spans till 2099 and there are future projection numbers (under measures). I want to limit the data for Future projections only to 5 years from today by default. Is there a way to do this with in the cube. I understand that this can be done using MDX but since we use excel to view the data from the cube it needs to be controlled with in the cube.

View 4 Replies View Related

Analysis :: SSAS Calculated Members For Each Week Of Month

Jul 6, 2015

I have a date dimension with structure/hierarchy (year - month - wkofmonth - day) & measure count

This works fine when I insert wkofmonth in columns & count in values but when I add another measure it duplicates per week

Product CountWk1(jan) CountWk2(jan) CountWk3(jan) CountWk4(jan) SalesVolume
1            1                        2                  0                         0               1000

Is there something I can do on the structure & do 4 calculated members pending how many weeks in a month, is there a simpler way where the next measure does not calculate by columns field.

If calculated member for each week, how do I do this, any example code?

View 2 Replies View Related

Analysis :: Statistical Function Results In Calculated Members

Jul 8, 2015

I'm working with the statistical functions Stdev and Median with calculated members.  The only way I can get the "correct" answer is if I have a dimension at the same granularity as the Fact table (Actually it's a degenerate dimension of the FACT table itself).  Otherwise it seems that the measure I'm using with Stdev returns results that are so wildly high, I think it must be acting on the SUM of the measure; because the measure itself is a Summed one. When I try to use the coordinates in the Stdev function, it seems like it is using the wrong set of data points :

stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members ), [Measures].[Value])  returns answers in the thousands when it should be more like 2.5

When used with a query, there would only be a single date member and a specific parameter member.  The total number of fact records is between 200 and 500 with values that range between 0 and 150.  This is the version that gives me answers that resemble the total sum of the [Measures].[Value].

If I add the dimension that is essentially a row number from the fact table, it gives the right answer (slowly, but that will be a different post ....

stdev( ( [Date].[Date].[Date].members, [Parameter].[Parameter].[Parameter].members, [FACTTable].[FACTTable].[KeyField].members ), [Measures].[Value])

View 4 Replies View Related

Caching (storing) Calculated Members In Analysis Services 2005

Mar 2, 2006

Do anybody know if Analysis Services 2005 allows cashing (storing) calculated members ?

There is a cube in Analysis Services 2000 with a calculated member based on some running sums. The performance of MDXs is too slow because running summs are being recalculated for each member of time dimension on each call.

I think that caching calculated members might help me and hope that this feature exists in Analysis Services 2005 .

Thanks!

View 1 Replies View Related

Analysis :: Attribute Relationships Impacts On Members Full Unique Name

Aug 17, 2015

Our SSAS integration didn't initially use attribute relationships.Now that our system has been running for a few years and we have bigger databases, we think we need to add them to improve performance. So we're in the process of adding them but we found out that, when attribute relationships are added, the full unique name of our members all go from something like:

[DIM].[HIERARCHY].[LEVEL].&[GRANDPARENT].&[PARENT].&[MEMBER]
to something like:
[DIM].[HIERARCHY].[LEVEL].&[MEMBER]

It looks nice and SSAS will accept the longer names fine but it will return the short ones in response to 'discovery' requests and in the XMLA response of MDX queries. This is causing problems in our low level XMLA-based modules that assume the long names in and out. is there any clean way to use attribute relationships and still have SSAS generate the long member names. We fiddled with the various documented dim/attribute properties but to no avail. It also appears that some switches are obsolete.

View 6 Replies View Related

Analysis :: Setup Multiple Default Members For A Dimension Attribute In SSAS?

Jun 2, 2015

I have a dimension report with an attribute reporttype which have different member let say A,B,C,D....etc.

I want to set more than one default members for dimension report.

I've read all about how dimensions can have only one default member, but I need to set more than 1.

View 2 Replies View Related

Hierarchy Level

Apr 28, 2008

Hi

i tried on CTE but iam not gettng correct result plz help me


Declare @TMaster Table (MasterId int, Type varchar(100), ParentMasterId int)
Insert into @Tmaster
Select 1, 'Fixed', 14 Union All
Select 2, 'Flexible',14 union All
Select 3, 'Others',14 union All
Select 13, 'Retirement benefits',1 union All
Select 14, 'PBHeads', 0

Select * From @Tmaster

Declare @TDetails Table ( MasterId Int, Description varchar(100))
Insert into @TDetails
Select 1, 'Basic' union all
Select 1,'Conveyance' union all
Select 1,'HRA' union all
Select 1,'Special Allowance' union all
Select 2, 'Children Education' union all
Select 2,'Travel Allowance' union all
Select 2,'Medical Expenses' union all
Select 2,'Variable Spl Pay' union all
Select 3, 'Project Allowance' union all
Select 3,'Quarterly Incentive' union all
Select 3,'Shift Allowance' union all
Select 3,'Annual Component' union all
Select 13,'Provident Fund' union all
Select 13,'Gratuity' union all
Select 13,'Super annuation'


Select * From @TDetails

in group list we have to show fixed, flexible, other
in subgroups(L1) list under fixed we have to show
Basic salary, HRA, Conveyance, Special Allowance,Retirement benefits

in subgroup(L2) we have to show Gratuity, providedfund, Super annuation they are subgroups of retirementbenefits


output :
Group Subgroup(L1) Subgroup(L2)
Fixed
Basic salary
HRA
Conveyance
Special Allowance
Retirement benefits
Provident Fund
Gratuity
Super annuation

Flexible reimbursements
LTA/Group Travel
Medical expenses
Children education
variable special pay

Others
Project allowance
Performance incentive
Medical insurance

View 12 Replies View Related

Analysis :: Calculate Max Value Over A Hierarchy?

Jun 19, 2015

I have a fact table that captures the captures oldest date between a Task date and todays date per Person (so there is only fact per person) like this - a measure is created from the Task Days

PersonID, Dateduekey, Task Days
130          20130809     679

I have a person dimension which has a hierarchy of Department_Name->Team_Name->Person I have created some MDX that gives the MAX Task Day for the hierarchy but when you Person attribute of the hierarchy the code runs really slow.with set a as nonempty(([v Dim Fee Earners].[People Structure].[Person],[Dim Calendar].[Primary Date].[All]),[Measures].[Days KPI]) member measures.a as max(existing(a),[Measures].[Days KPI])

How can I get this running for more efficiently when [v Dim Fee Earners].[People Structure].[Person] is selected? To be fair the true measure [Days KPI] is already calculated at person level so if there is way to use the [Days KPI] when [v Dim Fee Earners].[People Structure].[Person] is selected that would be good

View 6 Replies View Related

Calculating Average By Hierarchy Level

Jul 5, 2007

Hi all,

I have a problem which needs to be sorted out immediate in Analysis service Cube. My requirement is as follows

The following data explains the average value of each employee in corresponding level.

Level1 - > E1 – (25hrs /25days) =1 hrs/day
Level2 ---------- >E2 – (125hrs /25days) = 5 hrs/day
Level3 ------------------------ >E4 – (150hrs /25days) = 6hrs/day
Level4 --------------------------------------- > E6 – (100hrs /25days) = 4hrs/day
Level4 --------------------------------------- > E7 – (75hrs/25days) = 3hrs/day
Level4 --------------------------------------- > E8 – (175hrs/25days) = 7hrs/day
Level3 ------------------------ >E5 – (75hrs/25days) = 3hrs/day
Level2 ---------- >E3 – (100hrs /25days) = 4hrs/day

Eg:
I have productivity records of each day and each employee. I need to calculate avg of each last level employee productivity by monthly. Again Last level employee productivity avg must be added up with their immediate head. But, when I define a Measure Item as avg in the cube, it sums all the values of lost level employees & head and divides with number of records (normal avg).

My requirement is calculating each head avg by sum of each last level employee avgs / no of employees. If head having value, he too will be added. Again Head’s Avg will be added up immediate head.


The following calculation gives average value at each level.

Average of Level 3(E4) = > (4+3+7+6)/4 = 5 hrs/day< = (E6+E7+E8+E4)/4

Average of Level 2(E2) = > (5+3+5)/3= 4.333 < =avg(Level3(E4))+avg(Level3(E5)))/2

Average of Level 1(E1) = > (4.333+4+1)/3 = 3.111
< = avg(Level3(E2))+avg(Level3(E3)))/2


Formula for average of level :
: (Sum of Children value + Head Value of Corresponding children) / (No.of Children +1)

I want to calculate average of each employee as well as average of each level in cube (SQL Server Analysis Services).

Thanks in advance
Thiru

View 1 Replies View Related

Reporting Services - Analysis Services - Displaying Dimension Members As Columns

Dec 3, 2006

I think I've seen a similar post on a blog or on the forums - but it seems like this should be possible -

I have an MDX query - that works fine in SQL Enterprise Manager, and has my dimension members on columns, and my measures on the rows. When I try the same query in Reporting Services, I get the error:

"The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)"

Although it works when you pivot the view, I really need my data presented with the members on the columns and the measures on the rows. Another forum post mentioned using the SQL 9.0 driver, but I can't see this listed anywhere (the only one I see is the .NET framework Data Provider for Microsoft Analysis Services).

Here's what my query looks like -

SELECT
{ [Time].[Month].&[2006-09-01T00:00:00] ,
[Time].[Month].&[2006-10-01T00:00:00],
[Time].[Month].&[2006-11-01T00:00:00],
[Time].[Month].&[2006-12-01T00:00:00]
} on COLUMNS,
{
[Measures].[Unique Users],
[Measures].[UU Pct 1],
[Measures].[UU Pct 2],
} ON ROWS
FROM [Cube]



Any ideas?

Thanks,
Arjun

View 8 Replies View Related

SQL Server 2012 :: Finding Lowest Level Descendants In Hierarchy

Mar 11, 2015

I've got a fairly large hierarchy table and I'm trying to put together a query to find the lowest level descendants of the hierarchy. I think there must be some way to use the "Breadth-first" approach that's stated in the MSDN technet sites about SQL Server HierarchyID but i'm not sure how to write the necessary T-SQL to traverse that. I know I can get all the descendants of a parent node like this

SELECT *
FROM AdventureWorks2012.HumanResources.Employee
WHERE OrganizationNode.IsDescendantOf(@ParentNode) = 1

However, this query returns all levels for that parent's branch. If I just wanted list of employees that were at the lowest level of the branch(es) for this parent node, how would I do this?

View 1 Replies View Related

Column Level Or Database Level Encryption/decryption....

Jan 16, 2008

I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256  algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance

View 1 Replies View Related

High Level &&amp; Detail Level Design Documents

Nov 19, 2007



Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance

View 1 Replies View Related

Analysis :: DAX Row Level Multiply First Then Sum Up In Total

Jul 30, 2015

I've got measure a in one table and measure b in another table and calculation a*b.

            a  b   calc

            2  4   8

            5  2   10

SUM     7   6   18

How to get 18 as total instead of 7*6=42. I'd like to get row level calculation to sum up for the total level...

View 3 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 :: How To Get Max Level Member Of A Dimension Without Mentioning

May 29, 2015

I am Using following MDX Query to get the data for max date in the Dimension. But every time I am passing date key to dimension to get data. How to automatically get the Max available Level member for that dimension.In this case I am passing 20150429 to every line instead i need to get that automatically.

SELECT 
(
{
[DimDate].[HierYQMD].[Date Key].&[20150429],
{[DimDate].[HierYQMD].[Date Key].&[20150429].PARENT.PARENT.PARENT.LAG(4):
[DimDate].[HierYQMD].[Date Key].&[20150429].PARENT.PARENT.PARENT},

[code]....

View 4 Replies View Related

Analysis :: DAX - How To Simulate MDX SCOPE Forcing YEAR Level

Jul 19, 2015

In my fact table (month granularity), I've got a calculation defined: 

Margin%=DIVIDE(SUM(Margin);SUM(SalesAmount))

How should I define a calculation based on "Margin%", so that if I browse the Pivot Table by months it would always show the year level result instead? My date dimension is also with Month granularity and it has a Year column in it. 

View 2 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

Analysis :: Average Calculation On Leaf And Aggregate Parent Level And Above Using Averaged Value?

Sep 14, 2015

At the 2nd level, the calculated measure will only be correct if it is Averaged. And at the 1st level, the calculated measure will only be correct to take these Average value from 2nd level and Sum then up together. 

Level 1 Level 2
A    
X1
X2
=================
(Avg of X1 + X2)  
B
Y1
Y2
Y3
=================
(Avg of Y1 + Y2 + Y3)
===================================================
(Sum of (Avg of X1 + X2)  + (Avg of Y1 + Y2 + Y3) )

Currently, Instead of summing all the 3 averaged value, it is averaging against all the items like,

(Avg of X1 + X2 + Y1 + Y2 + Y3)

My MDX currently looks something like that 

[Measure].[Value] / [Dimension].[Attribute.count] -> so i can get the avarage at the 2nd level but it doesn't require on the 1st level but retaining this value.

How can i do an average on the leaf level and using these average values to sum at parent level?

View 4 Replies View Related

Transactions = Table Level Or Row Level ?

Nov 29, 2006

When you utilize transactions in ADO.NET are the locks put on the entire TABLE used or at the row level?
For instance if you do a SELECT within a transaction if you only pull 5 rows out of a 1000 row table can you just make it lock the rows that have been pulled?  It seems like it locks the entire table?
 
Thanks

View 6 Replies View Related

What Is Low Level And High Level Locking

May 3, 2007

Hi, Can anybody please explain me, what is low level and high level locking in SQL Server 2005 database.
Also what is the name of process which converts low level locking into high level locking and vise versa.
-Sanjeev

View 2 Replies View Related

Statement Level Vs. Row Level Triggers

Jul 20, 2005

Hi..I'd very much appreciate it if someone would tell me how to translatea statement level trigger written in Oracle to its equivalent (if there isone)in MS SQL Server. Ditto for a row level trigger.If this is an old topic, I apologize. I'm very much a newbie to SQL Server.Regards,Allan M. Hart

View 2 Replies View Related

Using Members And Roles With ONE Db

Jan 8, 2007

i'm not sure to put this in data or security, so i'll put it in both and put on my flame suit.....I'd like to setup the security to use the one single DB that i've setup to use for my inventory, instead of the ASPNETDB.MDF that accompanies the normal setup.If i need to include more info, please ask. 

View 1 Replies View Related

Calculated Members

May 9, 2005

This is correct
store.currentmember.properties("Store_Manager")

I want to obtain information of the column Store_Manager, the table Store, this is dimension, but BUT IT SHOW TO ME A MESSAGE OF ERROR: #ERR

Do you can help me?

View 1 Replies View Related

Calculated Members

Jun 21, 2006

Hi...
I'm trying to make a calculated member but I want it at the last level only, with the others I want that shows the sum of the previus :confused: .... Somebody can help me????

View 1 Replies View Related







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