Category: T-SQL

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

How To Implement a Composite Key In SSAS Tabular Model

As you might know SSAS tabular models do not support composite keys so you always must have just one column to make a unique row through the whole table. This is such a pain especially when you are new to the tabular models and don’t have that much detail information about it. So when you import some tables with existing relationships based on composite keys, the Table Import Wizard will ignore those relationships.

So what should we do to solve the problem?

The solution is to combine the values of the composite keys. 

Here is how you can do the job?

·         Creating a view on top of the source tables:

1.  If you’re using SQL Server 2012 and above you can use the “concat” function to combine the values. The function combines several expressions regardless of their data types. So you can use it like this select CONCAT (1, 1.22100001,‘First’) SQL2012 and the result would be something like this

clip_image001

2.  If you’re using earlier versions of SQL Server then you need to mind the data types. So for the above sample the SQL code would be select cast(1 as char(1)) + cast(1.22100001 as char(10))+‘First’ SQL2008 . As we expect the result is the same.

·         Adding a new computed column to all tables involved in SQL Server before importing the tables to the tabular model

·         Adding a new calculated column to all tables involved after importing the tables to the tabular model

As a quick note, you’ll need to remove the existing relationships imported from SQL Server and create the new relationship based on the combined keys.

Easy peasy!

Data Warehousing - DW, T-SQL

Creating a Simple Date Dimension Using Recursive Common Table Expressions (CTE)

In this post I’ll explain how to create a simple date dimension to use it in your data warehouses and your BI solutions. So, this article is for you if you need a fast and easy way to make a simple date dimension that supports the most commonly used date elements like

·         Integer date key

·         Different date formats

·         Quarter

·         Month names

·         Week numbers

·         Day of the week

·         Day of the year

·         Is day end of month

·         Not available (N/A) row

Due to the fact that there are lots of fellows that are still using SQL Server 2008 and earlier, I put the codes that support SQL Server 2008  as well as SQL Server 2012. But, I’ve commented the 2008 lines.

Continue reading “Creating a Simple Date Dimension Using Recursive Common Table Expressions (CTE)” →

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” →

SQL Server, SQL Server Integration Services - SSIS, T-SQL

How to install database diagram support programmatically

In the previous article we discussed about how migrating a database diagram into another database. In this article I’ll quickly show you how to install database diagram support. As mentioned in the” How to copy or migrate database diagrams into another database” article, we need to install database diagram support to be able to see the migrated database diagrams. We also explained a very easy way to install database diagram support from SSMS in the “How to store a SQL Server database diagram into a file and share it with others?” article. Now, assume that we want to migrate the database diagrams into several SQL Server instances. It seems that it might be better if we can implement the whole process programmatically. This was exactly my question when I wanted to deploy several database diagrams from a database hosted in development environment into a copy of that database hosted by test or UAT (User Acceptance Test) environments. For instance, just assume that there are a bunch of database diagrams created by developers in development environment. So, you’ll have an exact copy of the database structure in test environment. Your testers need to use the database diagrams created by the developers. So far, so good. This part of the challenge is covered in the previous articles. However, we still need to install database diagram support manually and this is what we don’t like! The solution is really easy. Run the following code and you are done! You can also add the following code to the execute SQL task from your SSIS package if you decided to implement the solution in an SSIS package (take a look at “Migrating database diagram by creating a simple SSIS package” No. 8).

Continue reading “How to install database diagram support programmatically” →