Analysis :: Scope Statement For A Specific Role
Nov 2, 2015Just wanted to know is there any way to write scope statements only for a specific role in the cube.
View 3 Replies
ADVERTISEMENT
Analysis :: YTD Calculation For Multiple Hierarchies With SCOPE Statement
Jul 2, 2015I 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.
Analysis :: IIF Does Not Aggregate AND Filter In SCOPE Does Not Work
Oct 22, 2015We have two measures
[Measures].[NSPO]
is in FACT1 Measure Group
[Measures].[NetSignedPremium] is in FACT2 Measure Group
The requirement is Measure.[NetPremium] should be the greater of
[Measures].[NSPO] OR [Measures].[NetSignedPremium].
All three measures are created in the cube and this logic is to be applied in the Calculations section of the cube.
Using an IIF statement like one mentioned below does the job, but data does not aggregate correctly for the
Measure.[NetPremium]
Measure.[NetPremium] = IIF ([Measures].[NetSignedPremium] > [Measures].[NSPO], [Measures].[NetSignedPremium] , [Measures].[NSPO])
To ensure that the columns are aggregated I have to use a SCOPE statement at the leaf level
[01 UWR Reference].[UWR Reference].Members
SCOPE(
FILTER( NONEMPTY( [01 UWR Reference].[UWR Reference].Members ) ,
( ( ([Measures].[Net Signed Premium Rcvd])
- ( [Measures].[NSPOTest] ) )
< 0 ) )) ;
This = [Measures].[Net Signed Premium Rcvd] ;
END SCOPE;
My problem is the FILTER within the SCOPE statement does not work. How to modify the filter in SCOPE? OR How to make IIF Aggregate correctly?
Analysis :: Exclude Members From A Scope - MDX SSAS?
Sep 27, 2013I'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]);
Analysis :: DAX - How To Simulate MDX SCOPE Forcing YEAR Level
Jul 19, 2015In 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.
Get List Of Users Not In A Specific Role
May 30, 2008ere is the make-up of the tables:
[dbo].[PortalUser](
[PortalUserID] [bigint] IDENTITY(1,1) NOT NULL,
...
[dbo].[Role](
[RoleID] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](250) NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_Roles_IsActive] DEFAULT ((1))
...
[dbo].[PortalUserRole](
[PortalUserRoleID] [bigint] IDENTITY(1,1) NOT NULL,
[PortalUserID] [bigint] NOT NULL,
[RoleID] [bigint] NOT NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_PortalUserRole_IsActive] DEFAULT ((1))
I'm asking to get a list of portalUsers that do not have a PortalUserRole records for the rolename I'm checking against. And don't ask me why the person who coded this is searching on rolename not ID. But this is how we're doing it for now.
Analysis :: Cannot Delete Role
Aug 24, 2015we are using a 2012 SSAS cube with many (about 100-200) roles. We use AdoMD to programmatically create, edit and drop roles. Everything works fine until we want to drop a role (this occurs only with some of the roles). I thought this was an AdoMD problem, but I actually get the same error when I delete such role in Management Studio. I attached the full exception message. I tried to remove all members before dropping the role to no avail, there must be some other dependency.
Program Location:
at Microsoft.AnalysisServices.Server.SendBatch(ArrayList objectsToDelete, ArrayList objectToAlter)
at Microsoft.AnalysisServices.MajorObject.DropPrivate(DropOptions options, XmlaWarningCollection warnings, ImpactDetailCollection impactResult, Boolean analyzeImpactOnly)
at Microsoft.AnalysisServices.MajorObject.Drop(DropOptions options)
at Microsoft.SqlServer.Management.SqlManagerUI.DropOlapObjects.DoDropObject(Int32 objectRowIndex)
at Microsoft.SqlServer.Management.SqlManagerUI.DropObjects.DropAllObjects(Boolean stopOnError)
Add Role To Analysis Services Database
May 23, 2007Does anyone know if there is a way to use a SSIS task to add a role to SSAS cube? At the moment I use Management Studio, Right Click "Roles" under the cube and go through New Role wizard, but I'd like to be able to include this in a SSIS package instead.
Thanks
Richard
Analysis :: Role Based Drill Through Action
Jun 3, 2015I have number of users which are under 6 Groups. We have 4 drill through actions. Now I would like to restrict 2 drill through actions for only some groups.
View 2 Replies View RelatedAnalysis :: Security Role Default Member
May 12, 2015I am setting up some security requirements - the requirement is that team leaders get to view all teams within their division. What I would like it to do when they log onto their dashboard/ssrs report is that it defaults to the team leaders default team (they can still select from other teams if they require).If I can avoid I don't really want another parameter in the report for this.
In my cube I have an attribute under the people dimension (where teams and division also reside) called Fee_Earner_Effective_Flag and where this is set to Y this is their current team.
Under the Role properties security under the Fee_Earner_Effective_Flag attribute I have set the Default member to
[People Primary FeeEarner].[Fee_Earner_Effective_Flag].[Fee Earner Effective Flag].&[Y]
When I then logon as the user and try to access the cube I get the following
Errors in the metadata manager. The '[People Primary Feeearner].[Fee_Earner_Effective_Flag].[Y]' security default member from the '{' attribute returns a result from a different hierarchy.
I have also tried setting the default member in the cube solution and then processing but then the cube failed.
Analysis :: Dynamic Measure Value Based Upon Role / Department
Sep 28, 2015I have an issue related to SSAS security. We have an SSAS multidimensional cube which needs 3 types of security:
- Access to the entire cube => OK, based upon a role
- Restricted access to one department (= dimension) => OK, based upon a role
- Access to the entire cube, but with dynamic security on 2 measures.
Let's say, we have 2 departments (food and non-food). Users within food are allowed to see sales and pieces from the food department, but not from the non-food department.
It is not an option to restrict access to the non-food department because there are other measure which they have access to. I tried cell security, but this is very slow and generates multiple empty rows on my selections.
Analysis :: Add Descriptions To Attributes In Role Playing Dimensions
Jul 28, 2015I 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?
Analysis :: DAX - Dynamic Masking Of Information Based On Role Security
Oct 2, 2015I have a business requirement to build a tabular data model, where I need to mask information of other Agents from a given Agent but I still need to show the overall sales of the given product.
For eg: IF an Agent is in APAC region he should see APAC region sales and also should be able see the sales of the same product in other region without knowing region specific break down.
For Agent "Tom" in APAC region, the numbers will look like this
APAC_Sales = 100,000
Other_Sales = 500,000
And if "John" is in NA region, then the number will look like this for him
NA_Sales = 200,000
Other_Sales = 400,000
I wanted to create "Roles" based on the Region, so all the agents belong to "APAC" region will have same view as Tom and "NA" region agents will have John's view.
Analysis :: Role Playing Dimension Among Multiple Fact Tables
Oct 28, 2015I am modelling cube in SSAS. Cube has around 20 dimensions and 6 fact tables. Some of the dimensions are common among the fact tables. e.g. Time dimension. Fact_PNL has 3 date columns for those we have 3 role playing dimensions in the dimension usages.
Another fact table has 5 date columns for them as well we have separate role playing dimensions in dimension usage tab. We have a common dimension Company which is foreign key in all fact tables. We might need to combine the data from multiple facts to get final output.
Should i create 6 role playing dimension for each of the fact table or use the same dimension for all fact tables?
Role playing dimensions should be created when we have multiple columns pointing to the same dimension ?
Analysis :: Show Grand Total When Using SSAS Tabular Role Security Permission
Oct 30, 2015If user want to see the grand total for a measure with include all members, even though the user has limited access for that member, so how we can do using DAX?For example, let’s say the total revenue for all the divisions in a cube is $15,000. You create a role called “Division A”, and set it up so members of that role can only see the revenue for Division A, which totals $3,000. If you use a front-end tool like Excel to access the cube and use the division hierarchy to see the total revenue, you will see the revenue of $3000 for Division A, but also want to see the Grand Total for the revenue as $15,000How we can achieve above scenerio in tabular model (DAX).
View 3 Replies View RelatedConnect To Analysis Services With Specific Username
Jul 10, 2007Hello,
We are trying to develop an ASP .Net web application that connects to an SQL Server 2005 analysis services and executes MDX statements. We want the connection to be made using a specific user name that we specify in the code rather than having the web application inherit the logged on domain user account.
We tried to specifiy the user name in the connection string as follows, but it is still taking the logged in user account rather than the one specified.
Microsoft.AnalysisServices.Server s = new Microsoft.AnalysisServices.Server();
s.Connect("Provider=MSOLAP.3;Password=123;Persist Security Info=True;User ID=domainusername;Data Source=<server ip>;Initial Catalog=<analysis DB>");
Is this possible?
Note that if we are using the OLEDB 9.0 provider for Analysis Services to connect an excel sheet to the analysis database we can easily specify the user name and password, so is there a similar way to do the same from a .Net application?
Any feedback is appreciated, thanks,
Grace
Variable Scope And Connection Manger Scope
Mar 13, 2008
I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.
It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.
My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)
Analysis :: Tabular - Filter / Search For A Specific Measure Value?
May 14, 2015One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
How To: Determine If Current Windows User Has Login Access, Database Access And If They Are A Member Of A Specific DB Role.
Mar 25, 2008
I need to determine the following about the current authenticated Windows domain user who is trying to access a SQL Server via a trusted connection.
1 Has the current user been granted login access to the trusted SQL Server?
2 Has the current user been granted access to a specific database?
3 Is the current user a member of a specific database role such as (DB_ROLE_ADMINISTRATORS)?
Thanks,
Sean
Analysis :: Order Total For Orders Containing A Specific Product Group?
May 27, 2015I have a request for being able to show ordertotals for those orders that contains a specific productgroup.
Case
Order 1 ProductGroup Value
A 20
B 40
C 40
Total 100
Order 2 ProductGroup Value
A 20
D 40
Total 60
So if i slice OrderTotal by ProductGroup the result will be
A 160
B 100
C 100
D 60
ALL 160
how to solve this.
Analysis :: MDX - Aggregate Measure For Specific Time Range / Filtering And Print Only Current Month
Aug 6, 2015At the following MDX code , I want to get the aggregate of measure only for members that are also in the specified last time (like in examp 01/06/2015) . I tried existing and exists, but without any lack.
WITH MEMBER A AS
(b)+(C)
MEMBER [Measures].[Aggregate] AS
Aggregate(DAYTIME].[Month].&[2013-01-01T00:00:00]:[DAYTIME].[Month].&[2015-06-01T00:00:00],
([Measures].[D])
[Code] ....
To SLQ Statement At A Specific Time
Sep 26, 2005Hi There,
I want to able to run a batch of sql statments (execute procedures) in SQl Server at a specific time. Just wondering if there is a method for that.
Analysis :: Dimensions Attributes - Drag All Or Some Specific Attributes
May 24, 2015I'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?
Whqt Is The TSQL Statement To Make A Backup Of A Specific Databese??
May 10, 2008i have a database names "students" in SQL server 2000. is there any TSQL statement to make a backup of the student databse in to a sspecific location ??????????
pls help
Case Statement - Count Orders That Fall Into Specific Dollar Buckets
Aug 2, 2013I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!
For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:
Code:
CASE
WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
...
WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]
Is there an easier way to do this, maybe with a loop?
SQL 2012 :: Identifying Specific Blocker Statement When It Is Part Of Explicit Tran
Jul 2, 2014App developers are complaining that a blocking report I sent them today does not have enough info to troubleshoot and fix the root cause of the blocking.(I used SQL Sentry to get the report and send to the users, but I think my question is more fundamental than just using one 3rd-party tool.)The reason for the complaints is that the top blocker is a SELECT stmt on TableA that is not the Wait Resource of the blocking (TableB).
I explained that this is likely because the "blocking" SELECT stmt on TableA is part of a larger explicit tran that includes TableB, and that they need to look through their code to identify the SQL stmt that is the root cause of the blocking.They would like that root statement identified in the blocking report, but I don't think it is possible to get to that through DMVs.
Analysis :: DAX With IF Statement Very Slow
Jul 21, 2015I have been trying to optimize a few DAX calculations that I have in my cube and ran into this problem Basically my DAX Measure
Counterparty Collateral Base DKK:=
[Counterparty Collateral Contracts DKK (filtered)]
+
CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16)
is pretty fast,
whereas as soon as I change the 2nd part to only add Account Balance if its>0, it takes ages i.e.
Counterparty Collateral Base DKK:=
[Counterparty Collateral Contracts DKK (filtered)]
+
IF(CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16)>0,
CALCULATE(-[Account Balances DKK], Accounts[Counterpart Type ID] <> 8, Accounts[ContractTypeID] <> 14, Accounts[ContractTypeID] <> 16),0)
Offcourse because I check every row I suppose with that "if" statement. Question is how do I improve it?
B
y the Way: [Account Balances DKK] is in the table - Account Balances
[Counterparty Collateral Contracts DKK (filtered)] is in the table- Open Positions
This calculated measure [Counterparty Collateral Base DKK] is in Open Positions.And the query in MDX, that I run to pull this measure, looks like
select {[Measures].[Counterparty Collateral Base DKK]
}on 0,
Non Empty {[Accounts].[Counterpart ID].children
*[Base Currencies].[Currency Code].children
*[Products].[Product Name].children
} on 1
from [model]
where [Dates].[Date].&[2015-06-30T00:00:00]
The requirement is such that I have to add the measures in two separate tables.How can I improve the performance when adding the filter?
SQL Server 2012 :: Using Merge Statement Output Records To Rebuild Table On Specific Date
Mar 5, 2015I have a table which is updated daily using a MERGE statement. As records are insert, updated and deleted, I am saving the OUTPUT from the MERGE statement into a history table with a timestamp and action$ column appended to the record.
Using this history table, I'd like to rebuild the data based on specific past date. I was able to create a stored procedure that inspects each record in the history table and apply it to the data in a temp table. The stored procedure solution uses multiple queries to rebuild the data at a point in time. I was curious if there was an easier and more efficient solution using a table function.
SQL Server 2012 :: Select Case Statement To Remove Part Of String After One Or Two Specific Characters
Jun 3, 2015I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN***
Example Record 2. Theale, Nr Reading, Berkshire
Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0
THEN LEN(Address )
ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0
THEN LEN(Address)
ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
Sql 2005 Database Role Vs Application Role ?
May 18, 2007Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
thanks
Analysis :: MDX SSRS Parameter In Case Statement?
Nov 3, 2015I am trying to calculate a measure based on parameter passed I have a liquidation rate of amount/facevalue when the dimension attribute is 'all' and i will like to the 'amount' to change based on the parameter passed and the 'facevalue' should not changed , i tried with the code below.
What I noticed is when i add a calculated member to return the currentmember of the cell member
measures.strat
as
[Strategy].[ICS].CURRENTMEMBER.uniquename
it comes back as
[Strategy].[ICS].[All]
even when I changed the default value for the parameter to another member like red
WITH
MEMBER
MEASURES.TEST
AS
((
CASE
WHEN [Strategy].[ICS].CURRENTMEMBER.uniquename = '[Strategy].[ICS].[All]'
THEN
[Measures].[Measures].[AMOUNT]
[code]....
Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)
May 23, 2015I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
SQL Server 2008 :: Replicate Only Specific Data To Specific Subscriber?
Jun 30, 2015We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?