TechTip: Creating Local OLAP Cube Data Files with DB2 Data

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Give users offline access to DB2 data in a versatile pivot table format.


In "Crunching AS/400 Data with OLAP Cubes and Excel 2000," I outlined how to use the Pivot Table wizard with Excel 2000 (and higher) to create pivot tables from DB2 data using an ODBC data source. This TechTip will go a step further.


Pivot tables are extremely useful because they allow a user to aggregate data in innumerable ways. For instance, sales dollars and quantity can be summarized by calendar quarter, by customer, by item classification, etc. Pivot tables, therefore, relieve IT from writing the same basic report over and over. (How many times have developers written the same basic report, just summarized a little differently?) Instead, pivot tables allow IT to collect the basic data and leave it to the user to summarize and format it as desired.


In this TechTip, I'll demonstrate how a script process can create a local OLAP cube file from DB2 data without using the Excel interface. (An OLAP cube file is a specialized file that stores aggregations in an optimized format.) Once a cube file is created, it can be distributed via a network share, emailed, or posted on a Web site for users to access and review using pivot tables within Excel. Since this is a script process, the cube data build can be scheduled to refresh at regular intervals. Furthermore, creating a local cube file alleviates the need for the user to have DB2 connectivity software and to go through the complicated Create Cube wizard.


I'll demonstrate the technique to build a cube file using VBScript, although you could use VBA, .NET, or any other development environment that can take advantage of the ActiveX Data Objects library. The ODBC component from the System i Access CD (formerly iSeries Access) is required to be installed on the computer that will create the cube file. Alternatively, a third-party ODBC driver can be substituted. Keep in mind that almost any database server in your organization can be used as a data source for an offline cube.


You computer should also have the latest Microsoft Data Access Components (MDAC) library installed, along with a version of Excel (2000 or higher.) Most computers already have a recent MDAC version installed.

The Data

Since IBM hasn't given DB2 for i users a large test data set to tinker with (hint to IBM: the CORPDATA sample data is good, but we need more data!), I decided to re-use the data from the original article, an export of Microsoft's Northwind Traders Access database to DB2. The Northwind database stores customer order data. Figure 1 below shows the tables and relationships in the Northwind schema.



Figure 1: These are the tables and relationships in the Northwind schema example. (Click images to enlarge.)


A Quick Review

How can we make this relational data useful to management? One way is to create a cube file that will "pre-aggregate" the sales data, such as quantity and extended price (aka measures) by a variety of summarization columns (aka dimensions) such as item number, item description, item category, and customer name. Once the data is pre-aggregated in a local cube file, we can allow users to quickly review the aggregations in Excel using pivot tables (more about that in a minute).


But first, remember that when defining a cube we need to define what is being aggregated (sometimes called measures, facts, or values) and how to summarize these aggregates (called dimensions or labels). The table below shows a list of measures and dimensions for the sample cube.


Sample Cube Definition


Measures / Aggregate

Product Category




Extended Price



Highest Price


Order Date







When this cube is built, it will contain summarizations of the aggregates by all combinations of dimensions. For example, a user could ask for total quantity and sales by supplier, by country within product, etc., and the cube will give an answer. This example is simple, but other meaningful measures, such as gross profit (extended price minus cost), could be added to the cube as a measure.


If you find yourself lost at this point, it is probably a good idea to review the prior article. Here is a warning from the previous article when designing a cube: local cube files are limited in scalability, so choose your dimension combinations wisely. Each additional dimension exponentially bumps up the number of aggregations done and increases the cube file size, processing time, and wait time accordingly.

A Brief Review of Pivot Tables

What is a pivot table? A pivot table is a tool in Excel that allows summarization, sorting, and analysis of data from a data source. Possible data sources to feed a pivot table are a range of cells in a spreadsheet or, you guessed it, an OLAP cube file.


The best way to talk about pivot table is to show one. Figure 2 shows an Excel 2007 spreadsheet containing a pivot table based on our sample cube.



Figure 2: This Excel spreadsheet contains a pivot table based on our sample cube.


As you can see, the Extended Price column (i.e., sales data) is summarized by date (columns) and product category (rows). The Country box at the top left is called a report filter. In this case, the report filter can be used to drill down to one or more countries.


The beauty is that, by a simple drag-and-drop operation, the data can be summarized in another way. Instead of summarizing rows by product category, we can summarize by supplier or customer or customer within product. If we wanted, we could put the order date (year, quarter, etc.) as a report filter in place of country. The possibilities are endless, and users can do this themselves so that IT doesn't have to write yet another variation on an existing report.


For those who need pivot table help, there are plenty of tutorials in the Microsoft help and on the Web.

Building a Local Cube Offline

In the prior article, we used the "Microsoft Query add-in for Excel" to build the cube data within Excel. Now however, we want to write a program to build a cube. In order to do this, we'll need the following:

•·        A connection to the cube's data source (in this case, ODBC will get data from DB2)

•·        A cube definition that defines the cube's measures and dimensions

•·        A DB2 SQL statement that can gather all of the required cube information into a single result set


With these things in mind, look at the end of this TechTip for the sample VBScript code (CreateSalesCube.vbs) required to build a cube file.


A local cube file is created using a specialized OLE DB provider from Microsoft called MSOLAP. As with any OLE DB provider, to use MSOLAP, you need to do the following:

•·        Instantiate an ActiveX Data Objects (ADO) connection object. (ADO is a standard Microsoft technology that allows a data connection to be made to a variety of data sources, usually database servers but also Exchange, Active Directory, and spreadsheets. An OLE DB provider is a vendor-specific plug-in to the ADO library.)

•·        Set a connection string to a data source.

•·        Open the connection.


Once the connection is opened, a program performs operations against a data source and then closes the connection.


The great thing about using MSOLAP is that it will build a local cube file from a remote data source using instructions specified all within the connection string. Once the connection is opened, the local cube will be built with no subsequent instructions. Therefore, the VBScript program does the following four steps:

•·        Create a connection object.

•·        Set the connection string to create a cube from a remote data source.

•·        Open the connection (which executes the connection string instructions).

•·        Close the connection.


Now that's easy enough to understand, but the connection string itself needs explanation. Not your typical connection string, the connection string for MSOLAP is divided into the following sections:

•·        LOCATION identifies where the local cube file will be created. It can be a drive path or a UNC path.

•·        SOURCE_DSN is the ODBC data source name to use when executing the underlying data source query (in this case, it will be an ODBC DSN called AS400).

•·        CREATECUBE is the statement that defines the structure of the cube, including the measures and dimensions. A tutorial on this topic would be extensive, but I will give a hint on an easy way to do this! After conceptually designing my local cube and building a supporting SQL statement, I use the Microsoft Query cube wizard to build this statement for me! (This method is outlined in the previous article.) The file name for an OLAP query created by MS Query is .OQY. Once you've created an .OQY file, simply open it with Notepad and extract the CREATECUBE section.

•·        INSERTINTO is the statement that populates the cube from the supplied SQL statement. The DB2 SQL joins various tables to get all the relevant data for the cube. As a side note, if your dimension columns have the possibility of containing a NULL, it is a good idea to modify your SQL to use a function such as COALESCE to replace NULLs with a value such as *Undefined*. Also notice here that the number and sequence of columns in the SELECT statement should correspond with the cube's columns.


With a mammoth connection string like that, it's no wonder all the work is done when the connection is opened!

What to Do with the Local Cube File

Once a cube file is created, there are a number of options for getting the data to the users. Here are a few ideas:

1.      Cube files can be opened directly by Excel using the File-Open dialog. Unfortunately, the Excel installation process doesn't automatically associate the cube (.CUB) extension with the Excel application. However, this can be easily remedied by associating Excel with a .CUB file extension using Windows Explorer. Also, if Excel squawks about a data source being potentially unsafe, just tell Excel to "get over it" and move on. When opening a cube file directly, Excel will present an empty pivot table.

2.      Cube files can be placed on a network drive for access by Excel users. If my memory serves me, though, with Excel 2000 only one user at a time can access a cube file.

3.      The cube creation script can be enhanced to zip and distribute cube files to users via email. This is a good solution for road warrior sales personnel or busy execs who can't necessarily connect directly to the DB2 server but may want to analyze current numbers.

4.      If the network configuration allows, copy the cube file right to a user's "My Documents" folder for easy access.

5.      Create a pre-built spreadsheet with pivot tables and pivot charts that reference the cube data on a network share. This option alleviates the need to have users start the pivot table process every time they open a cube file. In fact, a workbook can be created that has several predefined pivot table views that can serve as a starting point for users.

6.      It is a good idea to schedule the cube to be rebuilt automatically for a sensible period of time (depending on the application) such as daily, weekly, or monthly.


Number Crunching the Fun Way

The combination of OLAP cubes and pivot tables is compelling because it allows users to create their own report variations with existing tools. This allows developers to do more useful things than writing a million variations of a single report. The cube file also allows quite a bit of data to be analyzed without having to purchase an OLAP product.


OLAP cubes allow DB2 users to crunch data offline in a flexible and easy-to-use format. With a little training, the bean counters and sales reps in your company will love them!


Sample VBScript (CreateSalesCube.vbs) Code Required to Build a Cube File



' CreateSalesCube.VBS




' This script is intended to build

' a local OLAP cube file (.CUB) from

' "DB2 for i" data on a regular basis.



' Michael Sansoterra    09/20/2008





' Build Connection String

' (4 Components required for MSOLAP provider)


' 1.  Location of Cube


Dim ConnLocation



' 2.  ODBC Data Source Name

'     (Watch for those embedded double quotes!)


'     Place your own DSN name, credentials and other

'     connection string keywords here.



'     This example assumes AS400 uses the iSeries Access

'     (aka System i Access) ODBC Driver


Dim ConnDSN



' 3.  Create Cube Statement (Define Dimensions & Measures)


Dim ConnCreateCube

ConnCreateCube = _


"DIMENSION [Customer]," & _

"    LEVEL [All] TYPE ALL," & _

"    LEVEL [CompanyName]," & _

"DIMENSION [Country]," & _

"    LEVEL [All] TYPE ALL," & _

"    LEVEL [Country]," & _

"    LEVEL [Region], " & _

"DIMENSION [Product]," & _

"    LEVEL [All] TYPE ALL," & _

"    LEVEL [ProductName]," & _

"DIMENSION [Supplier]," & _

"    LEVEL [All] TYPE ALL," & _

"    LEVEL [CompanyName1]," & _

"DIMENSION [Category]," & _

"    LEVEL [All] TYPE ALL," & _

"    LEVEL [CategoryName]," & _

"DIMENSION [OrderDate] TYPE TIME," & _

"    LEVEL [All] TYPE ALL," & _

"    LEVEL [Year] TYPE YEAR," & _

"    LEVEL [Quarter] TYPE QUARTER," & _

"    LEVEL [Month] TYPE MONTH," & _

"   MEASURE [Quantity]    FUNCTION SUM," & _

"   MEASURE [Ext Price]   FUNCTION SUM," & _

"   MEASURE [Highest Price] FUNCTION MAX);"


' 4. Insert Data into Cube


Dim ConnInsertCube

ConnInsertCube="INSERTINTO=INSERT INTO OrderCube(" & _

"[Quantity], [Ext Price], [Highest Price], [CompanyName], " & _

"[Country].[Country], [Region], [ProductName], " & _

"[CompanyName1], [CategoryName], [OrderDate])  " & _


"SELECT OD.Quantity, " & _

"           OD.UnitPrice*OD.Quantity ExtPrice1," & _

"           OD.UnitPrice*OD.Quantity ExtPrice2," & _

"           CS.CompanyName, CS.Country," & _

"           CS.Region, PD.ProductName," & _

"           SP.CompanyName," & _

"           CT.CategoryName, OH.OrderDate" & _

"      FROM Northwind.OrderDetails OD " & _

"INNER JOIN Northwind.Orders       OH ON OH.OrderID=OD.OrderID " & _

"INNER JOIN Northwind.Products     PD ON PD.ProductID=OD.ProductID " & _

"INNER JOIN Northwind.Suppliers    SP ON SP.SupplierID=PD.SupplierID " & _

"INNER JOIN Northwind.Customers    CS ON CS.CustomerID=OH.CustomerID " & _

"INNER JOIN Northwind.Categories   CT ON CT.CategoryID=PD.CategoryID "


' Define ADO Connection Object

' Set the connection provider to MSOLAP


Dim Connection

Set Connection = CreateObject("ADODB.Connection")

Connection.Provider = "msolap"


' Combine 4 components of the MSOLAP connection string

' into one


Connection.ConnectionString = _

    ConnLocation & _

    ConnDSN & _

    ConnCreateCube & _



' Open the connection (this will create the cube and

' populate the cube from the remote data source.  The

' provider works to pre-aggregate the cube's measures by

' the various combinations of the cube's dimensions)




' Close The Connection



Set Connection=Nothing


' Signal user the script is done


MsgBox "Cube has been created!"

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at



Support MC Press Online





  • White Paper: Node.js for Enterprise IBM i Modernization

    SB Profound WP 5539

    If your business is thinking about modernizing your legacy IBM i (also known as AS/400 or iSeries) applications, you will want to read this white paper first!

    Download this paper and learn how Node.js can ensure that you:
    - Modernize on-time and budget - no more lengthy, costly, disruptive app rewrites!
    - Retain your IBM i systems of record
    - Find and hire new development talent
    - Integrate new Node.js applications with your existing RPG, Java, .Net, and PHP apps
    - Extend your IBM i capabilties to include Watson API, Cloud, and Internet of Things

    Read Node.js for Enterprise IBM i Modernization Now!


  • Profound Logic Solution Guide

    SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation.
    Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects.
    The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the companyare not aligned with the current IT environment.

    Get your copy of this important guide today!


  • 2022 IBM i Marketplace Survey Results

    Fortra2022 marks the eighth edition of the IBM i Marketplace Survey Results. Each year, Fortra captures data on how businesses use the IBM i platform and the IT and cybersecurity initiatives it supports.

    Over the years, this survey has become a true industry benchmark, revealing to readers the trends that are shaping and driving the market and providing insight into what the future may bring for this technology.

  • Brunswick bowls a perfect 300 with LANSA!

    FortraBrunswick is the leader in bowling products, services, and industry expertise for the development and renovation of new and existing bowling centers and mixed-use recreation facilities across the entertainment industry. However, the lifeblood of Brunswick’s capital equipment business was running on a 15-year-old software application written in Visual Basic 6 (VB6) with a SQL Server back-end. The application was at the end of its life and needed to be replaced.
    With the help of Visual LANSA, they found an easy-to-use, long-term platform that enabled their team to collaborate, innovate, and integrate with existing systems and databases within a single platform.
    Read the case study to learn how they achieved success and increased the speed of development by 30% with Visual LANSA.


  • The Power of Coding in a Low-Code Solution

    LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed.
    Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • Why Migrate When You Can Modernize?

    LANSABusiness users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.
    In this white paper, you’ll learn how to think of these issues as opportunities rather than problems. We’ll explore motivations to migrate or modernize, their risks and considerations you should be aware of before embarking on a (migration or modernization) project.
    Lastly, we’ll discuss how modernizing IBM i applications with optimized business workflows, integration with other technologies and new mobile and web user interfaces will enable IT – and the business – to experience time-added value and much more.


  • UPDATED: Developer Kit: Making a Business Case for Modernization and Beyond

    Profound Logic Software, Inc.Having trouble getting management approval for modernization projects? The problem may be you're not speaking enough "business" to them.

    This Developer Kit provides you study-backed data and a ready-to-use business case template to help get your very next development project approved!

  • What to Do When Your AS/400 Talent Retires

    FortraIT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators is small.

    This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn:

    • Why IBM i skills depletion is a top concern
    • How leading organizations are coping
    • Where automation will make the biggest impact


  • Node.js on IBM i Webinar Series Pt. 2: Setting Up Your Development Tools

    Profound Logic Software, Inc.Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. In Part 2, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Attend this webinar to learn:

    • Different tools to develop Node.js applications on IBM i
    • Debugging Node.js
    • The basics of Git and tools to help those new to it
    • Using as a pre-built development environment



  • Expert Tips for IBM i Security: Beyond the Basics

    SB PowerTech WC GenericIn this session, IBM i security expert Robin Tatam provides a quick recap of IBM i security basics and guides you through some advanced cybersecurity techniques that can help you take data protection to the next level. Robin will cover:

    • Reducing the risk posed by special authorities
    • Establishing object-level security
    • Overseeing user actions and data access

    Don't miss this chance to take your knowledge of IBM i security beyond the basics.



  • 5 IBM i Security Quick Wins

    SB PowerTech WC GenericIn today’s threat landscape, upper management is laser-focused on cybersecurity. You need to make progress in securing your systems—and make it fast.
    There’s no shortage of actions you could take, but what tactics will actually deliver the results you need? And how can you find a security strategy that fits your budget and time constraints?
    Join top IBM i security expert Robin Tatam as he outlines the five fastest and most impactful changes you can make to strengthen IBM i security this year.
    Your system didn’t become unsecure overnight and you won’t be able to turn it around overnight either. But quick wins are possible with IBM i security, and Robin Tatam will show you how to achieve them.

  • Security Bulletin: Malware Infection Discovered on IBM i Server!

    SB PowerTech WC GenericMalicious programs can bring entire businesses to their knees—and IBM i shops are not immune. It’s critical to grasp the true impact malware can have on IBM i and the network that connects to it. Attend this webinar to gain a thorough understanding of the relationships between:

    • Viruses, native objects, and the integrated file system (IFS)
    • Power Systems and Windows-based viruses and malware
    • PC-based anti-virus scanning versus native IBM i scanning

    There are a number of ways you can minimize your exposure to viruses. IBM i security expert Sandi Moore explains the facts, including how to ensure you're fully protected and compliant with regulations such as PCI.



  • Encryption on IBM i Simplified

    SB PowerTech WC GenericDB2 Field Procedures (FieldProcs) were introduced in IBM i 7.1 and have greatly simplified encryption, often without requiring any application changes. Now you can quickly encrypt sensitive data on the IBM i including PII, PCI, PHI data in your physical files and tables.
    Watch this webinar to learn how you can quickly implement encryption on the IBM i. During the webinar, security expert Robin Tatam will show you how to:

    • Use Field Procedures to automate encryption and decryption
    • Restrict and mask field level access by user or group
    • Meet compliance requirements with effective key management and audit trails


  • Lessons Learned from IBM i Cyber Attacks

    SB PowerTech WC GenericDespite the many options IBM has provided to protect your systems and data, many organizations still struggle to apply appropriate security controls.
    In this webinar, you'll get insight into how the criminals accessed these systems, the fallout from these attacks, and how the incidents could have been avoided by following security best practices.

    • Learn which security gaps cyber criminals love most
    • Find out how other IBM i organizations have fallen victim
    • Get the details on policies and processes you can implement to protect your organization, even when staff works from home

    You will learn the steps you can take to avoid the mistakes made in these examples, as well as other inadequate and misconfigured settings that put businesses at risk.



  • The Power of Coding in a Low-Code Solution

    SB PowerTech WC GenericWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed.
    Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

    • Discover the benefits of Low-code's quick application creation
    • Understand the differences in model-based and language-based Low-Code platforms
    • Explore the strengths of LANSA's Low-Code Solution to Low-Code’s biggest drawbacks



  • The Biggest Mistakes in IBM i Security

    SB Profound WC Generic The Biggest Mistakes in IBM i Security
    Here’s the harsh reality: cybersecurity pros have to get their jobs right every single day, while an attacker only has to succeed once to do incredible damage.
    Whether that’s thousands of exposed records, millions of dollars in fines and legal fees, or diminished share value, it’s easy to judge organizations that fall victim. IBM i enjoys an enviable reputation for security, but no system is impervious to mistakes.
    Join this webinar to learn about the biggest errors made when securing a Power Systems server.
    This knowledge is critical for ensuring integrity of your application data and preventing you from becoming the next Equifax. It’s also essential for complying with all formal regulations, including SOX, PCI, GDPR, and HIPAA
    Watch Now.

  • Comply in 5! Well, actually UNDER 5 minutes!!

    SB CYBRA PPL 5382

    TRY the one package that solves all your document design and printing challenges on all your platforms.

    Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product.

    Request your trial now!

  • Backup and Recovery on IBM i: Your Strategy for the Unexpected

    FortraRobot automates the routine tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:
    - Simplified backup procedures
    - Easy data encryption
    - Save media management
    - Guided restoration
    - Seamless product integration
    Make sure your data survives when catastrophe hits. Try the Robot Backup and Recovery Solution FREE for 30 days.

  • Manage IBM i Messages by Exception with Robot

    SB HelpSystems SC 5413Managing messages on your IBM i can be more than a full-time job if you have to do it manually. How can you be sure you won’t miss important system events?
    Automate your message center with the Robot Message Management Solution. Key features include:
    - Automated message management
    - Tailored notifications and automatic escalation
    - System-wide control of your IBM i partitions
    - Two-way system notifications from your mobile device
    - Seamless product integration
    Try the Robot Message Management Solution FREE for 30 days.

  • Easiest Way to Save Money? Stop Printing IBM i Reports

    FortraRobot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing.
    Manage your reports with the Robot Report Management Solution. Key features include:

    - Automated report distribution
    - View online without delay
    - Browser interface to make notes
    - Custom retention capabilities
    - Seamless product integration
    Rerun another report? Never again. Try the Robot Report Management Solution FREE for 30 days.

  • Hassle-Free IBM i Operations around the Clock

    SB HelpSystems SC 5413For over 30 years, Robot has been a leader in systems management for IBM i.
    Manage your job schedule with the Robot Job Scheduling Solution. Key features include:
    - Automated batch, interactive, and cross-platform scheduling
    - Event-driven dependency processing
    - Centralized monitoring and reporting
    - Audit log and ready-to-use reports
    - Seamless product integration
    Scale your software, not your staff. Try the Robot Job Scheduling Solution FREE for 30 days.