SSAS – Dynamic dimension security on a multidimensional cube including Active Directory and HR data

Case
 
In a data warehouse environment based on SQL Server, SSIS, SSAS MD and SSRS, a common request is to limit user permissions to specific data and measures. It’s common end users have only access to specific cost centres and/or specific (sensitive) measures. To limit the administrative work in managing SSAS roles and SSRS permissions, I have developed an automated mechanism where users automatically have access to the data and reports they are allowed to see. The permissions can be managed  by configuring two master tables which can be administrated by any users, no specific technical knowledge is required.

Prerequisite

• Access to Active Directory
• Employeenumbers are stored in Active Directory and correspond with Employeenumber in HR system.

Solution
 
Arhitecture

T-SQL - My personal t-sql notes

In this blog you will find my t-sql notes for specific scenario's. Use them if you like!

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

--Get the latest date for a specific item in a table

--in this example the latest order per customer

create table #orders

(

    orderid int,

    customerid int,

       orderdate date,

       quantity int

)

INSERT INTO #orders VALUES (1,1,'2018-01-01',10)

INSERT INTO #orders VALUES (2,1,'2018-02-01',10)

INSERT INTO #orders VALUES (3,1,'2018-03-01',10)

INSERT INTO #orders VALUES (4,2,'2018-07-01',10)

INSERT INTO #orders VALUES (5,2,'2018-08-01',10)

INSERT INTO #orders VALUES (6,3,'2018-10-01',10)

;WITH CTE_LatestOrderPerCustomer AS (

       SELECT

             orderid,

             customerid,

             orderdate As LatestOrderdate

       FROM (

             SELECT

                    orderid,

                    customerid,

                    orderdate,

                    ROW_NUMBER() OVER (PARTITION BY customerid ORDER BY orderdate DESC) As rownumber

             FROM #orders

       ) orders

       WHERE rownumber = 1

)

SELECT * FROM CTE_LatestOrderPerCustomer

DROP TABLE #orders

SSIS - Dynamically load csv files into SQL table, then zip with 7-zip and move to archive location

Case

I want to load csv files from a parameterized location into a SQL table, after processing I want to zip and compress the csv files and move them to a parameterized archive location.


Solution

With SSIS you can loop through files in a specific directory, process each individual file, move them to a different directory and compress the files. In this solution I will demonstrate the processing of flat files with the foreach loop container, data flow task, file system task and execute process task.
The compressing is handled by 7-zip, a free open source file archiver.

Prerequisite

• 7-zip is installed on your server or local machine, click here to download.
• 7-zip is added to your PATH enviroment variable
• You have a sql server database and are able to write data to