Creating PowerPivot data sources

As I have demonstrated there are a lot of things about PowerBI to get excited about. One of these is Microsoft’s adaption of the technology xVelocity. The true power behind PowerPivot is the xVelocity in-memory analytics engine.

Why XVelocity is important

Before Microsoft’s introduction of xVelocity most of the report offerings was via SSRS, ASP.NET, and Performance Point Services. SSRS an ASP.Net uses a lightweight development model. These visualization tools relied on data sources like SQL Server that traditionally was stored on hard disks.  Hard disks are relatively slow. Whenever system must use resources stored on disk, this will impact the response time of the report or dashboard. This is why Microsoft now uses xVelocity.  xVelocity gives you two very important things, faster dataset creation (via PowerPivot) and the faster response from visualization tools that rely on these datasets.

PowerPivot as a data source

The power to create PowerPivot data sources cannot be understated. With this technology you can create a centralized data source that encompasses various data sources. You can create a PowerPivot data source that is comprised of text files, Excel worksheets, and database tables into one data source. In my example, I’m going to demonstrate how to do this with SQL Server. PowerPivot data source differ from others because its dataset is stored entirely in memory. Again, this is due to the xVelocity technology.

PowerPivot is essentially the database “Tabular” model

Tabular model is a database design much like OLAP Cubes. However, Tabular cannot totally replace MDX cubes. The main reason is due to the memory requirements for tabular. Tabular models must be stored in memory while MDX can be stored on physical disks, essentially allowing an extremely large data sources. Unfortunately, the MDX language is more difficult to use and the OLAP cube development can be much more frustrating.  DAX, which is the expression language of Tabular/PowerPivot data sources, is easier to use and understand. Excel users will find DAX easy to adapt because of its familiarity to Excel expressions.