Business Intelligence

Steve Simon

Big Bonus Anyone?

December 19, 2016 by

Introduction

Late in October, I received an unusual request from the head of sales within one of my client sites. Sales sells three articles: bread, perfume and Jaguar motor cars. Now the reader will note that one of these items is a staple and the other two are for those folks with considerable disposable income. Management within the firm had increased the salesmen’s bonuses for those folks that managed to sell perfume and / or Jaguars along with the standard loaves of bread. The summary report may be seen below showing the final bonus rate for each sales order booked during the month.

Read more »
Daniel Tikvicki

Introduction to SQL Server Analysis Services (SSAS) monitoring

December 15, 2016 by

When it comes to monitoring of SQL Server Analysis Services (SSAS) performance, as it relates to the database engine, there are several categories that should be mentioned and compared.

Read more »
Steve Simon

A story of Whigs and Tories

December 9, 2016 by

Introduction

As many of you by now know, I am a fan of utilizing expressions within Reporting Services reports to add additional flexibility. Recently I received a client request to create a stacked bar report which in turn would provide access to the underlying data the made up the bars on the chart. My client sells two articles: “Whigs” and “Tories”. Many people like to “buy” them .The idea is to display the aggregated data as may be seen below (grey black and turquoise) and depending upon which bar and color is selected( clicked upon), to drill down and to display the underlying detailed data (see below).

Read more »
Sifiso W. Ndlovu

How to setup SQL Agent Job alerts to include SSIS catalog errors

December 7, 2016 by

I must have been one of the first people who abandoned BIDS as soon as SSDT was first introduced. Although I have never regretted that decision, I do sometimes feel that SSDT has introduced an unnecessary additional layer of troubleshooting package execution failures. Regardless of whether your SSIS packages are deployed using BIDS or SSDT, the common scheduling mechanism used to run those packages is usually the SQL Server Agent. This is where you are likely to encounter additional layers of troubleshooting SSDT-deployed packages. This is because unlike BIDS where package execution details were stored in the same database (msdb) as were the execution details of SQL Server Agent job, the advent of SSDT came with an introduction a new database – SSISDB – which is used to, amongst other things, store package execution details. Subsequently, in order to retrieve details of package execution, we ought to query the SSIDB database.

Read more »
Daniel Tikvicki

How to use SQL Server Analysis Services (SSAS) query results in Excel with Power Pivot

December 2, 2016 by

Prerequisites for using query results from any Analysis Services model database in Excel through Power Pivot add-in are SQL Server 2014 or higher with installed Analysis Services Tabular instance (Multidimensional is installed by default), deployed Multidimensional or Tabular model database (for data source) and Power Pivot add-in for Excel 2010 or higher (Power Pivot is native add-in in Excel 2016).

In this article, the custom SSAS Tabular model database will be used (“AW Tabular”) as an external data source.

Read more »
Steve Simon

With a little help from my friends (DQS)

November 9, 2016 by

Introduction

An interesting opportunity arose at a client site during early October which provided a phenomenal opportunity to do a Data Quality Services implementation. My client (a grocer) had been requested to produce summary reports detailing the amount of funds spent during 2016 (YTD) with the myriad of manufacturers from whom the chain purchases their inventory. All “accounts payable” entries are done manually and as such are prone to errors.

Read more »
Steve Simon

A festive gift: Working with images

November 2, 2016 by

Introduction

With Christmas just around the corner, in today’s “get together” I thought that we would have some fun by cataloging a collection of ‘your favorite items’. Whether it be a coin collection, a china plate collection or a stamp collection, the process is the same and certainly something that you will enjoy creating and maintaining. For today’s example, we are going to construct a “Postage Stamp” cataloguing system. We are going to see how we are able to get from this..

Read more »
Steve Simon

Reporting from OLAP cubes made easy

October 19, 2016 by

Introduction

Last month I ran two Business Intelligence pre-conferences in South Africa. A interesting request arose during the course of the preconference in Cape Town. The individual wanted an approach to extracting data from an OLAP cube that would avoid intensive utilization of MDX and more reliance upon T-SQL. His main concern was with filtering the data at run time, via the report front end.

In this “fire side chat” we shall do just that, utilizing the cube that comes with the new Microsoft database “WideWorldImporters” and we shall learn how we can get from this

Read more »
Steve Simon

Bars and stripes: Visual displays that you can count on!

October 3, 2016 by

Introduction

As we are nearing the end of the North American summer, I thought that we would take a lighter look at nifty ways of reporting information. In today’s “fireside chat” we have a look at a unique way of displaying our information using charts and line graphs all in one single chart (see below). We shall take things one step further and work with the colour fill of the vertical bars to reflect the values that they represent.

Read more »
Sifiso W. Ndlovu

How to filter multidimensional OLAP cubes in SSRS reports

September 28, 2016 by

Ever since the early days of my career, SQL Server Reporting Services (SSRS) has been one of my preferred data visualisation tools simply because end users and developers alike use it for free. Although a majority of my SSRS solutions have been based off a relational dataset that uses Transact SQL (T-SQL), I have also produced several reports that used Multidimensional Expressions (MDX) to connect and retrieve data from SQL Server Analysis Services (SSAS) multidimensional OLAP cube. Recently, I found myself having to refactor some of these SSAS based SSRS reports, particularly converting a single value SSAS-populated parameter into a multi-value parameter. In this article, I explore how you can go about making these changes using SSRS query designer’s design and query modes.

Read more »
Sifiso W. Ndlovu

How to use SQL Server Reporting Services (SSRS) to execute SQL Agent Jobs

August 22, 2016 by

Every production ETL (Extract, Transform, Load) solution is often intrinsically linked to a scheduling mechanism that is used to execute that ETL solution. In a SQL Server-based environment, SQL Server Agent is one of the scheduling mechanism that can be utilised to schedule an execution of ETL solutions such as SQL Server Integration Service (SSIS) packages. In the organisation that I currently work for, we’ve had several instances (for various reasons) whereby as the data team we’ve been required to provide a platform for business users to execute an ETL at their own convenience (i.e. on-demand). In this article, we will demonstrate on how we went about delivering self-service ETL execution requirement.

Read more »
Steve Simon

Smart charts and tablices

August 4, 2016 by

Introduction

In our last “fire side chat” we discussed a few of the challenges that the HR Manager of a major hardware chain was experiencing. Mary Smith, the HR manager has since approached us to modify her existing reports to function more efficiently and effectively by utilizing her existing data, yet reduce the total number of reports.

Read more »
Page 1 of 41234