Business Intelligence

Sifiso Ndlovu

Using SSIS ForEach Loop containers to process files in Date Order

August 3, 2017 by

One positive thing to come out of my recent project that involved rewriting one of the Data Marts from our Data Warehouse environment was a confirmation of my suspicions with regards to the behavior of SQL Server Integration Services’ (SSIS) ForEach Loop Container. You see, I have long suspected that the ForEach File Enumerator type in SSIS’s ForEach Loop Container does not process time stamped text files in an order that could be deemed correct to the human eye. For instance, Figure 1 shows a list of text files containing data relating to Marital Statuses of FIFA 2016 Ballon D’Or nominees.

Read more »
Craig Porteous
C:\Users\craig.CRAIGFLIX\AppData\Local\Microsoft\Windows\INetCache\Content.Word\SSMS_17.png

What’s New in Reporting Services (SSRS) 2017

July 26, 2017 by

To further the discussion related to Mohamed’s post on Reporting Services 2016 What’s new in SQL Server 2016 Reporting Services (SSRS), I wanted to cover the new features and functionality we see in Reporting Services 2017 as RC1 was released last week SQL Server 2017 Reporting Services Release Candidate now available, separately from SQL Server 2017 which reached RC1 a few days prior.

Read more »
Thomas LeBlanc

Analysis Services (SSAS) Cubes – Dimension Attributes and Hierarchies

July 5, 2017 by

In Data Warehouse language, slicing and dicing is done with Dimension Attributes. Sometime a developer feels the need to provide everything to end users, whereas seasoned Business Intelligence Architects understand to provide only the attributes from the requirements. It is a hard path to follow and the seasoned architect’s experience might be the better option.

Read more »
Minette Steynberg

Reporting in SQL Server – Power BI Report Server

June 13, 2017 by

Power BI is a self-service business intelligence tool from Microsoft which has been steadily gaining momentum in the last couple of months. One of the well-known disadvantages of Power BI is that it is basically cloud only. A lot of companies are not yet at the point where they feel comfortable having their data in the cloud or are premise bound for some other reasons such as data-sensitivity, data-sovereignty or compliance.

Read more »
Craig Porteous

Considerations for On-premises data in PowerBI

June 7, 2017 by

Whether you’ve been using PowerBI for a while or you’re just getting started with it, chances are you’ll want to source at least some of your data from on-premises systems. Be that SQL server databases, Oracle, SSAS etc. The same process even applies if you’re sourcing from AWS which I’ll go into in more detail below. All of these data sources will require Microsoft’s On-Premises Data Gateway.

Read more »
Thomas LeBlanc

Initial Attributes and Measures in SSAS Multidimensional Cubes

June 2, 2017 by

Connecting to tables in SQL Server Analysis Service (SSAS) databases is easy. Maybe too easy. The learning curve for developing in a Multidimensional Cube is steep, although a Tabular Model has lessened this curve. The power of encapsulating the business logic of aggregations and analytics into an Analysis Service database is too great not to embrace Analysis Services in an enterprise environment.

Read more »
Koen Verbeeck

Analysis Services (SSAS) Multidimensional Design Tips – Creating Dimensions

June 1, 2017 by

In the previous article, Analysis Services (SSAS) Multidimensional Design Tips – Data Source View and Cubes, we discussed best practices for SSAS Multidimensional cubes and data source views. In this article, we continue the series with design tips for creating dimensions as the subject. As with the previous article, most tips are suited for SSAS 2008 (most likely 2005 as well) to 2016 and later versions. Analysis Services Tabular is not covered in this article.

Read more »
Sifiso Ndlovu

How to Avoid Package Design Flaws When Sourcing Data From Flat Files

May 10, 2017 by

As developers of SQL Server Integrations Services (SSIS) solutions, we have more than likely configured Flat File Connections as sources in our Data Flow Tasks. Whilst any unforeseen changes to the structure and formatting of flat files will invariably cause SSIS packages to fail, as developers we can still do a lot in reducing unnecessary SSIS package failures relating to data coming out of flat files. In this article, we offer recommended development practices to some flat file source errors that occur as a result of poor SSIS development practices.

Read more »
Sifiso Ndlovu

How to use Columnstore Indexes to improve your Data Warehouse Staging Environment

May 4, 2017 by

My team and I were recently tasked with refactoring older data marts, particularly those that were created with SQL Server 2008 in mind. As we all know, SQL Server has undergone significant changes since the release of SQL Server 2008. One of those changes relates to the introduction of columnstore as an alternative to the traditional B-tree index (rowstore). Whilst most of the existing documentation relating to columnstore seem to focus on the benefit of columnstore against data warehouse workloads, in this article I argue that the usage of columnstore index should not be limited to facts and dimensions instead let’s introduce it in our data warehouse staging environments too.

Read more »
Koen Verbeeck

Analysis Services (SSAS) Multidimensional Design Tips – Data Source View and Cubes

April 28, 2017 by

In this article, we’ll discuss some tips and best practices regarding the design of OLAP cubes in Analysis Services Multidimensional (SSAS). Most tips – if not all – are applicable for SSAS 2008 to 2016 (and later most likely). Since Analysis Services Tabular – the in-memory columnstore OLAP database from Microsoft – is a completely different design experience, it will not be included in this article.

Read more »
Daniel Calbimonte

How to migrate MySQL tables to SQL Server using the SQL Server Migration Assistant (SSMA) and SSIS

April 25, 2017 by

Introduction

In this article, we will demonstrate how to migrate MySQL tables to SQL Server using the SQL Server Migration Assistant (SSMA) and SSIS. We will give a brief introduction to MySQL and create a table. If you do not have any experience in MySQL, this tutorial will teach you your first steps. After that, we will migrate the created table and existing tables. We will also mention common problems when we migrate tables.

Read more »
Thomas LeBlanc

Connecting to Data Source(s) with SQL Server Analysis Services

April 18, 2017 by

For someone new to SQL Server Analysis Services, a decision must be made on whether to use a Multidimensional Cube or Tabular Model. Both types can be installed on the same server, but they must be different instances. That could mean additional license costs. But, with the developer’s edition, both can be installed and reviewed before a production decision is made. Data Source connections are the first step to review with both versions.

Read more »
Andrea Martorana Tusa

How to create geographic maps using Power BI – Filled and bubble maps

April 5, 2017 by

The project

This is the first article of a series dedicated to discovering geographic maps in Power BI using Bubble Map and Filled Map.

My goal is to illustrate to you the different visuals available in Power BI for making maps. Remember that Power BI is a product undergoing constant evolution; new features are added every month at a fast pace. And over time Power BI has gained more mapping capabilities compared to the starting point.

Read more »
Thomas LeBlanc

SQL Server Data Warehouse design best practice for Analysis Services (SSAS)

April 4, 2017 by

Before jumping into creating a cube or tabular model in Analysis Service, the database used as source data should be well structured using best practices for data modeling. Some might say use Dimensional Modeling or Inmon’s data warehouse concepts while others say go with the future, Data Vault. No matter what conceptual path is taken, the tables can be well structured with the proper data types, sizes and constraints.

Read more »
Koen Verbeeck

How to optimize the dimension security performance using partitioning in SSAS Multidimensional

March 27, 2017 by

Introduction

In the articles How to partition an SSAS Cube in Analysis Services Multidimensional and Benefits of Partitioning an SSAS Multidimensional Cube, the concept of measure group partitioning is introduced and the advantages are clearly illustrated. One of the biggest advantages of partitioning is partition elimination, where only the partitions necessary to satisfy the query are read instead of all the data.

Read more »
Thomas LeBlanc

How to execute a Deployed Package from the SSIS Catalog with various options

March 21, 2017 by

In my previous two articles on SQL Server integration Services (SSIS), Parameterizing Database Connection in SSIS and Deploying Packages to SSIS Catalog (SSISDB), packages were developed, deployed and configured in the SSIS Catalog. Now, it is time to execute the packages with various options. There are a couple of ways to do this, but we need to be able to change the parameter values as well as monitor for failures or successes.

Read more »
Sifiso Ndlovu

The evolution of SQL Server Data Tools (SSDT) for Business Intelligence development

March 9, 2017 by

By all accounts, the introduction of SQL Server Data Tools (SSDT) in SQL Server 2012 was a watershed moment for many SQL Server developers. For better or for worse, SSDT as an IDE for business intelligence development changed – amongst other things – the way we deployed our SSIS packages (i.e. package vs project deployments), simplified Tabular Model development, and also introduced us to the SSISDB. Likewise the replacement of Business Intelligence Development Studio (BIDS) with SSDT had its detractors who were noticeably not very happy that in addition to installing SQL Server 2012 you still had to do a separate download and installation of BI templates for SSDT (previously, BI templates in BIDS were available as soon as you installed SQL Server 2005/2008). Although SSDT-BI is still being offered as a separate installation, subsequent releases of SSDT have included several enhancements changes that should go a long way to winning the hearts of its critics. In this article we conduct a comparative analysis of all versions (up until 16.5) of SSDT and identify all the major improvements that have been introduced in the BI templates.

Read more »