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

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.


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

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 (
             orderdate As LatestOrderdate
       FROM (
                    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


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.


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.

  • 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