Business Intelligence

Sifiso W. Ndlovu
Data imported using Custom SQL Statement.

Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events

July 1, 2019 by

One shared characteristic among popular data visualization tools such as Power BI is the ease at which data can be extracted from a variety of disparate data sources, usually at a click of a button, as shown in Figure 1. Such convenience, though, tends to come at a cost as you often have little control over how background scripts used to extract data are generated. Yet, this should be of utmost concern for data architects and BI developers alike as rarely do you find auto-generated scripts that are efficient and optimal. In this article, join me as I put on my DBA hat and trace, monitor and review SQL batch statements that are auto-generated by the Get Data feature in Power BI using SQL Server Extended Events.

Read more »
Sifiso W. Ndlovu
Successful Execution of SSIS components

Bulk-Model Migration in SQL Server Master Data Services

May 30, 2019 by

Just over a year ago, I published How to migrate SQL Server 2017 Master Data Services Models into another server article, that essentially demonstrated the migration of a SQL Server Master Data Services (MDS) model from one MDS instance to another. Like many of my previously published articles, I have since used the aforementioned article as a technical reference (i.e. syntax lookup for using MDSModelDeploy.exe utility) during the implementation of MDS-related projects for my clients.

Read more »
Sifiso W. Ndlovu
Choose the Power BI Dashboard you want to add

Using Power BI Free License to Embed Power BI into Dynamics 365

May 24, 2019 by

Whilst Power BI continues to be the leading data visualisation tool in Gartner’s BI quadrant, Dynamics 365 for Customer Engagement (formerly Dynamics CRM) remains one of popular business platforms for driving sales and facilitating customer communications. Thus, an integration of Power BI into Dynamics 365 for Customer Engagement ensures that you get to explore your Dynamics 365 for Customer Engagement data via a powerful and rich data visualisation tool. However, as indicated in Figure 1, Dynamics 365 for Customer Engagement plans often come with a Power BI free license thereby limiting the type of Power BI features available to you. In this article, we look at how you can get the best out of your Power BI free license for the purposes of embedding Power BI artefacts into Dynamics 365 for Customer Engagement. Going forward, any references to Dynamics 365 for Customer Engagement plan in this article will be shortened to Dynamics 365.

Read more »
Rajendra Gupta
Importing compressed data into SQL Server: Add an OLE DB destination

Importing Data into SQL Server from Compressed Files

March 5, 2019 by

Introduction

I have seen many organizations receive data from various sources and import into SQL Server. You might receive data in various formats and want to import into SQL Server. We can prepare a ETL (Extract-Transform-Load) process to import data into the SQL Server. In doing so, might receive data in a compressed file, which helps to send data over the network using a ZIP file format because it reduces the file size significantly. If we are receiving a ZIP file to import into SQL Server, we need to unzip it and then only we can import data. We might need to create a ZIP file as well from the existing files.

Read more »
Esat Erkec
SQL Server Reporting Services - Import Flat File

SSRS Report Builder introduction and tutorial

March 1, 2019 by

The SSRS report builder is a powerful tool for data visualization. In this article, we will take a first step into familiarizing ourselves with the SSRS report builder and we will also demonstrate a very basic SSRS report example. Additionally, we will review the self-service business intelligence approach just because this approach can be implemented through the SSRS report builder very easily.

Read more »
Rajendra Gupta
Selecting the Pie Charts Tree in thhe Power BI application marketplace

Power BI Desktop Pie Chart Tree

February 27, 2019 by

Power BI Desktop amplifies the value of data. We can connect to multiple data sources and visualize the data interactively. One of the best parts of Power BI Desktop is the custom visual feature. It allows developers to create custom visuals and users can then download these visuals from the marketplace and use it as per the data set requirements. We can prepare reports using Power BI Desktop and share the reports using the Power BI service.

Read more »
Rajendra Gupta

Export SQL Server FILESTREAM Objects with PowerShell and SSIS

February 14, 2019 by

In this series of articles on SQL Server FILESTREAM (see TOC at bottom), we explored various ways to store unstructured data in the file system with the metadata in SQL Server tables. If we have a large number of objects in the file system, it is advisable to use the fast disk for storage purpose. It is faster and provides better IO in comparison with the traditional file system.

Rajendra Gupta

Power BI Desktop and Python; like Peanut Butter and Chocolate

February 13, 2019 by

Power BI Desktop helps to visualize complex data with the help of inbuilt and custom visualizations. It allows integrating data from the various data sources and preparing visualization from it.

Microsoft recently integrated the Python programming language in the Power BI. Python is a powerful object-oriented language and is used by the data analyst and scientist to analyze complex datasets. We can now use the Python as a preview feature in the Power BI August 2018 release onwards. We can now use Python scripts to generate custom visualization. The combination of these technologies is akin to 1 + 1 = 3 or like the famous ad for Reese’s Peanut butter cups, when chocolate was introduced to Peanut Butter 😉

Read more »
Esat Erkec

SQL Unit Testing with SSDT (SQL Server Data Tools)

February 12, 2019 by

In this article, we will discuss the essentials of the SQL unit testing and then we will explore how to apply SQL unit testing methodology in Azure SQL Database with help of SQL Server Data Tools (SSDT). In particular, this article will focus about unit test theory in terms of SQL Server database development aspect and illustrate SQL unit testing with a detailed example.

Read more »
Rajendra Gupta

Importing SQL Server FILESTREAM data with SSIS packages

February 1, 2019 by

Initial configuration

We have been exploring the SQL Server FILESTREAM feature in this ongoing series of articles. In this previous article, Managing data with SQL Server FILESTREAM tables, we wrote about inserting FILESTREAM data into a FILESTREAM table and performing DML activities on it. Suppose we have created the FILESTREAM database in our instance and now we want to insert a large number of files into a FILESTREAM container. It is easy to write out the insert queries for a small number of files, but if the numbers of files were in huge quantity, it would be difficult to write out the code and insert data into it. It is difficult to manage such kind of requests regularly in the environment.

Read more »
Ben Richardson

Importing and Working with CSV Files in SQL Server

January 28, 2019 by

Introduction

CSV (comma separated values) is one of the most popular formats for datasets used in machine learning and data science. MS Excel can be used for basic manipulation of data in CSV format. We often need to execute complex SQL queries on CSV files, which is not possible with MS Excel. See this article for what is possible with Power BI.

However, before we can execute complex SQL queries on CSV files, we need to convert CSV files to data tables.

Read more »
Rajendra Gupta

Power BI Desktop Q&A data interaction examples

January 3, 2019 by

The Q&A feature in Power BI desktop provides a way to move towards Artificial Intelligence. In my previous article, we learned about the prominent Q&A in Power BI Desktop feature. We can get the desired result from Power BI Desktop by asking a question in Natural language. It converts the result set in tabular, chart format based on the requirement. It also helps users to build the question with its intelligence. It is one of the most incredible features of Power BI.

Rajendra Gupta

Flow Map Chart in Power BI Desktop

December 19, 2018 by

In this article, we will explore the Flow Map chart in Power BI Desktop. Power BI is helpful to visualize the data through various form of the inbuilt and the custom charts. We have explored few such important charts in previous articles. Below is the list of articles for reference

Sifiso W. Ndlovu

An MDS Driven Approach to a Turnaround Time Calculation in SQL Server

November 30, 2018 by

One calculation that you are almost guaranteed to have to produce in your career as a T-SQL developer relates to the calculation of a turnaround time. This is often a key KPI for measuring the performance of both individuals and teams, particularly when the business operates within a service-oriented sector i.e. customer support, transportation, healthcare etc. Turnaround time calculation does not only refer to business metrics rather any activity (i.e. ordering a pizza) with a recorded start and an end time can have its own turnaround time calculated. In this article we evaluate different options for calculating a turnaround time including using DATEDIFF function, creating your own user-defined function (UDF) as well as an integration with SQL Server Master Data Services.

Read more »
Esat Erkec

How to create an email alert in SQL Server Agent for job failures

November 28, 2018 by

SQL Server Agent is a Microsoft Windows service which helps to execute, schedule and automatize T-SQL queries, Integration Service Package, SQL Server Analysis Service queries, executable programs, operating system, and PowerShell commands. These actions which are performed by SQL Server can be called by the SQL Server Agent. Maybe, we can liken SQL Server Agent to an alarm clock because the agent will execute the scheduled task when the time comes

Read more »