Author: Soheil Bakhshi

BISM Tabular, Business Intelligence - BI, SQL Server Analysis Services - SSAS

How to Automate SSAS Tabular Model Processing

You are working on a BI project that involves SSAS Tabular model. As you cannot process the model manually you need to develop a solution to process the model automatically. There are two scenarios here:

  1. You have an up and running SQL Server Integration Services (SSIS) instance: In this case you can build a SSIS package to process the Tabular Model.
  2. You do not have an SSIS instance ready on the server: So you need to create a SQL Server Agent Job.

In this post I’m expressing easy ways to solve the problem based on the above scenarios.

Using SQL Server Integration Services to Process SSAS Tabular Model

Follow the steps below:

  1. Create a new SSIS project using (SQL Server Data Tools) SSDT
  2. Right click on the “Connection Managers” area and select “New Analysis Services Connection”image

Continue reading “How to Automate SSAS Tabular Model Processing” →

Administartion, SQL Server, T-SQL

How to manage the user access rights to see database views but not source tables using T-SQL

Problem:

There is a group of users that are not intended to have direct read access to the database tables. There are some predefined database views that the users should be able to see the data through those views. In our case, the users shouldn’t be able to even see the tables in SSMS or through any applications that can connect to the database. Additionally, the users should be as restricted as possible. For instance, they shouldn’t even know what the source table names are. So SYS or INFORMATION_SCHEMA should not show any additional information.

Solution:

The best possible way to achieve the goals is that we create a new database role and define the users as members of the new database role. We create a database role very easily though SSMS, but, if we have lots of views and we want to define accesses through the UI it would be a time consuming process. In addition, it increases the risk of human faults during setting up the configuration.

A very simple way is to use the following T-SQL script that will create a database role, it will also add the views as the role’s securables and it will grant the sufficient access rights so that any users that are members of the role be able to see the views. They’ll be also able to execute the views and see the results. You just need to make sure that the users are not members of some other roles that have overlap with the new role’s permissions.

Continue reading “How to manage the user access rights to see database views but not source tables using T-SQL” →

Business Intelligence - BI, SQL Server Integration Services - SSIS

Deploying SSIS Projects to Different Environments

In this post I’m explaining how you can deploy a developed SSIS project to several different environments. It might have happened to you that there are several environments that you need to deploy the SSIS projects to. Assume that you have DEV, QA, UAT and PROD environments. Some organisations might have even more environments. Also, there are many cases that you might have several PRODs that the SSIS packages should be deployed to all of them. So the scenario is that whenever you create a new SSIS project in DEV area or you may modify the existing projects, you need to deploy each SSIS project to QA for testing purposes. So, if you have 3 new SSIS projects or you’ve just modified 3 existing projects, you’ll need to deploy each project separately. It is the same story for QA guys after finishing the test cases and after the SSIS projects pass all the test cases. They’ll need to deploy all projects to UAT. Again it is the same story with UAT and PROD. It is getting harder when you need to deploy all the projects in several different PROD environments.

Using the solution below, you can easily deploy all SSIS projects from an environment to another environment or even several different environments.

Continue reading “Deploying SSIS Projects to Different Environments” →

Business Intelligence - BI, SQL Server Integration Services - SSIS

Operating on different source SQL Server instances in a single SSIS package

In some cases we need to do a single task for lots of SQL Server instances. Assume that we have a web based programme. The programme’s database is distributed across the country and we have 10 different virtual (VM) servers to host the programme’s databases. The programme is working based on some configurations that are stored in a CONFIG database. The CONFIG databases are hosted by 20 different SQL Server instances to serve 20 different clients. The SQL server instances are all named SQL server instances hosted by those 10 virtual servers. We need to update the CONFIG database for all regions on a monthly basis. The database structure of all CONFIG databases is the same. In this case a simple way is to create an SSIS package for each source server to collect the data from all source databases one-by-one. This means that we will have 10 copies of the same SSIS package that each package is pointing to a server as a source server. We need 10 packages because we can retrieve the CONFIG database list by writing a T-SQL script or using an extra Foreach Loop Container. So we need a SSIS package per server.