Business Intelligence

Steve Simon

Using a cursor to correctly extract SQL Server data and place it in a Reporting Services matrix

February 23, 2015 by

Introduction

In our last two chats, we discussed enterprises that have had financial years that began in July and ended at the end of June. One of our clients works with this fiscal calendar and their financial folks are Excel “Fundi’s” (Fundisa is a Nguni word for “expert”). Many of their reports contain the current month’s sales, in addition, carrying running totals from the beginning of the fiscal year to date. Read more »

Steve Simon

Using Master Data Services in SQL Server to quickly create a GUI that may be maintained by the end user

February 20, 2015 by

Introduction

Oft times we are forced into situations where we clearly need to think outside of the box. A case at hand arose early in 2014 where one of our client’s required a “quick and dirty” front end to modify data within a table that reflected the outstanding balances (of their clients) and the attempts that they had made to recover these funds. Master Data Services seemed to be the way to go!

Read more »
Steve Simon

Creating dynamically generated CSV files containing SQL Server data

February 12, 2015 by

Introduction

A few months back, I presented a paper at SQL Saturday 327 in Johannesburg, South Africa. Late last month I received an email from one of the attendees. His issue was quite interesting and I decided to share it with you. The gentleman wanted a SSIS script that would permit him to extract data from a SQL Server database table and place it in a CSV file with a dynamically allocated name. Being a strong advocate of using the SSIS toolbox, I experimented with an alternative solution. We are going to construct THIS SOLUTION in today’s get together.

Let’s get started.

Read more »
Steve Simon

SQL Server Reporting Services Best Practices

February 11, 2015 by

Introduction

In past chats, we have had a look at a myriad of different business intelligence techniques that one can utilize to turn data into information. In today’s “get together” we are going to try to pull all these techniques together, rationalize our development plans, and moreover, look at some good habits to adopt or for the want of better words utilize SQL Server Reporting Services Best Practices.

Read more »
Régis Baccaro

Continuous Deployment using SQL Server Data Tools and Visual Studio Online

February 2, 2015 by

In the previous posts

Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file
Deployment to several databases using SQL Server Data Tools and Team foundation Server
Continuous integration with SQL Server Data Tools and Team Foundation Server

I have been mostly writing about the interaction between SQL Server Data Tools and Team Foundation Server. Microsoft provides a hosted version of Team Foundation Build Service called Visual Studio Online. The configuration and functionality is mostly the same than what I have previously been writing about but there are some specifics things that we need to be aware when using Visual Studio Online Build Service.

Read more »
Steve Simon

Getting started with data mining in SQL Server

January 19, 2015 by

Introduction

In past chats, we have had a look at a myriad of different Business Intelligence techniques that one can utilize to turn data into information. In today’s get together we are going to have a look at a technique dear to my heart and often overlooked. We are going to be looking at data mining with SQL Server, from soup to nuts.

Read more »
Régis Baccaro

Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file

January 16, 2015 by

In the previous blog post : Deployment to several databases using SQL Server Data Tools and Team foundation Server I illustrated how it is possible to use TFS and a batch file to deploy a database to several SQL Server instances or to deploy several SQL Server databases to several instances. The main way to achieve that in the previous post was using a batch file. For more information about this technique please have a look at that blog post.

In this post on the other hand I will demonstrate how the same functionality can be achieved using a Windows Workflow Foundation file (xaml) deployment file and Team Foundation Server.

Read more »
Steve Simon

How to utilize DAX queries against a Tabular Database

January 12, 2015 by

In an earlier “get together”, we had a quick look at the DAX language and how to construct useful queries. In today’s conversation we shall be concentrating on utilizing the knowledge that we obtained from the earlier article and seeing how these queries may be utilized for “multiple value” query selection criteria (against a tabular database).
Enough said, let us get started!

Read more »
Steve Simon

SQL Server security mechanism – How to control what data are users able to view within their reports

January 8, 2015 by

Introduction

A few years back, a client asked me to implement a quick and dirty “security mechanism” to control what data the myriad of users were able to view within their reports. There were numerous tables with multiple columns and all departments (within the enterprise) had their data within these tables.

SQLShack Industries has tasked us with creating a similar quick and dirty “security mechanism”. We shall attack this challenge by creating the necessary stored procedures (to extract the required data) and then utilize these stored procedures to render and consume the data within our reports.

Read more »
Steve Simon

Getting started with Data Analysis Expressions (DAX) in SQL Server

December 22, 2014 by

Introduction

In our SQLShack discussions over the past few weeks, we have dealt with a few of the more conventional SQL Server data manipulation techniques. Today we are going to be a bit more avant-garde and touch upon a subject dear to my heart.

With Power Bi becoming more and more important on the business side within major industries worldwide, it is not surprising that sooner or later every SQL programmer is going to have to learn and be able to ‘talk’ DAX.

In this article we are going to have a look at the a few of the more important ‘constructs’ and produce production grade queries for data extraction and for reports; enabling the reader to ‘hit the ground running’.

Read more »
Steve Simon

SQL Server 2014 Data Access Layers

December 17, 2014 by

Introduction

As a person who has always enjoyed finding new and innovative ways to perform tasks more efficiently and effectively with SQL Server, I have endeavored to document some of the more ‘innovative’ ways and means of doing these things in our SQLShack ‘get-togethers’.

Today, we shall be looking at one of my favourites, the ‘Data Access Layer’, not to be confused with ‘Data Access Layers’ from the Visual Studio world.

Read more »
Steve Simon

Automatically load data into a SQL Server database by utilizing the Visual Studio Project

December 12, 2014 by

Introduction

A few months back, I encountered an interesting challenge at a client site. For those of you whom have read my previous article entitled “Excel in loading multiple workbooks into SQL Server“, you will know that the challenge centered around loading the data from multiple spreadsheets into our SQLShack financial database.

Now, one of the enterprises business rules was that the loading of this data was NOT to occur before that last of the daily spreadsheets arrived in the common data repository.

Read more »
Steve Simon

How to reduce the report complexity using the “Visibility” options in SQL Server Data Tools

December 11, 2014 by

Introduction

Far too often we encounter clients that are really too keen to establish all inclusive reports for decision making purposes. While this is super (in principle) oft times these folks will inform you that all the data that is within the tables should be present within the report, and this is not always feasible nor practical.

After much thought I came up with an alternative to permit these folks to have their ‘cake and eat it’ and yet not render a cluttered report.

Read more »
Steve Simon

Excel in loading multiple workbooks into SQL Server

December 8, 2014 by

Introduction

A year or so ago, I was working on a project that revolved around daily data loads (from various asset management groups within an enterprise) into the main SQL Server data repository. Each group completed and published its own daily figures within their own Excel Work Books. These Excel workbooks were then placed in a common directory and then loaded into the Corporate SQL Server database. Let us have a look at how this may be achieved. In short, we are going to create one package that will process all the spreadsheets within the given directory. Read more »
Steve Simon

How to design a map-based report using Business Intelligence Semantic Model (BISM) and Excel

November 27, 2014 by

Introduction

One of a database designers’ worst nightmares is having to design a database for business analysts and data stewards whom insist upon creating their own reports, using Excel as a GUI. The reason that I mention this is that user created reports often open up “Pandora’s box”; with many of these folks creating their own ‘miss-information’ due to a lack of understanding of the underlying data. A few weeks back I had the ’fortune’ of working on such a project, which prompted an ‘ah-ha’ moment. I decided to design the backend SQL Server database using the Business Intelligence Semantic Model (BISM) and to employ the super set of tools provided by Microsoft Power BI, with Excel as a GUI. The end results were wildly accepted by the user community and once you see how easy this is to apply, you will be ‘chomping on the bit’ to employ the same techniques on your own user driven projects.

Read more »
Steve Simon

How to enhance your reports with SQL Server Reporting Services (SSRS)

November 18, 2014 by

Introduction

A few months ago, I was working on a few SQL Server reports for a client. The one request that I had received (from this client) was to ensure that the finished reports were as ‘all encompassing’ as possible, as they wanted to conduct a considerable amount of Business Analytics, via the reports.

Knowing this, I decided (where possible) to attempt to construct the reports so as to enable the firm to do their ‘what if’ scenarios with a minimal amount of time and effort.

The screen shot below is a sample of the final report AND we are going to look at the steps necessary to create this report. Stay with me!!!

Read more »
Régis Baccaro

Automating database tests with Visual Studio and Team Foundation Server

October 31, 2014 by

This is the third post in the series about database development and testing using SQL Server Data Tools and Team Foundation Server.

Post 1: Continuous Integration with SSDT and TFS

Post 2: Unit testing with SQL Server Data Tools

You can run test in Visual Studio to test the quality of your build. In VS 2012 and 2013 there are 5 kind of tests that are available for the user Read more »

Steve Simon

How to clean data using Data Quality Services and SQL Server Integration Services

October 29, 2014 by

Introduction

A year or so ago, I worked for an online web grocery software house located in the northern United States. At that time I had my ‘baptismal’ exposure to ‘genuinely dirty data’. Granted most of the data entry was done manually and many times from offshore. The point being that I could not fathom just how many ways there were to spell the brand name of a major cereal manufacturer. Why is this such an issue? The answer is fairly straight forward. Imagine the scenario that you are trying to ascertain the dollar value of breakfast cereals sold in the country from the local supermarket standpoint all the way up to national sales. Imagine this utilizing a SQL Server Multi-dimensional cube. The ‘eagle – eyed’ reader will recognize that the results will not aggregate correctly should our aggregation attributes have a plethora of different ways of being spelt. Read more »

Evan Barke

SQL Server Business Intelligence – Using recursive CTE and persisted computed columns to create a calendar table

June 2, 2014 by

Introduction

Those of you that have worked extensively with dates in SQL Server (or any other relational database management system (RDBMS)) will know how finicky and complicated it can be to use DATE functions, DATEPART, DATENAME, DATEADD, GETDATE(), CURRENT_TIMESTAMP etc. Personally, I find it very useful to have a calendar table that stocks all of the necessary, pre-calculated fields in one place. Whether you’re doing BI or web, it can be very helpful to have a fixed ID for a date in order to really optimize your data analysis and processing. Read more »
Evan Barke

SQL Server Business Intelligence – Expanding fact tables in an established data warehouse

May 30, 2014 by

Introduction

As in often the case in life, things that sound simple are not always the easiest things to do. In computer science this is even more often the case. In fact, one of the most challenging things about information technology work is often the communication with bosses that know little about technology and require justification for time spent on seemingly simple tasks. However, by the same token, tasks that seem impossible to the untrained eye are often fairly straightforward and quick to implement and can earn you easy respect. Read more »