Category Archives: Business Intelligence – BI

How to send SSIS logs (errors) through email

First of all I want to explain the process of sending SSIS Logs through email for better understanding. As you might know you can save SSIS logs in several ways for administration purposes. In this article our focus is on how to send SSIS logs to the administrator whenever an event like a package failure is occurred. Just assume that we have several SSIS packages and we are storing the logs in a SQL Server database. Almost all of the packages are running over night. Now, what if a package or some packages failed? Generally one of the most essential activities is that a notification email should be sent to the system administrators to let them know that something’s wrong with the package execution. It could be more helpful to send them the relevant information about the failure. Reading the following lines you can handle this important part of the process in your organisation.

Continue reading How to send SSIS logs (errors) through email

How to hash sensitive data for maximising security in SQL Server 2005 and later versions?

SENSITIVE DATA! It’s an interesting topic! In this post I’m trying to explain how to hash data to increase security during ETL. Assume that we have sensitive data stored in several secured source systems. The source systems are located in different countries and different regions. As the source systems themselves are secured, how we can cover data security needs during ETL process to read data from source systems and load into staging area? Apart from using secured network infrastructure, VPN, network tunnelling etc. we need to cover data layer security to extract sensitive data. One of the best ways is hashing data when it is extracting from source databases. Hashbytes is a T-SQL function that is available in SQL Server 2005 and later. As you might know there are many hashing algorithms, but, different SQL Server versions are supporting different range of hashing algorithms. For instance SHA1 is supported by SQL Server 2005 and later, but, if you are looking more secure hashing systems like SHA2, 256 (32 bytes) or 512 (64 bytes), you should use SQL Server 2012. Actually the hashbytes function will return null in earlier versions of SQL Server. If you are looking for a higher level of security like SHA3 that is originally known as “Keccak” you should wait for it for a long time as based on my investigations it is not supported even in SQL Server 2014 OR you can write your own SHA3 code OR just rely on some third party codes available on the Internet! So let’s get our hands dirty with using hashbytes in different versions of SQL Server.

Continue reading How to hash sensitive data for maximising security in SQL Server 2005 and later versions?

Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot

SSAS 2012 supports three different approaches for creating a BISM (Business Intelligence Semantic Model):

1. Tabular

a. Uses relational modelling constructs like such as tables and relationships

b. Uses xVelocity in-memory analytics engine for sorting and data calculations

c. Needs to use SSDT (SQL Server Data Tools) to implement

d. Can import data from relational data sources using OLE DB native and managed providers

e. Tabular solutions only support one model.bim file per solution, which means that all work must be done in a single file. Development teams that are accustomed to working with multiple projects in a single solution might need to revise how they work when building a shared tabular solution.

f. support DAX calculations, DAX queries, and MDX queries

g. Tabular model databases can use row-level security, using role-based permissions in Analysis Services (DAX implementation required)

h. It might be not a good choice for the systems that are going to load terabytes of data

Continue reading Three different approaches for creating a BISM in SSAS 2012, Tabular vs. Multidimensional vs. PowerPivot

Installing SQL Server Reporting Services (SSRS) 2012 integrated mode with SharePoint 2013

Reporting services 2012 integrated with SharePoint 2013

 

In this post I’m going to describe installing SSRS 2012 integrated mode with SharePoint 2013. As some guys are pretty new to SSRS 2012 or SharePoint or even SSRS integrated with SharePoint, I am explaining the situation from scratch.


Requirements:

Note: Reporting services will not install on standard or express editions

  • Windows Server 2008 or later

Note: You can download Windows Server 2008 R2 180 days trial from here: http://www.microsoft.com/en-us/download/details.aspx?id=11093 and Windows Server 2012 from here: http://www.microsoft.com/en-us/server-cloud/windows-server/trial.aspx

Note: SharePoint 2013 is available only in 64-bit version, so you need a 64-bit operating system.

Continue reading Installing SQL Server Reporting Services (SSRS) 2012 integrated mode with SharePoint 2013