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.
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.
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.
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'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
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 ?
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 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?
I'm creating one of my first asp.net pages, and it accesses an MS SQL database.It runs fine locally, but if you go to it remotely through a web browser, you get an exception saying that the database login failed for user ASPNET.My brother told me to do the following in SQL Server Managment studio. It seems I don't have that program installed, so I did it from the SQL command prompt.CREATE LOGIN [bigblueASPNET] FROM WINDOWSuse AdventureWorkscreate user [bigblueASPNET](Where "bigblue" is the computer name and "AdventureWorks" is the database my asp.net web page reads from).This only partially fixed the problem - now when you open the page remotely and try to access the DB I get an error message saying that permission to use the SELECT command is denied.I searched on these forums, and found this:http://forums.asp.net/69166/ShowPost.aspxFor future reference, let's call what my brother told me "Method 1" and what the previous poster did as "Method 2."I haven't tried method 2 because I don't know exactly what I am giving ASPNET permission to do. Does method 2 enable ASPNET to do anything to any database? It seems method 1 is more secure, as from the commands I could already tell it only has permission to access AdventureWorks.So, I'd prefer to use method 1. Can anyone tell me what further commands I can use to give ASPNET permission to do specific actions?If I can't figure out method 1 and have to do method 2, how can I undo what I did in method 1?Thanks!
Tools -> connect to database -> Select datasourse - Microsoft SQL Server (SqlClient) -> Server name - comp4_8SQLExpress -> Log on to the server €“ Use Windows Authentication -> Connect to a database -> Select or enter a database name €“ MyDataBase
comp4_8 - is the name of my computer. I am using Windows XP Professional SP2. Login as administrator without password.
Now about the problem I have. On local machine everything is great! But I hosted my website and got this error. The question is how to give permission to this user to connect to my database. Cannot open database "MyDataBase" requested by the login. The login failed. Login failed for user 'ANGELINAASPNET'.
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?
I have an application which is using SQL Reporting Services 2005. I have all my RDL files under a root folder named "X". Now I need to give users/groups permission to browse the reports under this folder. I need to do it programatically using the web services class ReportService2005 (I thought this would be the right class to do this task). Can anyone please throw some light on this.
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]))
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" :
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.
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.
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.
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.
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?
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 ....
Is there a way - through a DMV or other query - that I can retrieve the collation for a hierarchy using code?I know that I can find out the server, cube, and hierarchy collation using SSMS. But I'm looking to do this in code, in order to compare collations between two cubes' dimensions, and compare member values.
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
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 .
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.
