Can a SQL Server OLAP cube be loaded from csv files

Question

I have never used or implemented an OLAP cube before, so I'm basically a beginner with this technology.

I am analyzing a project that is converting scanned documents to an on-line MS SQL Server database. From the research I have done, OLAP cubes appear to offer substantially faster queries than OLTP databases. So, using an OLAP cube appears to be a better choice performance wise.

But, I have only found examples that show how to load an OLAP cube with data from database tables. I have not been able to find any examples of loading data from csv files using tools like BCP or Bulk Insert for OLAP cubes.

Setting up an OLTP database first is possible, but it would only be used to load the OLAP cube. This can certainly be done, but I just wanted to make certain that there isn't an easier way to load an OLAP cube directly with csv files first.

So, does SQL Server provide a way to load an OLAP cube with csv files or does an OLAP cube have to be loaded from an existing OLTP database?

Answer 1

Traditionally, OLAP cubes sit on top of OLTP "data warehouses". There are a number of advantages of loading your data into an OLTP database before loading it into an OLAP cube. This process is known as "ETL" (Extract, Transform, Load). For more information, search for "Ralph Kimball" or "Bill Inmon". There is a lot of literature on how to design and build data warehouses and dimensional models ("star schemas").

If you want to use SQL Server Analysis Services for your OLAP cube, you have the choice between SSAS multidimensional and SSAS tabular. Currently, SSAS multidimensional does not support loading data from anything other than SQL Server database tables, whereas SSAS tabular supports a number of sources (including flat files). Even so, the recommended approach is to load the data from a relational database, and then use some other tool (for example, SQL Server Integration Services, SSIS), to perform the "ETL", to get the data from the source into the database.