I get this question all the time: what makes a data warehouse different from a regular database?

To those in organizations with a less robust business intelligence and analytics maturity, it may be difficult to differentiate between the two. However, there are very distinct differences between a data warehouse and a regular database. In this post, I will quickly address the major differences.

Read vs. Write

A “regular” database is generally the destination for data modified from a front-end application. In banking, the core system will typically record all changes and data to a standard database. A CRM system (like Dynamics CRM) will write to a standard SQL Server database. These databases are specifically designed to write data (update or insert data) as quickly as possible. They are not optimized for read (data retrieval) operations. In other words, the database is not designed to efficiently get data out of it. A “regular” database is often normalized; this structure optimizes those write operations.

A data warehouse, on the other hand, is designed for read operations. The structure of the data warehouse is designed to accommodate the fact that the majority of a user’s interaction with the warehouse will be retrieving data from it. The only time a “write” operation – any operation that modifies existing data or adds new data – occurs in the warehouse is at night during the ETL process (in most data warehouses anyway).

Unlike a “regular” database, a data warehouse is denormalized. You will often hear this structure referred to as a star schema when discussed in a business intelligence context.

Point in Time vs. Historical

A credit union’s core banking database will very easily tell you what a member’s current share balance is. But will it just as easily tell you what that member’s balance was yesterday? Last week? Last month? A year ago?

Another major difference between a “regular” database and a data warehouse is that a data warehouse is designed for historical reporting. Data warehouses accrue information nightly from various source systems; a “regular” database typically only stores current data (or an extremely limited amount of historical data). Some historical data might be buried in log files or audit trails in a “regular” database, but the database is by no means designed to efficiently or easily access this data. A data warehouse, whose primary purpose is for analytics and intelligence, makes accessing this historical data incredibly easy.

Think of what you ask your front-end applications versus what you might ask a data warehouse. A front-end application (whose data is stored in a “regular” database) might be asked “what sales prospects are in our system today? A data warehouse might ask “what is the average number of sales prospects we have had in our pipeline for the last twelve months?”.

Single Source vs. Multiple Sources

A “regular” database is usually the backend to a front-end application often used by business users or customers. Each backend database is designed to only store data for that particular application.

A data warehouse, on the other hand, is designed to integrate data from various application sources. A healthcare example might be that the data warehouse integrates the main EMR system database, a 3rd party surgical scheduling application database, a billing database, and a logistics application’s database. A credit union’s data warehouse might integrate their core platform, consumer loan origination system, mortgage origination system, and CRM application. Each of these data warehouses have integrated data from various “regular” databases. The ability for a data warehouse to integrate data from various data sources is probably the most important difference between a “regular database” and a data warehouse.


There are several other differences between a data warehouse and a “regular” database, but these few points represent the major differences in structure and design. As big data, analytics, and business intelligence become more and more critical to an organization’s sustained success, becoming familiar with some of these key business intelligence concepts will become a necessity. Take a look at some of our other business intelligence posts to learn more!

Photo Credit

Data warehouses, multi-dimensional analysis cubes, and predictive analytics can appear to be imposing goals.

Where do we start?

How quickly can we accomplish our data goals?

Do I have the right talent?

These questions, and many like them, create doubt and concern amongst credit union executives wondering how they can start utilizing data better and begin to develop BI practices. Rich Jones and I have developed a business intelligence strategic engagement opportunity with credit unions seeking to answer these questions about business intelligence and data utilization. From the $300 million credit union or community bank without an MCIF to the $2.8 billion organization working to build their first data warehouse, we help each organization design a very specific roadmap that will enable them to accomplish their business intelligence goals and become a data-driven credit union.

So how does it work?

We recently completed a very successful business intelligence strategy engagement with a large east coast credit union. Their story was like most credit unions – there were many third-party applications and data sources spread throughout the organization. The information contained within these disparate applications made it incredibly challenging to get a 360-degree view of their membership and their internal business operations. Realizing that they could do more with their data, they sought our assistance to develop a long-term business intelligence and data strategy that could lead them to success.

Once we arrived onsite, we met with nearly 30 individuals over a four-day period to identify the various applications used throughout the credit union, the current reports and data uses, the needs of each department, and a variety of other relevant details. From the CEO to the Vice President of Consumer Lending to the lead Lending Analyst, each individual interviewed gave us the insight necessary to understand the organizational data needs and current data challenges. These interviews were absolutely critical to the success of the engagement and comprise the overwhelming majority of the time that we spent onsite.

With the interviews completed (and many pages of notes!), we consolidated our discovery notes and developed an 18 to 36 month data strategy that will allow the credit union to achieve their data goals. Our very detailed implementation plan included hiring milestones, third-party data integration milestones, business intelligence unit establishment deadlines, and several other key deadlines and milestones. These very specific deadlines allow the credit union to monitor their progress as they undertake each step of the process. Sequential, logical, and iterative, the roadmap breaks down the business intelligence strategy into very achievable and digestible tasks that reduce the stress that often accompany significant organizational projects.

Prior to this, the credit union was struggling with how to take the right first steps in achieving their data goals. The project loomed ominously as they continued to struggle with business intelligence and data utilization.

Now, the credit union understands exactly what they need to do, whom they need to hire or train, when they need to achieve certain milestones, and all of the details in between. Stress and concern over business intelligence has turned into excitement and opportunity. They now have the plan in place to become a data-driven credit union!

Why us?

Our implementation plans and business intelligence roadmaps are based on significant past experiences and expertise. Rich’s experience in leading the establishment of business intelligence units at multiple large credit unions, and my expertise in the tactical development of data warehouses and business intelligence operations creates a powerful combination of two distinct yet complementary skill sets. Together, we can help change the credit union world from a backwards-looking, report-reading industry into one that is forward-looking and data-driven.

You may contact Brewster Knowlton at brewster@knowlton-group.com or Rich Jones at rich@leading2leadership.com. Click to get additional information about our business intelligence strategic engagement!


In my post discussing Top-Down BI Strategy, I introduced a chart which I call the “Data Mass Value Curve”.

Value of Data vs. Amount of Data

Data Mass Value Curve

This graph identifies an exponential increase in the value of the data as a function of the amount of data available. That is to say that the more data you have available to you, the value of that data increases exponentially. But why is this true? Why does the value of the data available exponentially increase?

Why is the Increase of Data Value Exponential?

Take the example of a credit union who only has access to their core data. This credit union will only be able to provide a one-dimensional analysis of their membership. They can return simple requests like, “how many of our members have share drafts” or “how many of our members have vehicle loans”. Those questions are simple and do not provide particular insightful analysis.

Now look at the credit union that has access to both their core data and their CRM system. Questions that were once simple like “how many of our members are over the age of 65?” become “how many of our members over the age of 65 call into our call center more than once a week?”. The questions are much more insightful and the integration of data systems allows for more complex questions to be asked of the data. Instead of asking questions only about the core or only about the CRM system, you can ask questions that draws data from both systems at once.

As the number of data sources available to us increases, the number and complexity of questions we can ask of our data continues to increase. With three data sources available, we can draw analysis from one, two, or all three sources at the same time. This is the crux of the exponentially increasing value of data as the number of data sources (or amount of data) increases. This level of multi-dimensional analysis is critical to a business intelligence program that delivers significant tangible benefits to the organization.

Collect as much data as possible, right?

Your first thought might be, “let’s collect as much data as we possible can”. But there is a slight catch – the “Data Mass Value Curve” assumes a proportional level of data expertise that can properly and efficiently utilize the data available. Often, the limiting factor of an organization is the expertise with which they can utilize the data available. When the data expertise barrier is reached, the increasing amount of available data provides no additional value.

Data Mass Value Curve_Barrier_1

In this curve, the expertise barrier is reached quite early and the value of the data is stifled. The actual value of the data is a fraction of its potential given the limited expertise to utilize the data properly.

Now let’s look at an organization with a bit more data expertise available:

Data Mass Value Curve_Barrier_2

Notice that the expertise barrier is reached farther out on the curve. This organization is not able to completely leverage the amount of data available to them, but they are able to make more of an impact with their greater expertise.

The organization below has expertise that can provide nearly all the value possible:

Data Mass Value Curve_Barrier_3

This organization has the necessary resources, talent, and efforts to be able to properly utilize the data sources available to them.

How does this help me today?

This “Data Mass Value Curve” naturally is a bit abstract. However, the concepts can be applied to your BI environments. Ask yourself some simple questions:

  1. How many data sources can I access in a SQL database format?
  2. Of these data sources, how much of the data is integrated? Do you have data marts or a data warehouse in production?
  3. Do I have the necessary staff, resources, or talent to be able to effectively utilize and analyze this data?

After answering those basic questions, you can start to gauge where your organization sits on the “Data Mass Value Curve”. Once you have established your location on the curve, you can begin to roadmap a strategy to achieve proper data utilization and returned value. Depending on the answers to the questions above, you may need to hire or train SQL and BI talent. Other organizations may have a necessary level of expertise but not enough integrated or accessible data sources available to provide value. Abstractly understanding your organization’s location on the curve can help you drive your business intelligence investment in the right direction.

In our last lesson, we showed you how to use SSIS to export data to Excel. In fact, all of our previous SSIS lessons have focused on exporting data from SQL into some other format. In this lesson, however, we will show you how to import data from Excel into SQL.

This is very helpful as you start to develop your business intelligence program in your bank or credit union. Think of how much data exists at your financial institution sitting in the form of Excel spreadsheets. By importing this data into SQL, it can be cross-referenced and/or integrated with existing SQL data. This is a key first step in any business intelligence program, and in this lesson we will help you get started!

Setting up our Data Source

The Excel spreadsheet that I will be importing into SQL is based on data compiled by the NCUA. The spreadsheet contains a list of all credit unions and their associated member totals and total assets (as 6/30/2014). The image below gives you a quick snapshot of what the data looks like:

Spreadsheet Header

With our source Excel spreadsheet identified and described, open up your SSIS solution named “Credit Union SSIS Solution” if you have been following along with previous examples. Otherwise, create a new SSIS solution and name it “Import to SQL.dtsx”. Just like we have in past examples, drag a “Data Flow Task” from the “SSIS Toolbox” onto the “Control Flow” designer. Right click on the task, click “Rename”, and then rename the task “Import NCUA File into SQL”.

Renamed Data Flow Task

Now, double-click on the newly renamed task to be brought to the “Data Flow” editor. From the “SSIS Toolbox”, drag an “Excel Source” item onto the data flow designer. Rename this item “NCUA Excel File”.

NCUA Excel File Task Item

Double-click on the renamed item; the “Excel Source Editor” window will appear on your screen. Click the “New” button to the right of the “Excel connection manager” dropdown. Click “Browse”, then locate the file you will be importing (in my case, the NCUA file), press “Open” and then press “OK” when you are brought back to the “Excel Connection Manager” window.

In the “Name of the Excel sheet” dropdown, choose the specific spreadsheet within the Excel workbook that you wish to choose as your data source.

Excel Source Editor

Press “OK” once this has been completed.

Defining the Destination to Import Data into SQL

With the Excel source defined, drag an “OLE DB Destination” item from the “SSIS Toolbox” onto the data flow designer. Rename this item “SQL Destination”. Click the “NCUA Excel File” item so that it is selected then drag the blue arrow from the source item to the “SQL Destination” item.

Connected Items

Next, double-click on the “SQL Destination” item. Here, we defined where the NCUA data will reside in SQL. Click the “New” button to the right of the “OLE DB connection manager” dropdown. Select the connection from the left panel on the “Configure OLE DB Connection Manager” window that appears or define a new one by clicking the “New” button at the bottom of the window. Press “OK”.

For this example, we will need to create a new table in the database for this Excel data to reside. To do this, click the “New” button to the right of the “Name of the table or the view” dropdown. The “Create Table” window will appear on screen containing the SQL code that will be used to create a new table. I want to make one subtle change and alter the name of this table. After “CREATE TABLE” replace “[SQL Destination]” with “NCUA_Data”.

Create Table Window

Press “OK” once you have done this. If you open SQL Server Management Studio, you will see that this new table has been created in the database. This is important; the table is created once you press “OK” from the “Create Table” window – NOT when you execute the package for the first time.

To finish setting up the destination, press the “Mappings” option from the left side of the “OLE DB Destination Editor” window. Ensure that the column mappings are correct, and then press “OK”.

Execute the package (by pressing F5 or the green play button on the toolbar) to see if everything works. Assuming you successfully execute the package in SSDT, open up SQL Server Management Studio. We want to view the contents of the table we just sent the Excel data to in order to validate the process properly imported all data.

SQL Validation


It’s that easy! The process required to import data into SQL from Excel is nearly identical to the previous export examples we have completed throughout the series. We simply define the source, define the destination, and then the mappings between the two. Learning this relatively simple task in SSIS can open up tons of new opportunities for you to be able to use your data more effectively. Eliminate silos of Excel data by bringing that data into SQL. Doing this will allow you to integrate and cross-reference it with your other SQL data sources ultimately bringing you closer to business intelligence success!

Download the Excel File from this lesson by click this link: NCUA_Data.xlsx

In our last post, we learned how to use SSIS to export data to a flat file. While that is certainly a step in the right direction towards removing all manual effort from our reporting processes, we still had to manually kick off that SSIS package. In this post, we are going to learn how we can use the SQL Agent to schedule this package to automatically execute.

What is the SQL Agent

The SQL Agent is a Windows service that executes scheduled tasks or jobs. Creating a SQL Agent job allows us to schedule when an SSIS package, for example, is executed automatically. SQL Agent jobs can be created to execute a number of tasks including: executing T-SQL code, SSIS packages, SSAS queries, PowerShell scripts, and a number of other types of tasks. For our purposes, we will be creating SQL Agent jobs to automatically execute our SSIS packages. ETL jobs are often automatically executed at a set time (usually nightly) via SQL Agent jobs, therefore understanding the basics of the SQL Agent are critical to becoming well-versed in the data warehouse/business intelligence toolkit contained within Microsoft SQL Server.

Creating the SQL Agent Job

Before we create the SQL Agent job, open up the “Credit Union SSIS Solution” in SSDT if it is not currently open. In the “Solution Explorer”, right click on the “Package.dtsx” package name and click “Rename”. Rename the package “Export to Flat File.dtsx”. Press the “Save All” icon on the toolbar and then exit SSDT (SQL Server Data Tools).

NOTE: the next steps require you have the proper authorization and permissions to create, schedule, and execute SQL Agent jobs. You may need to speak with your IT department for proper authentication depending on your SQL environment.

Open SQL Server Management Studio (SSMS) and login with your Windows credentials. At the bottom of the “Object Explorer” you should see an item named “SQL Server Agent”. Click the toggle button to the left of it to expand the folder. Once expanded, expand the “Jobs” folder.

Object Explorer

This where the SQL Agent jobs are stored. Notice that two jobs currently appear – these are generated by the system and can be ignored. To create a job, right click on the “Jobs” folder and click “New Job”.

New Job

Give this job the name “Exporting to Flat File”. Leave the other default options unmodified. In every SQL Agent Job you can have many steps. So, for example, we could have multiple SSIS packages executed in the same job. This is a nice feature if you have recurring processes that need to be executed on the same schedule. You could create a single job for all of your weekly recurring reports, so that you would only need to monitor and maintain a single job instead of one for each SSIS package.




Click on the “Steps” option on the left side of the “New Job” window.

Steps

Click the “New” button near the bottom of the window to create a new job step. Name this step “Export to Flat File SSIS Package”. In the “Type” dropdown, select “SQL Server Integration Services Package”. You will notice the bottom half of the window will change significantly once you make the selection.

In the “Package Source” dropdown, choose “File system”. This is telling the SQL Agent that we will be choosing a package located in a folder instead of being stored somewhere else (there are other package storage mechanisms within SQL Server that could be selected). With “File system” selected, click the ellipsis button to the right of the “Package:” text box near the bottom of the window. Locate the SSIS package “Export to Flat File.dtsx” that we created in the previous post and renamed earlier in this post. Press “Open” once you have found this item.

Job Step

After making the necessary changes, your window should look similar to the image above. Press “OK” once you have made the modifications. Once you press “OK”, you will be brought back to the “New Job” window which should now look like:

New Job Part 2

We have created the SQL Agent job with the appropriate step created to execute the SSIS package we created in the last post. Now we must schedule the job to execute at a specific time.

Click the “Schedules” option from the panel on the left. Once the window changes, press “New” towards the bottom of the screen.




When the “New Job Schedule” window appears, type “Sample Ad Hoc Execution” in the “Name” text box. Then, in the “Schedule type:” dropdown, choose “One time” from the menu.

Schedule the job to execute about ten minutes from when you are completing this step. So, I am completing this step at around 11:50 AM on 6/29/2015, so I will set the job to execute at 12:00 PM.

Job Schedule

Press “OK” once you have made the schedule updates. Press “OK” at the bottom of the “New Job” window to save the SQL Agent job we just created. If there were any errors or you do not have the necessary permissions to create the job, SQL will throw an error and a pop-up window will appear.

You will notice that the “Exporting to Flat File” job now appears under the “Jobs” folder in the “Object Explorer”.

Updated Jobs Folder

Once the scheduled execution time passes, right click on the job in the “Object Explorer” and click “View History”. You will see a successful execution logged with the appropriate details on the screen. If anything failed in the job, this window will tell you why the error occurred. Using this log viewer is incredibly helpful when debugging a failed SQL Agent job or step.

View History

You can navigate to wherever you saved the exported flat file and see that the file was modified and created right at the time we specified.





And just like that you created your own SQL Agent job and automated the execution of an SSIS package. This is a critical step towards leveraging the most valuable tools within Microsoft SQL Server and its business intelligence operations. If you need to automatically import data into a SQL table or automatically execute a set of SSIS packages, using a SQL Agent job provides you with the flexibility and automation you need to be efficient and successful!

Up to this point, we have focused solely on using SSDT (SQL Server Data Tools) or BIDS (Business Intelligence Development Studio) to create SSRS reports. Another aspect of the Microsoft Business Intelligence software is a package known as SQL Server Integration Services or SSIS. SSIS is used primarily as a tool to move data in an efficient or automated fashion. For example, we can use SSIS to automate the movement of data from one database to another database or from one table in a database to an Excel file. In short, think of SSIS as the tool that allows you to move and modify data from one location to another. There are literally thousands of different ways you can use SSIS, but we will focus on the most commonly used methods: importing and exporting data. In this post, we are going to show you how to use SSIS to export data to flat files.

Starting our SSIS Project

To create our SSIS project, open up either SSDT or BIDS depending on what version of Microsoft SQL Server you are running. I will be using SSDT, but the instructions and steps will be the same. When SSDT opens, you are brought to the “Start Page”.

Start Page

Click “New Project” in the top left corner of the “Start Page” window. When the “New Project” window appears, make sure that “Business Intelligence” is highlighted on the templates panel on the left side of the screen. Choose “Integration Services Project” in the middle of the screen. Name this solution “Credit Union SSIS Solution”.

New SSDT Project Window

Press “OK” once this is complete.

Blank SSIS Package

Take a brief look at the “SSIS Toolbox” panel on the left side of your screen. This is where you can access all of the control flow and data flow items for SSIS. Before continuing, let’s define the terms “control flow” and “data flow”.

The “data flow” is a task within SSIS that controls the movement of data between a source and destination. The data flow tasks are different from control flow tasks (which we will describe in the next paragraph). Data flow tasks include tasks like the “Derived Column” that allow you to create and define a column directly within the data flow, a “Merge” task which allows you to merge data from separate sources, and a “Data Conversion” task that allow you to type cast directly within the data flow. There are dozens of different data flow tasks that give you tremendous flexibility in defining the movement of data from source to destination.

Learn SQL Course

Looking to learn SQL? Click here!

The “control flow” – specifically the control flow task items – manage the workflow of the SSIS package. For example, you may want to first move files from one location to another using “File System Task”, then complete some data flow using a “Data Flow Task”, and then send an email confirming the steps have been completed using the “Send Mail Task”. Like the data flow tasks, there are dozens of control flow tasks that enable you to make complex SSIS packages.

Creating and Modifying the Data Flow Task

In this post, we are only going to need a “Data Flow Task”. To add a “Data Flow Task” to the package’s control flow, either double-click on the “Data Flow Task” item from the “SSIS Toolbox” panel or click on the item and drag it onto the blank control flow.

Data Flow Task

Next, we need to modify the data flow task. Right click on the “Data Flow Task” on the control flow and click “Rename”. Name this task, “Export Account Type List”. Next, double click on the “Export Account Type List” task or choose the “Data Flow” tab towards the top of the control flow editor. The screen should change to reflect that you are editing the data flow task and not the control flow anymore.

SSIS Data Flow Editor

The simplest data flow has a source and a destination with no other tasks or transformations in between. Our goal for this task will be to use SSIS to export data from a database to a flat file (a .csv or .txt file).

Defining the Data Source

To define the source of our data flow, drag a “OLE DB Source” item from the toolbox onto the data flow editor. “OLE DB” stands for “Object Linking and Embedding, Database”. Without getting into overly-technical details, just think of this as the way in which Microsoft interacts with database objects.




You will notice the big red “X” to the right of the name of the task on the data flow editor. This is the way SSDT and SSIS tell you that something isn’t quite right and needs attention.

OLE DB RED X

Double click on the “OLE DB Source” task to bring up the “OLE DB Source Editor” window.

OLE DB Source Editor 1

Click the “New” button to the right of the “OLE DB connection manager” dropdown. Press the “New” button once the “Configure OLE DB Connection Manager” window appears. When the “Connection Manager” window appears, type in the appropriate name in the “Server name” dropdown. Since my SQL instance is running locally, I can type a period in the “Server name” dropdown to indicate the localhost. Once the server has been specified, choose the correct database from the “Select or enter a database name” dropdown. I am going to return my account type details from the “CreditUnion” database.

Connection Manager

Click the “Test Connection” button at the bottom of the window to ensure everything has been entered correctly, and you have the proper permissions to access the database or SQL instance. Press “OK” in the “Connection Manager” once complete.

Once you select “OK”, you will be returned to the “Configure OLE DB Connection Manager” window. Under “Data connections” on the left side of the window, you will notice that the data connection we just created appears as a selectable item. Highlight this data connection (if it isn’t already), and press “OK”.

Configure OLE DB with Connection

Now, you should be brought to the “OLE DB Source Editor” window with the “OLE DB connection manager” dropdown filled in with the data connection we just created. For now, leave the “Data access mode” dropdown value set to “Table or view”. We will discuss, in upcoming posts, how we can use some of the other options in this list.

From the “Name of the table or the view” dropdown, find the table that stores the details about the account types. In my database, this is the table named “[dbo].[AccountType]”. If no table exists with a distinct list of account types, change the “Data access mode” dropdown value to “SQL command”. In the text box that appears, type in the SQL query that you will use to return that list.

OLE DB Source with table

OLE DB Source with SQL Command

Once you have this set up, choose the “Columns” option from the left side of the window. This will show you a list of the columns that are included in the data flow based on the query you defined or the table you selected.

OLE DB Columns

Press “OK” once complete.

Right click on the data flow task named “OLE DB Source” and rename it to “AccountType Table Source”.

Defining the Data Destination

We have a data source identified and defined, but now we need to define the destination. Drag a “Flat File Destination” item from the “SSIS Toolbox” onto the data flow task editor. Rename this item “AccountType CSV Output”. Click on the “AccountType Table Source” source. Notice two arrows that appear underneath the task Click on the blue arrow and drag it from the “AccountType Table Source” item to the center of the “AccountType CSV Output” item and then release. The source and destination items should now be connected by this blue arrow.

Connected Items

Double-click on the “AccountType CSV Output” item. When the “Flat File Destination Editor” window appears, click the “New” button to the right of “Flat File connection manager”. Leave the radio button next to “Delimited” selected and press “OK”. When the “Flat File Connection Manager Editor” window appears, start by renaming the “Connection manager name” to “AccountType CSV”.




Next, click the “Browse” button to the right of “File name”. Instead of selecting an existing file, this is where we are going to create a new file. Simply type “AccountType” in the “File name” text box and then change the drop down value from “Text files (*.txt)” to “CSV Files (*.csv)”. I am saving this to my desktop, but you can choose whatever file or network location you wish.

Create File

Press “Open” once you have the values entered like the image above. You will be returned to the “Flat File Connection Manager Editor” window. Click the checkbox to the left of “Column names in the first data row”, and then press “OK”.

When you are returned to the “Flat File Destination Editor” window, click the “Mappings” selection from the left navigation panel of this window. This is necessary when creating every destination within SSIS. This tells SSIS how to map the data from each source column to each destination column. Since we are creating a new flat file based on our source data, SSIS automatically maps each column to the identically named destination column.

Mappings

Press “OK”. Save everything in this solution by choosing the “Save All” option from the toolbar:

Save All

Press the green arrow on the toolbar to run the package. The screen will change slightly as SSDT enters debugging mode. Assuming everything ran smoothly, you should see a screen like you see in the image below:

Debug

Navigate to where you created the output file when we defined the destination and open up the CSV file. You should see the appropriate number of rows (one for each account type) with all of the relevant column details. You may need to use the text to columns feature in Excel to separate each column based on the comma delimiter, but all of your data should be present.

Head back to SSDT and press the stop button in the toolbar to exit the debug mode.


It just a few simple steps, we have created our first SSIS package. As you can see, it is fairly easy to use SSIS to export data to flat files. The process is very similar if we were to export our data to Excel or import data from a file into SQL. In upcoming posts we will explore additional ways we can use SSIS!

In our last post, we showed you how to create drill down SSRS reports so you can create incredibly dynamic reports. Drill down reports create dashboard-like functions right within SSRS. For those credit unions trying to maximize their BI investment, initially using SSRS over third-party BI visualization tools can minimize your upfront costs.

What is an SSRS Subscription?

This post will introduce you to creating SSRS subscriptions. But, what is an SSRS subscription? Think of any other subscription you have in your life to a blog, newsletter, or magazine. Your subscription allows you to receive something that you want at a specified time. SSRS subscriptions are very similar; you can subscribe to a particular report and have it delivered to you at a specified time and location.

SSRS allows you to receive reports in a few different ways. The most common delivery methods are to have the report sent to a windows file share or to your email. If you have Microsoft SharePoint in your organization, you can have SSRS reports delivered directly to SharePoint as well. In this particular post, we will show you to how to create a windows file share subscription for a single report. There are some very inventive ways to use SSRS subscriptions beyond what we will discuss in this post. Head over to Microsoft’s SSRS Subscription and Delivery page to learn more if you are interested!

Why use SSRS Subscriptions?

Suppose we have a key weekly lending report that is currently being created the old-fashioned way: manually through Excel. The person creating that report in Excel will complete the report and send it to three or four people. Those three or four people save the copy locally to their drive thus creating multiple copies of the same document. Along with having multiple copies of the same document stored on the shared drive, we are also using unnecessary space on the mail server. IT network and server staff deal with this dilemma regularly.

With SSRS, you could create that report and have the data dynamically retrieved from a SQL database like we have done in past examples. Then, we create an SSRS subscription that places the report output in a specified location at 7 AM every Monday morning. Now, those three or four individuals who used to receive the report in their inbox can simply head to the designated location to view the report. They never have to ask for the report and the person who used to develop the report never has to manually produce and send it again.

Are you that person stuck developing a report manually and then sending it out to various people who request it? Then this post is for you!

Setting up the Report Security

To create subscriptions, we need to first navigate to the report browser. This may be the first time you have done this. To do so, ask your IT/BI area for the SSRS URL. It typically has the form: http://[server name]/Reports. This is the URL that you use to view reports and manage subscriptions. Once you are there, you may need to upload the reports that we created if you haven’t deployed them already.

First, create a new folder on the home page (if you have permission to do so) named “Credit Union Reports”.

Create New Folder

Once the folder is created, click on it to be brought to the page for the “Credit Union Reports” folder. In the SSRS menu, there is an option title “Upload File”. Click that button, browse to where you saved your SSRS reports from previous posts, and select the .rdl files for each report. I will be uploading two reports: “Share Account by Branch” and “Share Accounts Opened”.

If you have done this correctly, the “Credit Union Reports” page of SSRS in your browser will look like the image below.

Folder with Reports

If you click on any of the reports, you should be able to view the output of those reports. This is similar to clicking the “Preview” button for a report in SSDT or BIDS.

Hover over the report named “Share Accounts by Branch”. You should see a dropdown arrow to the right of the report name. Click this arrow and choose “Manage”.

Manage Report

When the browser changes, select the “Data Sources” from the left side of the screen. If the radio button next to “A custom data source” is not selected, select it now. Under “Connect Using:” select the radio button next to “Credentials stored securely in the report server”. Subscriptions require that credentials be stored securely in the report properties. It is recommended that you have a dedicated username with the proper privileges – something like “Data_Warehouse_Reports”. That way, you don’t have to use your own personal credentials in the report data source along with having some other benefits.

Once you choose the radio button next to “Credentials stored securely in the report server”, enter either your personal credentials or the credentials for your dedicated report/data warehouse account (this is more of an IT thing so you may or may not have one). Click the check box next to “Use as Windows credentials when connected to the data source” (this assumes you are using Windows credentials through active directory – which is most common – instead of a SQL Authentication account created for the SQL instance).

Data Source Security setup

Press “Test Connection” towards the bottom of the screen to ensure that all properties and credentials have been entered correctly and that you have the necessary privileges. If everything was entered properly, green text under the “Test Connection” button will appear saying “Connection created successfully”.

Test Connection

Press “Apply” to save your changes.

Using the breadcrumb trail at the top of the browser page, head back to the “Credit Union Reports” page.

Creating the SSRS Subscription

Now that we have stored the credentials in the report, we can now create the subscription. From the “Credit Union Reports” page of the report server, hover over the “Share Accounts by Branch” report. When the dropdown arrow appears, click on it and then choose “Subscribe”. The subscription delivery options page should appear:

Blank Subscription

This screen allows you to specify how, where, and when you would like the report delivered. In the “Delivered by:” dropdown, choose “Windows File Share”. This method will allow us to send the report to a location on our network. When you choose “Windows File Share” from the menu, the screen should change significantly:

Blank Windows File Share

We can leave the “File Name” text box with the default value of the report’s name. The “Path” text box is where we specify the location that we would like the report to be placed. There is an important subtlety to note here: the path must be in UNC (Universal Naming Convention) form. If we wanted to place the report on our C drive, we couldn’t use the path “C:\”. Instead, we would have to type it with the form “\\[Computer or Server Name]\c$”

To keep things simple, let’s place this report on our C drive. In the “Path” text box, type “\\[Computer or Server Name]\c$” where the [Computer or Server Name] is replaced with the name of your computer or server you are working on. For example, if I wanted to place this file on a computer named “Brewster”, I would use the path “\\Brewster\c$”.

In the “Render Format:” dropdown, choose “Excel”. Before we enter credentials, click on “Select Schedule” towards the bottom of the screen.

Select Schedule Screen

This screen allows us to specify when we would like this report to run. We can be very specific with the scheduling process which gives us some nice, flexible options. We want to set this report to run in about five or ten minutes from whenever you are completing the subscription. So, if it is 2pm on a Wednesday when you are following along with this post, be sure to select the checkbox next to “Wed” and set the “Start Time” to about 2:05 pm.

Press “OK” once you have this set up. Again, we want this to run within the next few minutes (if you are following on), so be sure you have your times set up correctly.

Now, you should be brought back to the “Subscription” screen. Go ahead and enter your credentials in the “User Name:” and “Password:” text boxes. I am writing this post at approximately 1:20 pm, so I have scheduled the subscription to execute at 1:25 pm.

Completed Subscription Details

With everything completed as you need, press “OK”. As long as there were no errors immediately flagged by the report server, your SSRS subscription should be scheduled. You should be returned to the “Credit Union Reports” page in SSRS.

Now, wait the few minutes left until the subscription should be executed. In my case, this means waiting until 1:25 pm. While you wait, you can click the “My Subscriptions” hyperlink at the top of the page.

My Subscriptions

When you click that hyperlink, you will be brought to a screen that contains all of your subscription details. Since this is the first subscription we have set up, you should see just a single row with the “Status” field set to “New Subscription”.

My Subscriptions Details

Once the subscription executes, this screen will be updated with the results of the last subscription execution. I have waited a few minutes, so it is past 1:25 pm which is when I had the subscription scheduled to execute. I am going to refresh the “My Subscriptions” page.

Successful Subscription

If you look at the “Status” column now, you will see that the value has changed to reflect the status of the last execution. Since this subscription executed without issue, it tells me that “The file ‘Share Accounts by Branch.xlsx’ has been saved to the “\\XXXXX\c$” file share.”.

To be sure, let’s navigate to the C drive where we told SSRS to place the report.

Windows Explorer Proof of Subscription

We can see that the “Share Accounts by Branch” report has been placed to the C drive in an Excel format just like we expected!


SSRS subscriptions can be incredibly helpful when we need to automate the delivery of reports that we have created. One of the key components of business intelligence is automating and improving the efficiency of our reporting operations. By taking advantage of the inherent features of SSRS, we can drastically reduce the manual effort involved in completing and submitting reports throughout our credit unions.

Our previous post showed you how to create an SSRS report with multiple parameter data types. By doing that, we created even more dynamic reporting opportunities.

An incredibly invaluable function of SSRS is the ability to “drill down” to other reports. The concept of “drilling down” is where you can click on a specific item in the report to get more detail. For example, suppose you have a table showing the count of all members grouped by the state in which they reside. We could create drill down capabilities so that if you clicked on the name of the state, a new report would appear showing the name and address for all members who reside in the state you selected. Effective dashboards frequently leverage the concept of drill downs; we are going to apply this concept to our SSRS reports.

Setting Up our Summary and Detail Reports

When creating drill down SSRS reports, you must have at least two reports: a summary report and a detail report. The summary report will be the report that user will interact with to drill down to additional details. The detail report will contain all of the details we want to display after a user clicks on a specific row or item in our summary report.

In our initial post on building your first SSRS report, we created a report that showed the number of share accounts opened at each branch and the total current balance of those shares. For this post, we will use the report created in our first lesson as our summary report.

Since we already have our summary report, we need to now build a detail report. In this detail report, we want to show details for each account opened for a particular branch. Our goal is to have users be able to click on a branch name from the summary report and then be brought to the detail report for that specific branch.

To start, open up our existing project in SSDT or BIDS if it is not already. In the “Solution Explorer” on the right side of your screen, right-click on the “Reports” folder, hover over “Add”, and select “New Item”. Make sure that “Report” is selected on the window that appears, and give this report the name “Share Account Details by Branch.rdl”. Press “OK” once complete.

Add Detail Report Item Screen

Create the “Data Source” to whatever database in which your data resides. Name this data source “CreditUnion” (see the building your first SSRS report post to review how to do this).

Once the data source is created, right-click on the “Datasets” folder and a new dataset. Using the “CreditUnion” data source we just created, we need to define the SQL query that will populate this report. We are going to include many columns including: member number, account open and close dates, current balance, etc. One of the most critical aspects of the query you place in the new dataset, which we will call “ShareAccountDetails”, is that it includes a WHERE clause filtering on the branch:

ShareAccountDetails Dataset

This parameter is critical to our ability to create the drill down function. Press “OK” to save your dataset if you have not done so already.

You should see the “Branch_Name” parameter in your “Parameters” folder in the “Report Data” panel on the left side of your screen. If you remember back to our last post on creating an SSRS report with multiple parameters, we dynamically defined the available options for the “Branch_Name” parameter using a dataset. That dataset included a distinct list of all branch names from our database. Using the previous post as a guide if you need to review, create a dataset that contains a list of distinct branches. Then, modify the “Branch_Name” parameter so that its available values is defined by the branch list dataset. Unlike the last post, set the “Branch_Name” parameter to only accept a single value instead of multiple values. The “Available Values” section of the “Report Parameter Properties” window should similar to the image below:

Branch_Name Parameter

Press “OK” to save the parameter options if you haven’t done so already.

Next, we need to create a table in our report for the data to reside in once rendered. Either drag a blank table from the toolbox on the left side of your screen, or right-click on the blank report, hover over “Insert”, and select “Table”. Move the table to the top left corner of the report window.

Now, we need to add columns from our “ShareAccountDetails” to our table. Remember to hover in a cell of the data row (bottom row) of the table and wait for the table icon to appear. When we completed this when creating our first SSRS report, we only had one dataset. Since we have multiple datasets in this report, you must choose both the data source and the dataset from which you want to retrieve data:

Add Dataset Columns to Table

That was a subtle difference, but one that is certainly worth mentioning. To keep things easy, add each column of the “ShareAccountDetails” dataset to the table in our report.

Just like we have done before, make the header row bold and resize each column’s width to fit. In our previous posts, we formatted the column’s data type by right-clicked on a cell and selecting “Text Box Properties”. Make sure to format the “Account Opened Date”, “Account Closed Date”, and “Current Balance” columns appropriately.

Before previewing your report, it should look similar to the image below:

Pre Preview Table

Go ahead and preview this report now.

Detail Report Preview

This report is pretty basic, but it accomplishes what we need it to for the time being. At this point, we can say we have completed our “detail report”.

Creating the Drill Down Function

At this point, we have created both a summary report and a detail report. Now, we need to define the drill down action to tell SSRS what we want to happen when users click on the branch name from the summary report.

First, open up the summary report named “Share Accounts by Branch”. You can do this by double-clicking on the report in the “Solution Explorer” on the right side of your screen. Below is a reminder of what this report looks like:

Share Accounts by Branch

To begin creating the drill down action, right click on the “Branch_Name” cell in the data row (bottom row) of the table. Select “Text Box Properties”.

Branch Name Right Click

Select the “Action” option from the left side of the “Text Box Properties” window. This section of the “Text Box Properties” window allows you to define actions that occur when you click on the values in this cell or column. Choose the radio button next to “Go to Report”.

At this point, we are telling SSRS that we want to go to some report when we click on the values in this column of our report. Next, we need to define for SSRS what report we want to view and pass any necessary parameters to the detail report.

In the dropdown menu beneath “Specify a report”, choose the “Share Account Details by Branch” report that we created earlier in this post. Below that, press the “Add” button. If you remember in our detail report, we had a parameter named “Branch_Name”. We want this parameter to be automatically defined based on whatever branch we click on in our summary report.

In the “Name” dropdown, select “Branch_Name”. This dropdown contains a list of parameters from the “Share Account Details by Branch” report. Next, in the “Value” dropdown, choose “[Branch_Name]”. The “Value” dropdown indicates what value from the current report do we want to pass to the detail report. In this case, we want to pass the name of the branch that we select. The “Text Box Properties” window should look like the image below:

Branch Name Text Box Properties

Press “OK” once you have made the necessary updates to the “Action” section of the “Text Box Properties” window.

Go ahead and preview your report now. Try hovering over one of the branch names in the table; you will notice the cursor changes. Choose one of the branch names and click on its name. If everything was set up correctly, you will be brought the detail report with the “Branch_Name” parameter automatically populated based on whatever branch you initially clicked.


That’s it! That is all that it takes to create a drill down SSRS report. It all comes down to a few simple questions:

  • What report do I start with (our summary report)?
  • What report do I want to drill down to (the detail report)?
  • What do I want to click on to be sent to the detail report?
  • What values do I want to pass from my summary report to parameters in the detail report?

Those basic questions sum up the entire process of creating a drill down report within SSRS.

Big Data, Analytics, and Business Intelligence have become the most commonly used buzzwords in business.  Despite their varying uses and connotations, each boils down to a single proposition: how can we best use our data?  

The answer may vary dramatically from credit union to credit union.  Perhaps your goal is to improve marketing returns through increased analysis into member product propensity; for others it may be to improve reporting and operational efficiency through automation and data centralization.  No matter what the specific aim of your business intelligence strategy is, the ultimate goal is always to use data more effectively.

With the advancements in software and technology, business intelligence technology is now within any credit union’s reach.  However, simply having business intelligence software does not mean you have business intelligence.  Credit unions must invest time and resources to develop a successful Business Intelligence/Data Strategy (BIDS) that is both successful and sustainable.  Below are several points to consider to help you get started.

1. This is not purely a technology initiative; ensure the support of key business areas.

Many equate business intelligence with software and technology.  While this is certainly true to an extent, business intelligence is equal parts business and technology.  Successful business intelligence programs are designed to most effectively cater to the data, analysis and reporting needs of business functions of the credit union.  However, understanding the technology involved in developing your business intelligence environment is critical to understanding what is possible within the project’s scope of time and budget.  Sustainable Business Intelligence/Data Strategies leverage key insights held by both business users and technology professionals.

Before beginning your business intelligence investment, it is recommended that you receive buy-in from various departments throughout the credit union.  Lending and Marketing are two departments that typically benefit the most from BI; describe the benefits these areas could receive and ensure their support for the business intelligence initiative.

2. Develop a "Data Dictionary"

Do the terms account, member, individual, and household mean different things to different departments within your credit union?  Perhaps your MCIF does not include charge-off accounts, yet account-level reporting from your core typically does include these accounts.  Member-level reporting may involve different filtering criteria when different departments complete the same request.  Does your information systems department report slightly different account totals than your Accounting/GL application?

These subtleties are not uncommon, yet they represent an important aspect of developing your business intelligence strategy: not all terms mean the same thing to everyone.  A critical step to a successful enterprise business intelligence/data strategy is creating a "data dictionary".  Your data dictionary will include a list of terms used in the business and their specific definition as it will be implemented within your business intelligence program.  Key personnel in all areas of the credit union should agree upon these terms.  Term ambiguity can cause misinformation and lack of clarity for reports and dashboards – a major detriment to the success of your business intelligence/data strategy.

3. Assess your current business intelligence environment

Being realistic about your current business intelligence and data utilization environment is essential to developing a realistic strategy roadmap.  Are users consistently compiling reports?  Do relatively simple data requests take days to complete?  Are departments storing silos of information in stand-alone Access database or Excel spreadsheets?  Do you have staff competent with SQL?  All of these questions are essential to assessing your credit union’s current BI environment and BI readiness.

An invaluable exercise in assessing the current state of your business intelligence environment is to compile a "data inventory".  In this "data inventory", you will identify every data source within the credit union, which department or individual is responsible for that data source, and what type of data is stored in the source.  This is often a mix of Excel files, Access Databases, SQL Databases, and various credit union applications (core, residential LOS, residential/mortgage LOS, MCIF, etc.).  By compiling the "data inventory" you gain a tremendous amount of knowledge about your credit union’s current data landscape.  If you engage with an outside vendor to develop your business intelligence solution – as do most credit unions – the "data inventory" is invaluable in helping your vendor or consultant gain insight into the data environment of your organization.

4. What do you really want to measure?

Data warehouses are often the foundation of a business intelligence strategy.  In short, a data warehouse integrates data from various sources.  The data warehouse is designed for the purpose of reporting and analysis.  As such, it is critical to understand what your credit union seeks to measure before beginning to develop a data warehouse.  

A great way to assess what your credit union most frequently measures is to compile a "report inventory".  The "report inventory" is a list of all reports that are produced by the credit union.  It is best to focus on recurring reports, but it also valuable to understand what types of reports are being requested on an ad hoc basis.  In the "report inventory", capture which department compiles the report, who the report recipient is, how long the report takes to compile, what information is captured in the report, and how frequently is the report needed (i.e. daily, weekly, monthly, quarterly).  If you choose to engage with a business intelligence consultant, a "report inventory" allows the consultant to understand the type of data that is often requested and develop the data model accordingly.

5. Are you willing to invest in the long-run?

A successful business intelligence/data strategy is not a short-term solution.  It is not like Microsoft Excel where you buy it, install it, and begin using it in a matter of minutes.  Business intelligence solutions are best developed in smaller, targeted phases while keeping the larger picture in mind.  

For example, the first three months after beginning your business intelligence program might involve much of the analysis as to the current state of your organization’s data environment.  Simultaneously, you may be training a select number of staff on how to write SQL queries or develop reports and dashboards. 

In budgeting for your business intelligence initiatives, remember that this is not an immediate, one-time purchase.  A successful data program requires continuous investment as the data needs of your organization grow.  In nearly all business intelligence/data strategy engagements, you should be looking to institute a minimum of an 18-36 month roadmap. 

Now What?

Quite a bit is involved when developing a successful business intelligence/data strategy.  It involves a combination of business and technical expertise while simultaneously balancing the goals of your credit union, timeline, and costs.  To summarize, when beginning to develop your initial business intelligence/data strategy you should consider the following key points:

  • This is not purely a technology initiative; ensure the support of key business areas.
  • Develop a "data dictionary"
  • Assess your current business intelligence environment
  • What do you really want to measure?
  • Are you willing to invest in the long-run?

 

The Knowlton Group has partnered with Rich Jones, a 20+ year credit union executive and strategist, to create a turnkey business intelligence/data strategy consulting solution – providing both strategic solutions and technical expertise.  Together we will help you define a business intelligence/data strategy roadmap while also providing the necessary business intelligence technical expertise to develop a successful and sustainable data-driven culture.

Visit www.knowlton-group.com or email brewster@knowlton-group.com to find out more information!