PowerPivot & Analysis Services – The Value of Both
Guest partner post by Shimon Shlevich, Product Manager at Panorama Software
As Microsoft PowerPivot is gaining more popularity and exposure, BI professionals ask more and more questions about PowerPivot’s role in the organization in trying to understand what value the new in-memory BI solution from Microsoft brings, along with the benefits and the limitations of it. Is PowerPivot going to replace SQL Server Analysis Services? If so, how soon? What should be done with the existing BI solution? Or maybe both can coexist and serve different needs?
In order to answer these questions and understand both short and long term impacts of the new products on your BI solution we need to understand what motivated Microsoft to release this new creature and where do they position it. Microsoft are trying to achieve two main goals – introduce a new in-memory engine for data processing and promote the self-service BI concept extending the usage of BI systems to a wider audience.
The new in-memory engine is called “Vertipaq”. Vertipaq is claimed to perform much better than classic SSAS engine doing the aggregations and calculations as well as temporary data storage in a computer’s RAM eliminating the slow disk lookup overhead. The first version of this engine is currently released as a part of both Microsoft Office Excel 2010 and the SQL Server 2008 R2 enabling SSAS to work either in classic or the new in-memory mode. The in-memory mode for SSAS is currently only available for PowerPivot created cubes and not for all your classic cubes, however, eventually the new engine will make it to a major SSAS release and will become the new default engine of the SSAS.
Meanwhile, classic SSAS is more functional than PowerPivot in terms of analytics and administration. SSAS has more semantics such as hierarchies, and more administration support such as robust data security functionality. SSAS is probably the richest multidimensional engine on the market today, scalable to support large data amounts and completely enterprise ready. The downside of these capabilities is that SSAS project requires design and planning of the BI solution, implementation, deployment, testing and additional phases. A team of BI developers, IT support, long development cycle and not that frequent updates result with a highly customized, less flexible solution which is good for years and relies on enterprise data which structure does not change that often.
Analysis Services is the corner stone of any corporate infrastructure and it enables users to analyze data that has already been pre-modeled for them by IT. So users can create standard reports, dashboards and KPI’s based on the data there, in a sense, answering ‘known’ questions. PowerPivot, on the other hand, enables users to connect to any data and instantly start modeling and analyzing it “on-the-fly” (without IT defining the cubes and modeling it in advance). PowerPivot essentially enables users to answer those ‘unknown’ questions that can often exist.
How often have you had data was missing from the cube? Or a business user come to ask for a missing metric and you postponed its creation for the next data warehouse update which was postponed and never actually happened? This is where we need self-service BI and this is where PowerPivot comes to help both the business user and the IT team. PowerPivot authoring environment is the same beloved Microsoft Office Excel that everybody has and knows how to use. The simplicity and the familiarity of this desktop tool eliminates the need for additional training and increases the adoption rate. Give them a tool they are not afraid to use and they’ll know how to work with the metrics. Business users are able to just go through any data on their flat spreadsheet and produce a cube from it in a pivot table with only a single mouse click. There are certain limitations there, but the value is still huge – self service BI with zero training required and remarkable engine performance providing instant business value.
That’s why we say SSAS answers your “known” questions and PowerPivot solves the “unknown” ones. Panorama NovaView 6.2 supports both systems and supplies our customers with the same interface and same tools for both SSAS and PowerPivot. NovaView’s unified security layer secures both data sources at the same time and with the same security definitions making administrators’ life easier and making PowerPivot ready for a large enterprise deployment. NovaView BI Server resides in the center of the BI solution and implements the business logic, additional data semantics, and security applied on both SSAS and PowerPivot. It also delivers the data insights over both data sources via the entire suite of NovaView front end tools such as Flash Analytics, Dashboard, Smart Report, Spotlight and more.
By adopting the Microsoft roadmap of self-service BI, Panorama offers intuitive and easy to learn tools which allow business users to connect to either SSAS or PowerPivot cube within seconds. Following the initial connection NovaView users can manipulate the data, build extra calculations, exceptions, charts, KPIs and more. Users can save their work and share it with colleagues by making it publically available, sending by email or via a SharePoint portal. Specifically for PowerPivot, Panorama’s data security layer and rich analytical and dashboarding abilities extend PowerPivot cubes and create an enterprise ready, self-service, in-memory driven BI solution.
Learn more about Panorama for PowerPivot >>
