Search code examples
sql-serverssascubesolap-cube

Is a single table a bad starting point for OLAP cubes (SQL Server Analysis Services)?

I'm going to use a single table to aggregate historical data about our (very big) virtual infrastructure. The table will be composed of 15 to 30 fields, and I esitmate from 500 to 1000 records a day.

Why a single table? A couple of reasons:

  • Data is extracted to csv using powershell scripts. Then bulk load on a single table is very easy and fast.
  • I will use the table to connect excel and report through pivot tables. Then a single table is perfect (otherwise I should create views).

Now my question:

If I'm planning in the future to build cubes upon this table is the "single-table" choice a bad solution? Do cubes rely on relational databases or they can be easily built upon single-table databases?

Thanks for any suggestion

Solution

  • Can't tell you specifically about SQL Server Analysis Services, but for OLAP you typically use denormalized and aggregated data. That means fewer tables than in a normal relational scenario. And as your data volume is not really big (365k rows/year - even small for OLAP), I don't see any problem using a single table for your data.