Today the subject of investigation is the Temporal Table, which is a new feature in SQL Server 2016. My focus will slightly be on how to use it in Data Warehouse environments, but there is some general information passing by as I write.
Read more »
Business Intelligence
How to automate SSAS tabular model processing in SQL Server 2016
March 6, 2017There are many ways to process your SSAS Tabular Model. This can be achieved in SSIS using the Analysis Services Execute DDL Task or manually, through Management studio GUI but to have a little fun & make the task more flexible I’m going to script this with ASSL/TMSL & build a notification round it. We can then schedule this as a step in a SQL agent job, call it from SSIS or PowerShell.
Read more »
Benefits of Partitioning an SSAS Multidimensional Cube
February 28, 2017Introduction
In the article How to partition an SSAS Cube in Analysis Services Multidimensional, we explained how you can partition your measure groups in an SSAS cube. In this article, we’ll look at the expected benefits of the partition strategy. Time to reap the benefits of our hard work.
Read more »
How to partition an SSAS Cube in Analysis Services Multidimensional
February 28, 2017Partitioning is the concept where you divide your data from one logical unit into separate physical chunks. This can have several advantages, such as improved performance or easier maintenance. You can for example partition a table in a SQL Server database, but you can also partition your measure groups inside an Analysis Services (SSAS) Multidimensional cube. In this article, we’ll discuss how you can set-up partitioning. For more information about the expected benefits, take a look at Benefits of Partitioning your SSAS Multidimensional Cube.
Read more »
How to retrieve information about SSIS packages stored in MSDB Database
February 21, 2017Introduction
Nowadays, most mid-size companies have implemented a Data Warehouse (DWH) solution. This DWH can be designed using a set of tools (or features) from Microsoft SQL Server. One of them is SQL Server Integration Services, also known as SSIS.
Read more »
Top 5 Deprecated Features in Reporting Services 2016
February 17, 2017It’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 »
SSRS Failed Subscription Alerting
February 10, 2017Even 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 »
How to resolve Excel Timeline Filter Errors caused by SQL Server OLAP Data Sources
January 31, 2017
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 »
Integration Services Performance Best Practices – Writing to the Destination
January 26, 2017With this article, we continue part 1 of common best practices to optimize the performance of Integration Services packages. As mentioned in the previous article “Integration Services (SSIS) Performance Best Practices – Data Flow Optimization“, it’s not an exhaustive list of all possible performance improvements for SSIS packages. It merely represents a set of best practices that will guide you through the most common development patterns.
Read more »
Integration Services Performance Best Practices – Data Flow Optimization
January 26, 2017In this article, we’ll present a couple of common best practices regarding the performance of Integration Services (SSIS) packages. These are general guidelines for package design and development which guarantee a good performance in most use cases. However, there are always exceptional cases and outliers. The mantra of “measure twice, cut once” also applies here. Thoroughly test any changes to your packages to conclude that a change made a positive effect. This means not only running the in the designer, but also on the server. Also, this article is not an exhaustive list of all possible performance improvements for SSIS packages. It merely represents a set of best practices that will guide you through the most common development patterns.
Read more »
How to administer SQL Server Reporting Services (SSRS) subscriptions using PowerShell
January 23, 2017In 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 »
Scaling out Reporting Services & changes in SQL Server 2016
January 18, 2017Scaling 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 »
Deploying Packages to SQL Server Integration Services Catalog (SSISDB)
January 16, 2017Starting 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 »
Parameterizing Database Connection in SQL Server Integration Services
January 12, 2017SQL 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 »
Report Subscription Changes in SQL Server Reporting Services 2016
December 30, 2016What 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 »
Script Task Debugging in SQL Server Integration Services (SSIS)
December 29, 2016Script Task
In SQL Server Integration services (SSIS), it may not be possible to meet all the business requirements using built-in tasks. In these cases, we can achieve the functionality using C# or VB.net code in script task. The script task is an ideal alternative solution to built-in tasks.
Read more »
Reporting in SQL Server – Using calculated Expressions within reports
December 19, 2016Introduction
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 »
How to use Expressions within SQL Server Reporting Services to create efficient reports
December 9, 2016Introduction
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 »
How to setup SQL Agent Job alerts to include SSIS catalog errors
December 7, 2016I 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 »
How to use SQL Server Analysis Services (SSAS) query results in Excel with Power Pivot
December 2, 2016Prerequisites 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 »
How to see the schema of a result set before running a query
November 30, 2016Introduction
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 »
Creating reports based on existing stored procedures with SQL Server Reporting Services
November 10, 2016Basic extraction of the SQL Server database data is usually achieved by querying the databases and creating stored procedure to automate the extraction process. Unfortunately, extracting the information in this way will not yield high-end reports, and only basic table-shaped reports are available when extracting the information via SQL Server Management Studio or similar tools. In order to create high-end reports that will include additional projections of the data, such are graphs, lists, charts… SQL Server offers powerful reporting options within the SQL Server Reporting service.
Read more »
How to use SQL Server Data Quality Services to ensure the correct aggregation of data
November 9, 2016Introduction
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 »
Two methods for restoring a data warehouse/data mart environment
November 8, 2016Implementing best data warehouse designs and practices such as data lineage reduces the need to ever have to restore an entire relational data warehouse. However, sometimes there are instances whereby you have inherited poorly designed data warehouse environments that leaves you with no other options but to perform an entire database restore in an event of a sudden disaster. I recently found myself in a similar situation of having to recover one of my data mart following a data integrity issue wherein all data of a type 1 dimension was updated/overwritten using an incorrect source file. In this article I take a look at how different approaches can be utilised to restore the compromised SQL Server-based data mart back to its “good state”.
Read more »
How to create an image cataloging system using SQL Server Reporting Services
November 2, 2016Introduction
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 »