Data source options for a data cube
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
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.
2.6. Multiple data sources
2.7. 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.