Database Reference
In-Depth Information
The final reason for the Microsoft column store being so fast is that it is stored
in memory.
With the advent of much better performance on SQL Server, including the
introduction of a column store index that has all the benefits of the engine
spoken that was previously described, the other benefits of using an analytic
database become much more important.
The second benefit of analytic databases is the creation of what Microsoft
once called a Unified Dimension Model, and now calls a Business Intelligence
Semantic Layer. This layer abstracts the naming conventions of the underly-
ing database and allows for meaningful, business-friendly naming, as well as
abstracting the structures, aggregations, and calculations. The main benefit
of this is that a single maintenance point is created. If the calculation of Net
profit changes, it can be maintained in a single place and not have every
single report require changes.
The third benefit of analytic databases rolls on from the second: Report writers
and self-service users do not need to know the underlying structures or how
to write SQL queries against them, nor do they need to know the calculations
used. Instead, if they are working in a tool such as Excel, they can simply drag
the Net profit measure onto a pivot table and then drag the location hierarchy
onto the rows and have a basic report with drill-down capabilities.
In today's world, with the hardware capabilities available, the second and
third benefits are often greater than the first—at least in the enterprise space
that has a BI team.
In the organizations that don't have BI teams, an organization's ability to
build its own “analytic databases” or cubes is important. This is where the
tool PowerPivot comes in.
PowerPivot was first introduced as a downloadable add-in for Excel 2010
and an installable add-in for SharePoint (run from the SQL 2008 R2 install)
called PowerPivot Services. The engine running underneath both of these is
the Microsoft column store implementation then called VertiPaq—the same
column-store you have just read about.
In the SQL 2012 release, these options were expanded to include an Analysis
Services instance that doesn't require SharePoint. The engine was renamed
xVelocity (a naming choice this particular author doesn't like—Vertipaq was
Search WWH ::




Custom Search