Posts

How Credit Unions Can Quickly Improve their Analytics Maturity   (Part 1)

Is credit union leadership still moving cautiously and slowly when it comes to adopting data analytics technology?  Yes, according to The National Survey on Credit Union Data Analytics and Decisioning Trends that examined the adoption rates of analytics, priorities, budgets, and implementation timelines. The recent study reveals that nearly half (45%) of all respondent credit unions are lacking a data analytics strategy, and those that do have a strategy still say it will take three to five years to implement.

For most credit unions, the concept of implementing a data analytics strategy to better meet member’s needs is not a new concept.  From more accurate decision-making to reducing member attrition and acquiring new members, data analytics is a winning formula for most organizations.  But for the nearly half of all CUs that are still lagging in this key area of digital transformation, what’s holding them back? After talking with many credit union executives, it boils down to: devising a BI strategic plan, effective budgeting, recruiting talent, leadership buy-in, and lack of data quality.

Let’s dive in with some remedies to these very fixable challenges.

Challenge:
Devising a strategy that is owned and followed is a major stumbling block for many credit unions wanting to be more data-driven. Many financial institutions know they can do more with their data, but they don’t quite know how or where to start.  For many credit unions, it is common not to have the in-house expertise and skills to define and execute a successful data analytics strategy.

I see many organizations with partial strategies nestled within different areas of the business, such as marketing or lending. However, few organizations have figured out the antidote to close the gap between creating a plan and acting on the plan.

Solution:
For those credit unions ready to implement analytics initiatives, success requires a top-down approach. You should focus first strategically, from a higher level, before you start focusing only on the operational challenges, the technology and some of the tactical components that analytics requires. Here are a few steps when devising your BI strategic plans:

  • Determine your business objectives
  • Create a long-term budget
  • Build awareness and buy-in among all key players
  • Appoint a committee who is held accountable to keep the plan in motion
  • Bridge the talent gap and hire in house talent or outsource an expert
  • Assess and upgrade technology capabilities
  • Ensure data quality, governance and controls
  • Identify analytics success measures
  • Create an analytics workplace culture

Challenge:
Budgeting for data analytics programs is a top challenge for many credit unions. Just over half of the credit unions who responded to the survey revealed they have budgets in place for data analytics, and 45 percent of those CUs plan to spend less than $100,000 on such tools this year. Roughly,15 percent will spend more than $500,000. For many, it may appear daunting when viewing these numbers.  Certainly, the cost of data warehousing, data visualization tools, staffing, and more is no small effort.

Solution:
Pull resources from one of your existing business areas that have the talent needed to implement, oversee, and sustain the data initiatives. Find vendor partners that can help your organization achieve their data objectives within your organization’s allowed budget. When devising your budget, keep in mind some critical items that will need to be funded:

  • Data warehouse/analytics platform
  • Staff time in implementation, maintenance, and analyzing and preparing reports
    • Try to quantify how much time staff currently spend producing reports
  • Consultants and partners to manage various aspects of the project from strategy to implementation to ongoing data utilization.
  • Training staff on using data analytics tools, platforms, dashboards and report generation.
  • License fees and hosting subscription fees.

Stay tuned for Part 2 as we provide additional solutions to some of the top challenges CUs face with their data analytics programs.

Does your credit union face some of these challenges?   Data analytics can be complex especially if you don’t have a roadmap in place to guide your strategy. But, if executed well, analytics systems can have an enormously positive impact on your business outcomes.

 The Knowlton Group can help. Our expertise, years of working with FIs on assessing and implementing a proven data analytics strategy can work for you.

Contact us today to learn how!

If you aren’t familiar with SQL Server Reporting Services (SSRS), please take a moment and read this articles on SQL Server Reporting Services (SSRS) from Microsoft. This post will give briefly introduce you to SSRS and describe some of its capabilities. One of the most important benefits of SSRS is that it is included in nearly every SQL Server installation. That means you don’t need to purchase any additional business intelligence or visualization software. If utilized correctly, SSRS can provide a significant cost savings to organizations that don’t necessarily want to spend money on other visualization and dashboard solutions.

Why build an SSRS report?

The vast majority of credit unions, community banks, and regional banks tend to use Microsoft SQL Server for most of their databases. While you will certainly see instances of Oracle or IBM databases, Microsoft is going to be the most prevalent database employed. One of Microsoft SQL Server’s built-in functionalities is SQL Server Report Services or SSRS. SSRS’s browser-based report viewing and management interface has a tremendous amount of reporting features that can be leveraged by your organization.

SSRS enables us to automate the rendering and creation of reports that we frequently generate. We can create a report that will automatically populate a table based on the SQL query we are using behind the scenes to retrieve results and return data. An SSRS report also allows you to specify parameters and variables at runtime to change the results of the report based on drop down lists, date selections, or other data types that we will discussed in later posts.

Think of your lending reports for a moment. Perhaps you have a report for pending loan applications, approved loan applications, declined loan applications, and funded loan applications. The data elements in each of those reports might be identical, yet you are stuck running four separate reports independently. With SSRS, you could create one report and simply have the data be filtered by a “Loan Status” dropdown menu. Report consolidations like this are one of the most tangible benefits of using SSRS.

An SSRS report has many benefits and uses beyond what we briefly introduced. As this series unfolds, we will discuss the various ways you can use SSRS to improve reporting, reduce manual time and effort, and deliver the results faster and more accurately to your report recipients.

Creating Our First Report

Let’s finally start creating our first report. Your organization should be able to set you up with access to SQL or with either SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS). SSDT or BIDS will be the program we use to create our reports. Whether you use SSDT or BIDS depends on your version of SQL Server (SQL Server 2008R2 and below uses BIDS while newer versions use SSDT). These programs are very similar. I will be using SSDT for these posts, but you should be able to follow along if you will be using BIDS.

First, open SSDT (or BIDS) which can be found in your “Start Menu”. The application should open up and show the “Start Page” on the screen.

SSDT_Main_Screen

With the “Start Page” open, click the “New Project” hyperlink just above the “Recent Projects” header on the left side of the “Start Page”. The “New Project” screen will appear. Looking at the “Installed Templates” section of the left sidebar, ensure that “Business Intelligence” is selected. From there, select “Report Server Project” from the center panel. Towards the bottom of the window, enter the name “Credit Union Reports” in the “Name:” text box.

New_Project_Window

You can change where this project will be saved if you wish, but I will leave the default location set. Once this has been done, press “OK”.

Creating an SSRS Report Item

When the “New Project” window disappears, right click on the “Reports” folder on the right sidebar, hover over “Add”, and then select “New Item”.

Create_New_Report_Item

If you select “Add New Report” instead of hovering over “Add”, the “Report Wizard” screen will appear. I prefer to avoid using the wizard (especially when showing new users SSRS report potential) when possible.

When the “Add New Item” window appears, make sure that “Report” is selected in the center panel. In the “Name” text box towards the bottom of the window, we will call the report “Share Accounts by Branch”. The “.rdl” usually will automatically appear – this is the file extension for any SSRS report.

Add_New_Item

Your screen will now look slightly different than before since a blank report form will appear.

Blank_Report_Window

Creating a Data Source and Dataset

When we create any report, there are two basic components we need to consider: the data and the report items. The data consists of the various data sources and datasets that we will use to populate our reports. Report items include the various ways we can visualize our data; these items include tables, matrices, maps, charts, and others. Every report needs data to be visualized and then report items in which the data is rendered.

Going back to our example, we will create a data source. This will tell our dataset (which will we create next) from which SQL Server instance and database will we retrieve data. To do this, right click on the “Data Sources” folder on the left “Report Data” sidebar, and then select “Add Data Source”. The “Data Source Properties” window will appear on your screen.

Add_New_Data_Source

Change the name of the data source, in the “Name” text box, to “CU Database”. Next, press “Edit” to the right of the “Connection String” text box. When you do this, the “Connection Properties” window will appear on your screen. In this screen, we will define the SQL Server Instance in the “Server Name” drop down along with the database to which will be connecting. In the “Server Name” box, type “localhost”. You can also type a period in this box. This will tell SSDT to find the SQL Server instance running on the machine that you are currently on. If your SQL Server Instance is on another server on the network, you will need to get the server name from your IT or networks team.

Once you enter “localhost” or “.” in the “Server Name” box, click the arrow in the “Select or enter a database name” box. This dropdown box will contain a list of databases on the server to which you have been granted access. I will be connecting to the database named “CreditUnion”.

Connection_Properties

Once you have selected the database, press “OK”.

The “Data Source Properties” window will now look a little different since the connection properties have been filled in.

Data_Source_Properties_after_Connection

Press “OK” to create the data source.

Now that we have created a data source, we need to create a dataset. A dataset is where we define our SQL code that will retrieve our data from the data source we just defined. To create the dataset, right click on the “Datasets” folder and then select “Add Dataset”. The “Dataset Properties” window should now appear on your screen.

Dataset_Properties

In the name text box, change the name of this dataset to “ShareAccounts_by_Branch”. Below, select the “Use a dataset embedded in my report” radio button (as opposed to the default “use a shared dataset”). In the “Data Source” dropdown box that appear, select “CU Database” which is the name of the data source we just finished creating.

In the “Query” text box, enter the SQL code that will define the data that you retrieve. My query will return a row for each branch and three columns: the branch name, the number of share accounts opened at that branch, and the current balance of all shares that were opened at that branch. As mentioned in the credit union business intelligence series introduction, this data has been generated randomly by a script I created. There is NO real member data here and all accounts, balances, social security numbers, names, address, and all other related data attributes have been randomly generated via SQL or through random name generators online.

Dataset_Properties_with_Query

With the query defined, press “OK” to save the dataset.

If you look at the “Report Data” panel on the left side of the screen, you will notice that the data source and dataset we have created now populate their respective folders. With the data source and dataset now defined, we need to create a report item to visualize the data we wish to retrieve.

Adding a Table to our SSRS Report

Report items can be added a couple different ways. First, you can hover over the “Toolbox” tab on the left sidebar and then drag an item from the toolbar onto the blank row. The other method, which I prefer, is to right click on white space in the report, hover over “Insert”, and choose the item I wish to add to my report. For this example, we are simply going to add a table to our report. So, using one of the two methods described, add a blank table to the report.

Add_New_Table

Let’s move the newly created table to the upper-left corner of the report. To select the table either click on one of the outer edges of the table, or select a cell within the table and then press the small box in the upper left corner that appears (see image below):

SELECT_TABLE

Once you have selected the table, click on the move table icon Move_Table_Icon and drag the table to the upper left corner of the report.

MOVED_TABLE

Now that we have the table created, we need to tell the table what data to display when we run the report. To do this, we need to specify which column from our dataset will appear in what column from the table. Simply move your cursor over one of the cells in the “Data” row of the table until the small table icon appears on the top right corner of the cell. Table_Icon.

Click on the small table icon once it appears. A list of all columns from the dataset will appear in a menu. Choose what dataset column you would like to appear in the table column that you initially hovered over. In this example, I will put the “Branch_Name” column in the first name column, the “Count_of_Shares” column in the second column, and the “Current_Share_Balance” column in the third column.

TABLES_WITH_COLUMNS_SPECIFIED

You will notice a few things immediately. First, the column header is automatically added to the table. Second, SSDT and BIDS will automatically interpret an underscore as a space and adjust the name of the column accordingly. Before we actually run this report for the first time, let’s adjust the size of columns and make the column headers bold. To adjust the column width, hover your cursor over the edge of the grey boxes that appear above column header row until the resize cursor appears. Drag the columns to the appropriate width. To make the column headers bold, click on the top left column header cell (“Branch Name” in my example), hold the shift key, and then click on the top right column head cell (“Current Share Balance”).

WIDTH_ADJUSTED_AND_COLUMNS_HIGHLIGHTED

Once highlighted, press the bold symbol in the top toolbar section of SSDT or BIDS that typically is to the right of the font selection dropdown menu.

BOLDED_HEADERS

At this point, we can run the report to see what it looks like. Just above the report form are two tabs: “Design” and “Preview”. We can edit the report when the “Design” tab is selected, and we can preview what the report will look like by selecting the “Preview” tab. Select the “Preview” tab now.

REPORT_FIRST_PREVIEW

This is our first preview of the report! As you can see, the data from our dataset has been placed in the appropriate columns of our table. We will do one last formatting edit before we wrap up this post. If you look at the “Count of Shares” and “Current Share Balance” columns, neither of them are formatted properly with commas and/or dollar signs where necessary.

To leave the report preview, click the “Design” tab.

To make commas appear properly in the “Count of Shares” column, right click on the column in the “Data” row (the bottom row), and then select “Text Box Properties”.

COUNT_OF_SHARES_RIGHT_CLICK

When the “Text Box Properties” window appears, select the “Number” option on the left sidebar. In the “Category” list that appears in the main part of the window, choose “Number” instead of “Default”. When the formatting options appear, click the checkbox that says “Use 1000 separator”.

COUNT_TEXT_BOX_PROPERTIES

Press “OK” once this has been completed.

To format the “Current Share Balance” column, we need to follow a similar process. Right click in the text box, select “Text Box Properties” and then choose the “Number” option from the left toolbar of the “Text Box Properties” window that appears. This time, choose “Currency” instead of “Number”. Be sure to click in the checkbox next to “Use 1000 separator”. Press “OK” once completed.

SHARE_BALANCE_TEXT_BOX_PROPERTIES

Select the “Preview” tab just above the report editor to view the formatting changes we have made.

SECOND_PREVIEW

Notice how the numbers are properly formatted as we defined them in the “text box properties” for each column!


This concludes our first credit union business intelligence series post! This lesson has introduced you to SSRS and taught you how to create a very basic SSRS report. As the lessons develop over the coming weeks, our SSRS reports will become more complicated and include features like parameters, dynamic filtering, and drill-downs.

Your comments and questions are always appreciated. If you have any questions at all, please email [email protected], and we will answer your question as soon as we possible can!