Data source options for a data cube


1. Overview

This article describes the different kinds of inputs you can use to create a data cube.

2. Data source options

2.1. OLAP cube

If your data is already available as an OLAP cube, that is probably the best option because aggregates are already calculated.

For example, use the MDX Select transform to select specific measures and dimensions from a SQL Server Analysis Services (SSAS) cube.

2.2. SQL databases

If no OLAP cube is available, using some sort of SQL solution such as Microsoft SQL Server or Oracle would be the next best option, and ideally, the data comes from the same server and the same database.

Some data cube transforms, such as Join, can create optimized queries to the server, and SQL Server for example will receive a query with a join statement directly in it.

2.3. Stored Procedures and Manual Queries

If you are comfortable with writing your own SQL queries, then write a stored procedure and use the Stored Procedure Select transform. Or, write a manual query using the Manual Select transform.

Performing filtering in your stored procedure or manual query is better than using the Filter transform because you would be limiting the number of records at the data source.

2.4. Table-valued Functions versus Stored Procedures

In SQL Server, a table-valued function can replace a stored procedure that returns a single result set. In Dundas BI, using a table-valued function can in cases give you much better performance than using a stored procedure. For example, when you use a stored procedure, hierarchy promotion will be done in memory on the Dundas BI server. But if you use a table-valued function instead, this hierarchy promotion will be done by SQL Server itself and result in much greater performance especially if your hierarchy has a lot of members.

When you drag a table-valued function from a SQL Server data connector to the data cube canvas, you'll get a Tabular Select transform.

2.5. Database Views

Using database views can also be a good option because in SQL Server you can create indexes on views.

3. Multiple data sources

If you need to use data from multiple data sources, or if you are using flat files or XML data, then you'll need to use the various data cube transforms (such as Join and Filter) to process the data.

4. In-memory and warehouse storage

When working with data sources such as Excel and flat files using UNC connections, note that data is not real-time data (the files get generated at some sort of interval) and great performance improvements can be achieved using the warehousing/in-memory features of the data cube.

Uploaded Excel and flat files are only updated by a new upload, and are automatically warehoused after the first use.

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri