Business Intelligence

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 »
Sifiso W. 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 »
Sifiso W. Ndlovu

Top 5 Deprecated Features in Reporting Services 2016

February 17, 2017 by

It’s not often that I write negative articles surrounding SQL Server’s latest release but ever since we upgraded one of our BI boxes to run SQL Server Reporting Services 2016 (SSRS 2016), I have picked up on some frustrations from my team when using the upgraded Report Manager portal due to the unavailability of features that used to exist in versions prior to SSRS 2016. I have since realized that in spite of the many exciting features and improvements introduced in SSRS 2016, there is a downside to this latest version of reporting services that is likely to leave many administrators frustrated.

Read more »
Craig Porteous

SSRS Failed Subscription Alerting

February 10, 2017 by

Even now, with SQL Server 2016 SP1 released only a few months ago, it baffles me that there is still no built-in functionality to alert admins or users of failed subscriptions in Reporting Services. We still rely on scripts like the one I’m about to describe or report recipients contacting administrators/helpdesks when their report emails don’t arrive or fail to appear in file shares. This is something that people have had to work around for years. There is some documentation that Microsoft provides to help you get started with monitoring subscriptions from the log files with direction on using PowerShell but it is not by any means a complete solution. (Monitor Reporting Services Subscriptions)

Read more »
Sifiso W. Ndlovu

How to resolve Excel Timeline Filter Errors caused by SQL Server OLAP Data Sources

January 31, 2017 by

Over the course of my career, I have developed and deployed to production several business intelligence solutions. These solutions have been consumed using numerous data visualisation tools. One of those data visualisation tools, is Microsoft Excel – which remains a popular data visualisations tool for many of my end users. Like many software products, newer version releases of Excel usually introduce new features. One such feature is the Timeline filter control which was first introduced in Microsoft Office 2013. The Timeline filter control enables end users to visually filter Excel PivotTables using fields defined as dates. The Timeline filter has several benefits compared to the traditional Excel dropdown filter. Figure 1 illustrates one of those benefits whereby the Timeline filter allows end-users to easily apply a filter (i.e. using a scroll bar) again a PivotTable. It also has useful annotations that describes what you have filtered on – in this case, we have chosen sales period between February and March 2011.

Read more »
Sifiso W. Ndlovu

How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell

January 23, 2017 by

In the article Report Subscription Changes in SQL Server Reporting Services 2016, I covered several changes to standard and data-driven subscriptions that were introduced in the release of SQL Server 2016. However all of those changes related to administering report subscriptions using a GUI (i.e. Report Manager Portal, SSRS Configuration Manager). The release of SQL Server Core, SQL Server on Linux and enhancements on Windows PowerShell, reinforces the growing shift by Microsoft to have SQL Server professionals adopt scripting as one of the ways to carry out their daily tasks. In this article we continue to explore SQL Server Reporting Services (SSRS) subscription feature by discussing some of the Windows PowerShell commands that can be utilised to administer report subscriptions.

Read more »
Craig Porteous

Scaling out Reporting Services & changes in SQL Server 2016

January 18, 2017 by

Scaling out Reporting services to multiple nodes, in itself, is a relatively simple process. It’s when we come to solve problems and investigate performance that we begin to see there is a lot more going on under the hood that’s not clear through implementation. There are also some foundational elements that have changed in SQL 2016 with next to no guidance from Microsoft on the changes.

Read more »
Sifiso W. Ndlovu

Report Subscription Changes in SQL Server Reporting Services 2016

December 30, 2016 by

What makes SQL Server 2016 one of my favorite SQL Server release since the release of SQL Server 2012 is the increased number of exciting new features that have been introduced. Whilst my article Top 5 New SQL Server 2016 DMVs for DBAs reviewed some of these new features albeit from a DBA point of view, in this article I continue to review SQL Server 2016 but from the perspective of an SSRS administrator in relation to the changes in report subscriptions.

Read more »
Steve Simon

Reporting in SQL Server – Using calculated Expressions within reports

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

How to use Expressions within SQL Server Reporting Services to create efficient reports

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 (gray black and turquoise) and depending on 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

How to use SQL Server Data Quality Services to ensure the correct aggregation of data

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

How to create an image cataloging system using SQL Server Reporting Services

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” cataloging system. We are going to see how we are able to get from this…

Read more »
Rajendra Gupta

How to create and configure the Tree Map Chart in SQL Server 2016 Reporting Services

October 28, 2016 by

In the previous article, in this series, How to create and configure a Sunburst chart in SQL Server 2016 Reporting Services, we have explored new chart types in SQL Server 2016; the Sunburst chart and the Tree Map chart, that are used to display hierarchical data. The Sunburst chart is a way of presenting relational datasets together in a compact form. The Tree Map Chart is also a way of showing hierarchical data.

In this article, we are going to show how to create a Tree Map chart and its comparison with the Sunburst chart.

Read more »
Steve Simon

How to effectively extract data from an OLAP cube by relying upon T-SQL

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 »
Page 2 of 512345