Securing Role Playing Dimensions in Analysis Services

In Analysis Services, dimension data can be secured at either Shared Dimension or Cube Dimension. A Shared Dimension is an object of Analysis Service Database which is available to all the cubes in the database. The Cube Dimension on the other hand is an object of the cube and is an instance of the Common Dimension which is available only to a specific cube
(Screen Capture 1).

Screen Capture 1 – Shared Dimension Vs Cube Dimension

Usually, it’s a good idea to secure the data at Shared Dimension level as the permissions gets passed down to Cube Dimensions. This is especially helpful if the Dimension Data security is automated using AMO code – less code to maintain.

This works in most scenarios, except when the dimension is used as Role Playing Dimension. For e.g. Time, Currency, Organization Structure, Staff dimensions are commonly role played. Analysis Services does not permit securing the data of Shared Dimension if it is used as role playing dimension. Exceptions are thrown when the cube if browsed, if you attempt to do so.

If a dimension is role playing, the only way data security can be implemented is by securing the individual Cube Dimension. Cube Dimension Data Security overrides Shared Data Security.

The following screen capture shows the effect of securing the role playing dimension- using Date Dimension from AdventureWorks. Here Date Dimension (Shared Dimension) role plays as Delivery Date (Cube Dimension) and Ship Date (Cube Dimension).

Screen Capture 2 – Securing Role Playing Dimension

Securing Cube Dimensions can be quite daunting especially if you are considering automating cube security because all the related dimensions need to be considered individually, but that’s the only way out.

How do you create Computed/Calculated column in SQL Server?

Computed Column in SQL Server

How do you create Computed/Calculated column in SQL Server?

A computed column is computed based on an expression that can use other columns of the same table. The expression can be a non-computed column name, constant, function, and any combination of these connected by one or more operators. However, the expression cannot be a sub-query.
 
Scenario: I want to create a computed column which should be calculated based on the values of other columns. For instance, Employee table has DateOfBirth column and I want to create a computed column to store the Age of an employee (in years). Another example could be to display the Grade of a student based on the marks obtained by the student.

Example 1: Employee (EmployeeID, EmployeeName, BasicSalary, DOB)

EmployeeID	EmployeeName	BasicSalary	DOB
1	Hari	5000	1983-05-05
2	John	3500	1978-10-25
3	Peter	4500	1982-02-10
4	Ravi	2500	1980-03-04
5	Max	3600	1985-09-20

Problem: Add a new computed column Age in Employee table.

Example 2: Student (StudentID, StudentName, MarksObtained)

EmployeeID	EmployeeName	MarksObtained
1	Hari	74
2	John	80
3	Peter	55
4	Ravi	34
5	Max	67

Problem: Add a new computed column Grade in Student table using following grading system:

If MarksObtained >= 75 then A
If MarksObtained >= 60 then B
If MarksObtained >= 40 then C
Else F
 

Example 1:

--------------------------------------

-- Prepare data
--------------------------------------
IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL   DROP TABLE dbo.Employee
GO

CREATE TABLE dbo.Employee (

     EmployeeID INT
    ,EmployeeName VARCHAR(30)
    ,BasicSalary SMALLINT
    ,DOB DATE
)
GO

INSERT dbo.Employee VALUES

 (1, 'Hari', 5000, '1983-05-05')
,(2, 'John', 3500, '1978-10-25')
,(3, 'Peter', 4500, '1982-02-10')
,(4, 'Ravi', 2500, '1980-03-04')
,(5, 'Max', 3600, '1985-09-20')

SELECT * FROM dbo.Employee

 

--------------------------------------

-- Add computed column
--------------------------------------
ALTER TABLE dbo.Employee
ADD Age AS DATEDIFF(YEAR, DOB, GETDATE())   

SELECT * FROM dbo.Employee

 

 

 

 

You can also explicitly define the data-type of the computed column if you want. You can use CAST/CONVERT to explicitly define the data-type.

--------------------------------------

-- Change data type of computed column

--------------------------------------

ALTER TABLE dbo.Employee DROP COLUMN Age

GO

ALTER TABLE dbo.Employee

ADD Age AS CAST(DATEDIFF(YEAR, DOB, GETDATE()) AS TINYINT)

 

 

Example 2:

--------------------------------------

-- Prepare data

--------------------------------------

IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL   DROP TABLE dbo.Student

GO

CREATE TABLE dbo.Student (

     StudentID INT

    ,StudentName VARCHAR(30)

    ,MarksObtained TINYINT

)

GO

INSERT dbo.Student VALUES

 (1, 'Hari', 74)

,(2, 'John', 80)

,(3, 'Peter', 55)

,(4, 'Ravi', 34)

,(5, 'Max', 67)

SELECT * FROM dbo.Student
 

 

 

 

--------------------------------------

-- Add computed column

--------------------------------------

ALTER TABLE dbo.Student

ADD Grade AS

CASE

   WHEN MarksObtained >= 75 THEN 'A'

   WHEN MarksObtained >= 60 THEN 'B'

   WHEN MarksObtained >= 40 THEN 'C'

   ELSE 'F'

END

GO

   

 

SELECT * FROM dbo.Student

　 

 

Note:
Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:

• Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.              For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(DD, GETDATE()) cannot be indexed because the value may change in subsequent invocations. 
• A computed column cannot be the target of an INSERT or UPDATE statement.

Parse Numbers from a String(Eliminate number from string)

Parse Numbers from a String(Eliminate number from string) 

Someone asked me recently about T-SQL function to parse numbers from a string. Since there is no built-in function as such, I explained him the logic to calculate same thing using T-SQL code. I will explain the logic here, in case you need to implement same thing (or somewhat similar thing) in your project.

Let’s take example to understand the problem.

Input	Output
Hari12 Sharma34	1234
My 1st code is D$34A and 2nd code is E#078!!!	1342078
Hari# Sharma
$1#0?0 !?@1	1001

Here is the code to get the desired output:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'

DECLARE @Position SMALLINT = 0

SET @Position = PATINDEX('%[^0-9]%', @Str) --Find first character

 

WHILE (@Position > 0)

BEGIN

    -- Replace alphabet with empty string.

    SET @Str = STUFF(@Str, @Position, 1, '')

   

    -- Find next alphabet

    SET @Position = PATINDEX('%[^0-9]%', @Str)

END

SELECT @Str Result

 

Result

-------------------------------------

1342078

 

We can also write function to encapsulate this code and use wherever required.

This above code can be optimized by using Tally table mentioned below:

DECLARE @Str VARCHAR(100) = 'My 1st code is D$34A and 2nd code is E#078!!!'

 

DECLARE @Result VARCHAR(100)

SELECT  @Result = COALESCE(@Result, '') + Digit

FROM    (

        SELECT  SUBSTRING(@Str, t.ID, 1) AS Digit

        FROM    dbo.Tally t

        WHERE   t.ID <= LEN(@Str)

        AND     PATINDEX('%[0-9]%', SUBString(@Str, t.ID, 1)) = 1

        ) stg

 

SELECT @Result Result

Result

-------------------------------------

1342078

CONCAT() IN SQL SERVER

CONCAT() IN SQL SERVER 2012

SQL Server 2012 introduces a new string function CONCAT() which allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values. Good thing about the function is that it takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This function would help eliminate the need of explicit data conversions when concatenating two values.

 Note: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned.

SYNTAX:

SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN])
-- Between 2 and 254 values can be passed. 

EXAMPLE:

DECLARE @FirstName NVARCHAR(10) = N'हरि' 
       ,@LastNameVARCHAR(10) = 'Sharma'
       ,@Cell VARCHAR(20) = '+1 (425) XXX-YYYY'
       ,@Age INT = 30
       ,@Delimiter VARCHAR(2) = SPACE(2)
SELECT CONCAT(@FirstName, @Delimiter, @LastName, @Delimiter, @Cell,@Delimiter, @Age)

OUTPUT:

　

You might have observed that there’s no data conversion being performed in the above example.

The data returned by CONCAT function depends on the type of valued passed. Below are some of the mapping input and output types:

 

Input Value / Type	Output Value / Type
SQL-CLR (TYPES & UDT)	NVARCHAR(MAX)
NVARCHAR(MAX)	NVARCHAR(MAX)
NVARCHAR(<=4000)	NVARCHAR(<=4000)
VARBINARY(MAX)	NVARCHAR(MAX)
ALL OTHER INPUT TYPES	VARCHAR(<=8000) *Unless one of the parameters is an NVARCHAR of any length and in that case, the output value would be in NVARCHAR(MAX)

NOTE: CONCAT function only works with SQL Server 2012 and later versions.