Business Intelligence

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 »
Daniel Calbimonte

Creating Backups with SSIS

November 21, 2014 by

Introduction

This Article is for people with experience using T-SQL and SQL Server Management Studio, but without experience in SQL Server Integration Services (SSIS).

In this article, we will introduce you to the SSIS world and then we will show how to combine different SSIS task to create combined backup tasks. 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 »
Steve Simon

Share your data!… Loading SQL data from SharePoint

November 12, 2014 by

Introduction

In the process of doing the round of PASS SQL Saturday’s, I have often been asked how to load data into SQL Server from SharePoint. Naturally and at first glance it should be a ‘no brainer’ however the gotcha is normally related to a security issue. Over the past few days, I have been working on just such an issue. But wait, I am getting ahead of myself!

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 »
Evan Barke

SQL Server Business Intelligence features – creating reports based on OLAP cubes

May 19, 2014 by

Introduction

In order to build a SQL Server business intelligence solution one needs to:

  1. Design a de-normalized data warehouse
  2. Build and schedule an Extract, Transform and Load (ETL) package that will feed the data warehouse at regular intervals with new data from the OLTP database.
  3. Setup, personalize and process a cube based on the data warehouse.
  4. Add the processing step to the ETL schedule to ensure the whole chain is automated.
Note: These steps are covered in detail in other articles in the series. If you do not yet have an OLAP cube set up and automated and you need more info as to how to do so; please refer back to the relevant step.
Read more »
Evan Barke

SQL Server Business Intelligence Features – Creating a Simple OLAP Cube

May 9, 2014 by

Introduction

Multidimensional cubes and transactional databases are two very different things. From experience I have seen even veteran DBAs avoid the subject of cubes completely because it is too much of an unknown area for them. Often cube work is passed on to developers because of their comfort with using Visual Studio. This is a great pity because it is, in reality, not very difficult at all to create an OLAP cube. It is safe to say that most of the work needs to be done in a traditional SQL Server database engine / SSIS environment from creating the data warehouse model to keeping it fed with ETL packages. This article assumes you already have a data warehouse and uses AdventureWorksDW2012 as an example. Following these steps should put you on the road to a decent SQL Server business intelligence solution based on a read-optimized OLAP cube. Read more »
Evan Barke

SQL Server Business Intelligence Features – SQL Server Data Tools – Business Intelligence

April 30, 2014 by

Introduction

In our previous article on the introduction to SQL Server business intelligence we covered the general structure of an enterprise business intelligence solution. The tools needed to build these solutions were briefly mentioned. The purpose of this article is to provide you with a deeper understanding into the creation of an ETL (Extract, Transform and Load) dataflow. To do this one needs to use SQL Server Data Tools – Business Intelligence (previously known as BIDS or Business Intelligence Development Studio). In this article we’ll take a look at the basic functionality of SQL Server Data Tools and how to use it to keep your data warehouse up to date. It’s worth noting that there are many different ways to go about building your ETL solution. This article gives sound advice and pointers as to how to approach the problem. Read more »
Evan Barke

SQL Server Business Intelligence – Introduction

April 23, 2014 by

What a load of Bl…

You may be wondering what Business Intelligence is. If you’re reading this article you probably have prior experience with Microsoft SQL Server or at least one other relational database management system (RDBMS). If this is the case you’re probably used to managing a lot of valuable data. If you’re a DBA you may be used to maximizing performance by rewriting stored procedures, creating indexes or running profiling traces.
Read more »
Page 5 of 512345