Business Intelligence

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 »
Thomas LeBlanc

Deploying Packages to SQL Server Integration Services Catalog (SSISDB)

January 16, 2017 by

Starting with SQL Server 2012, Integration Services (SSIS) packages can now be deployed to a single source for managing execution in multiple environments. The SSIS Catalog is a single database container for all deployed packages. The configuration files are replaced with Environments. Deployed versions are tracked historically and a package can be reverted to a previous deployment. On top of these features, internal reports with a dashboard help when debugging errors or examining performance over time.

Read more »
Thomas LeBlanc

Parameterizing Database Connection in SQL Server Integration Services

January 12, 2017 by

SQL Server Integration Services (SSIS) has been growing into an enterprise solution over the last three releases. Variables were a step in that direction, but with Project deployments to the SSIS Catalog in SQL Server 2012 Microsoft has stepped it up, even more. Project parameters have become the go to solution for changing variable values in packages at run time. This article is going to dive into an example that parameterizes the Server Name and Initial Catalog (database name) in a connection string for packages in a project.

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 »
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 »
Gerald Britton

How to see the schema of a result set before running a query

November 30, 2016 by

Introduction

Suppose you’ve been asked to run a query against some SQL Server database but you don’t know anything more than the name of a stored procedure that you need to call. If you are going to do anything with the result set, you need to know at least the names and types of the columns to expect. This is actually a problem faced by many applications, including SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS).

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 »
Steve Simon

Reporting in SQL Server – Customize the visual appearance of your reports

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 color 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 visualization 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 »
Page 3 of 712345...Last »