Category : MSBI


Work offline with SSIS package

Sometimes it happens that we need to open an SSIS package which contains connections which are not accessible or not allowed to be accessed due to some security reasons. In this post “Work offline with SSIS package”, we are going to learn that how we can open, read, and modify the components of an SSIS package when its connection managers are not able to connect to the underlying data source. This feature can be highly useful when it comes that there is a developer who is not authorized to access the underlying data source being used in the package but he needs to read some column mappings information from the source and destinations being used in the package.

Whenever we open an SSIS package in Visual Studio designer, the designer tries to connect to the data sources being used by the package to verify the metadata. And if it fails … More


Checkpoints with containers in SSIS

In the previous post “Using checkpoints in an SSIS package“, we discussed how to configure an SSIS package in order to enable the checkpoints. Now, In this post “Checkpoints with containers in SSIS“, we are going learn how to configure a package to restart from the first task of the container in which it fails instead of the beginning of the package.

Most of the time, in an SSIS package, containers are used to group a set of tasks in a single unit of work and if the package gets failed, we need to restart the package from the beginning of the container instead of restarting it from the beginning of the package. Let’s create a dummy package and demonstrate it.

Using checkpoints with containers in an SSIS package – Demo

In this demonstration, we are going to use sequence container to group multiple tasks in … More


Using checkpoints in an SSIS package

Checkpoints in SSIS

In this post “Using checkpoints in an SSIS package”, we are going to learn how to configure checkpoints in an SSIS package.

SQL Server integration services use checkpoints in order to avoid the re-execution of an SSIS package from the beginning in case the package fails during execution. Checkpoints can be extremely useful in case the task prior to the point of failure takes a long time to execute. For example, if we are dealing with an SSIS package which is loading a huge amount of data from an OLTP server (highly transactional) to a destination server and is taking a long time to accomplish the data loading operation, and if the package gets failed after the data loading operation (at any other task being executed later on), we would not like to restart the package from the beginning which will take a long time to reload … More


Get error column name in Data Flow Task in SSIS

How to get error column name and error description in Data Flow Task in SSIS

During execution of an SSIS package, when a bad row comes in the data flow task, the task gets failed. However, most of the components (source, transformation, and destination) in the data flow task exposes an error output path which can be configured to redirect these invalid records to the error output path and then we can log these redirected bad records using a destination component. Once all these bad records get logged, we can investigate the root cause of the errors, fix them, and then these bad rows can be reintroduced again.

The error output path in the data flow task contains two four byte int columns, ErrorColumn and ErrorCode, which represent the lineage id of the error column and the error code respectively. The values in both these columns are numeric which … More


Dynamic SQL in OLEDB source component in SSIS 1

The metadata could not be determined because statement contains dynamic SQL

When we use a batch of T-SQL statements which contains dynamic SQL or uses temporary tables inside, in an OLEDB source component placed in a Data Flow Task of a SSIS package, we get an error during column names extraction because the source component gets failed to fetch the metadata (columns and their data types) for the given T-SQL batch.

In this post, we are going to learn how we can use a dynamic SQL statement or a batch of T-SQL statement which does not expose its metadata directly, in a source component which could be an OLEDB source, or an ODBC source being used to fetch data from the SQL Server.

Follow below steps to generate the error:

  • Create an integration services project in visual studio.
  • Add a Data Flow Task in the package.
  • Create an OLEDB connection
More

Sort data in Power BI

This post “Sort data in Power BI” explains how we can sort Power BI visual data by a column which is being used in the visual itself or by some other column which is not being used in the visual itself. We know that Power BI desktop tool provides set of functionalities to create flashy and interactive reports. It supports direct connectivity with many data sources, to know more about Power BI desktop application, visit our previous post “Rise of the Power BI“, to explore drill down functionality in Power BI, visit “Drill down report in Power BI” post.

To demonstrate Power BI visual data sorting, we are going to use below sample query which is extracting internet sales data along with sales territory from AdventureWorksDW2014 database:

SELECT
DST.SalesTerritoryGroup AS GroupName, DST.SalesTerritoryCountry AS CountryName, DST.SalesTerritoryRegion AS RegionName,
ROUND(SUM(FIS.SalesAmount), 2) AS SalesAmount
FROM FactInternetSales FIS
More

Drill down report in Power BI

In previous post “Rise of the Power BI“, we have discussed about the Power BI and it’s components. In this post “Drill down report in Power BI”, we are going to learn how we can create a drill down report in Power BI.

To demonstrate the Power BI drill down functionality, we are loading the sample data using a SQL query from “AdventureWorks2014” database. Power BI desktop provides a direct connectivity to SQL Server which we are going to use in this tutorial.

Lets, load the data into power BI to create a drill down report:

Loading data into Power BI from SQL Server

To create a SQL Server connection to pull data from AdventureWorks2014 Database, follow below steps:

  1. Open Power BI Desktop application.
  2. A dialog box will appear at startup screen, click on “Get Data” button appearing at left of the dialog box.
More

Rise of the Power BI 1

What is Power BI?

Power BI is a new cloud first self service analytic tool introduced by Microsoft which empowers us to bring our whole business data at one place for analysis purpose. It provides an easy way to pull data from multiple data sources which includes but not limited to files, databases, azure databases, Hadoop files, websites and many more.

Power BI has various components, from which Power BI Desktop tool is used to create, publish and share interactive and stunning reports. Mobile users can download the Power BI Mobile application to view these published dashboards on their mobile devices. All together, it’s a new era of interactive and mobile friendly cloud based self service reporting.

It’s a free reporting solution from Microsoft which can be used by anyone, anytime, anywhere.

Why Power BI?

Microsoft is providing a complete enterprise BI solution to the industries since more than a … More


Change chart type dynamically SSRS

Today, we are going to learn how we can change chart type dynamically depending on the selected value in the report parameter in SSRS (SQL Server Reporting Services) report. Report parameter has few predefined chart type as values in the report, and depending on the selected value in the parameter, chart renders in the report.

We know that there is no any inbuilt way which can be used to change the chart type at run time in SSRS and to achieve this we need to forge a couple of things. In this post we will discuss a method which can be used to achieve this. For demo, I am using AdventureWorks2014 database in this tutorial. I have attached the zipped project file which can be downloaded from here or from the link provided at the bottom end of this post.

Let’s create a dummy project in SSDT to demonstrate how … More


Role playing dimension

We have explored Junk dimension and Degenerate dimension in previous posts, now we are going to discuss Role playing dimension in this post. Lets start with the definition followed by an example.

Role playing dimension

A fact table keeps the facts of a business process. It is built of two types of columns: measure columns and dimension key columns. Measure are the quantitative business data about the business process and dimension keys reference to the dimension tables which hold the textual attributes of the business process.

Typically a dimension key column of a fact table refers a dimension table but its also very common where multiple columns of a fact table refer to a single dimension table. When a single dimension table is linked from multiple dimension key columns of a fact table, that dimension table is known as role playing dimension. As multiple columns of a fact table … More