Excel 2013 BI versus SSRS 2012

1) Excel 2013 has PowerPivot 2012 integrated / inbuilt into it. This means access to vertipaq engine, data processing, and dashboard related benefits. Powerpivot 2012 is able to harness the power of Windows Azure Data Market too. More on the same can be read from here.

2) Excel 2013 had PowerView integrated / inbuilt into it. This means all the reporting related benefits, visualizations like charts - graphs - bing maps, KPIs and dashboarding and other dashboard related components like hierarchies etc. PowerView is designed to interpret DAX queries on BISM model, and integration with Excel means that now Excel is even more capable of reporting BISM data models than PowerView itself. More on the same can be read from here.

3) Excel 2013 can connect to Hadoop on Azure using Hive ODBC Driver. This means excel can exploit data from cloud from related data stored in Azure databases as well as big data stores like Hadoop. More on the same can be read from here.

4) Excel 2013 facilitates sourcing media and other rich content from the web using it's newly introduced Content app and Task pane app. This means very rich integration using a relatively least complex tool compared to other self service tools available in the industry. More on this can be read from here.

5) Excel 2013 ships with some great advancements in visualizations. Users are not expected to be very deeply insighted into reports development, and components that can even make suggestions on the kind of reports suited for the kind of data they are available. More on this can be read from here.

6) Most of the applications provide features to export data into excel, which means any kind of data can be easily imported into excel. Also most of the applications provide plug-in for Excel to provide deeper integration with excel to make the data exchange process easier.

7) Excel 2013 now supports decoupled PivotTable and PivotCharts, that means better representation of trend analysis. And even Excel Services supports this feature. More on this can be read from here.

This list is not an exhaustive list of features, and I am sure there are many more features worth listing. Above listed are just those that I could remember quickly. I am not saying SSRS is a weak tool by any means and on top of that its a server line of technology and integrated with SQL Server. It has great visualizations and available in the form of service which is suitable from a scalability and architecture standpoint.

But clients with

• lots of data stored in variety of data stores
• limited funds and even limited time and patience to wait for IT to facilitate their analytical needs
• totally resistant to learn another tool claiming to be their analytics angel
• and seeking to retain the power of determining the landscape of analytics in their own hands

would definitely find Excel 2013 as their BI magnet.

If you have more Excel 2013 BI features worth listing, feel free to comment the same on this post.

Powerpivot and BISM in SQL Server Denali

Business Intelligence Semantic Model (BISM) is the new philosophy in MS BI analytics parlance, that is taking shape in SQL Server Denali. Tabular projects and MOLAP with choice of MDX or DAX can be termed as a brief definition of BISM, in tangible terms. SQL Server Denali CTP3 ships with all the new features supporting and reflecting BISM in SSAS. But that is just one part of the world.

Powerpivot is the flagship product of microsoft for self-service business intelligence. And surprisingly, microsoft is aggressively inducing the flavor of BISM here also. Three major additions to powerpivot are:

1) Diagram View: To me this looks more like a DSV equivalent of SSAS. Though I have not tried hands on, but from what it sounds, this is a very valued addition to the tool. End users would enjoy modeling using a designer, compared to an excel kind of UI for developing models.

2) Hierarchies: The ability to create user-defined hierarchies would mean that user can logically arrange and relate entities, which can translate the user can easily envision and model drill-down and rollups on their data. Hierarchies are so essential part of any data model, and this capability would enable users to logically analyze their data.

3) Perspectives: This is not a new feature, and those who have used SSAS would definitely understand what this means. If powerpivot data models are shared on a collaboration platform like Sharepoint, this feature can be a real value addition and abstract relevant part of the models to relevant users.

There is much more than just the above listed features, that is being offered in Powerpivot for Excel with SQL Server Denali. To learn about the same, check out this link.

Tableau and BonaVista Dimensions for self service data analysis and diversified data visualizations

The term "self-service" has been making a strong buzz since quite some time. Though PPS caters the platform for dashboard creation and sharepoint hosts it very well, but still I feel that Microsoft has a lot of scope to improve in the data visualization area. Microsoft acquired the license from Dundas for charts and graphs, and the same can be seen being available in SSRS and Excel, with the addition of visualization enhancements like Sparklines, Indicators etc. But in my views, still it's at a considerable distance from the data visualization capabilities that few other tool offers. Proclarity had some of the best data visualization capabilities, and phase-by-phase same are being added to PPS. It was one of the best tools for data analysis and the visualizations that it created, especially the decomposition tree was spectacular.

Tableau is one of the leading tools in the area of data analysis for it's extremely rich data visualization capabilities, which makes analysis quite easy and appealing. One might immediately think that Excel has also got rich visualization capabilities as a client tool. Nothing can be rated without a comparison, and I suggest to go through this product tour video of tableau, to get a feel of the data visualization capabilities that it offers. Honestly speaking, I see it as one of the accelerator replacement, if you are not intending to invest into PPS and developer hours to build dashboards and want to equip your business analysts to self-serve data analysis. Also if you want to add the self-service effect with power packed data visualization capabilities, Tableau is one of the top 10 options to consider. Check out this amazing data visualization gallery.

BonaVista Systems, has launched a new product called BonaVista Dimensions. To me this product seems like another version of powerpivot. If you want to learn the rationale, based on what I am making this statement, check out this architecture diagram. In my views, what makes this product different from powerpivot, is the data visualization that it adds to Excel. It can be said that it adds tableau data visualization effect to Excel. I have not done any research into the pricing and licensing details, but what I can say by and large is that if you are looking to powerpack your data analysis using Excel as the client tool with Tableau class data visualizations, this product has the potential to add value to the data visualization and effectively the data analysis department.

One question that might come to one's mind is that why not Powerpivot? My immediate answer to the question would be that powerpivot still misses data visualization capabilities and whatever it offers in this area is on the shoulders of it's hosting tool i.e. Excel. I do not intend to say that these products are better replacements for powerpivot, each product has got it's own space. Till microsoft makes the quality of data visualization enriched to the level of Tableau, it can be seen as one of the best value additions on your MS BI solution, which again depends on factors like budget and time to market / time to delivery. In summary, excel is a good data analysis client tool to consume data from SSAS, but it can still be made a lot better.

Technical Diagram about security in Powerpivot Architecture and ecosystem

I keep on telling this quite frequently, "A picture is worth thousand words" and the picture that I am going to share is about worth a ten thousand words. SQLCAT team has released a new technical diagram (in fact a poster sized diagram) on powerpivot for excel and sharepoint security architecture. It involves not only security edges of powerpivot, but also all the related components too.

You can download this technical diagram in different formats as listed below.

Download PowerPivot Security Architecture Technical diagram (.pdf)
Download PowerPivot Security Architecture Technical diagram (.vsd)
Download PowerPivot Security Architecture Technical diagram (.xps)

Image Courtesy: SQLCAT.com

Difference between QlikView and Powerpivot , is it Mobile Business Intelligence ?

Qlikview is one of the fastest growing product in terms of self-service business intelligence. I felt the need to compare and technically evaluate the differences between QlikView and Powerpivot. There are two very important articles that explains the comparison between these two products. One is authored by Donald Farmer titled "QlikView from a Powerpivot standpoint" and the other is by Darren Kerfoot "Microsoft's new Powerpivot from a QlikView standpoint". Though more or less, both of these products offers similar set of services, one difference that caught my attention was Qlikview's support for mobile business intelligence.

Before you read ahead, below is an interesting comic strip from Dilbert, that I think would fit the theme. Companies who are acting as solution providers to their end clients, often need to provide BI application and services on mobile devices, and the target users can be business leadership as well as power users. And if your product has no support for mobile business intelligence when other competitors are flaunting their demos on IPads, IPhones and Blackberrys to CEOs and CIOs, it can be a big concern to worry.

In the similar way, it might sting a little when you announce that you are not / never going to provide business intelligence delivery on mobile devices. Major providers of Business Intelligence products / solutions have already started supporting delivery of BI reporting and access of BI solutions on mobile devices, for example SAP BusinessObjects Mobile , Microstrategy 9 Mobile , IBM Cognos 8! Go Mobile. Presently in my knowledge, there is almost no significant support for mobile business intelligence in Micorsoft BI Stack. I am sure Microsoft must be having it's eyes on this growing requirements or rather I would say growing market. Accelerators like Roambi can still be used to extend the reach of SQL Server based BI solutions on mobile devices.

Self service Business Intelligence on SaaS Platform Alternatives ( Powerpivot Alternatives )

Self-service Business Intelligence is one of the growing markets these days and business users are looking for flexibility and capability to analyze data and design reports or dashboards at will. Products / Services that provide dashboarding solutions, but are IT Developers toys and makes Business Users dependent on IT Staff to request for almost anything facilitated by these products, are miles away from self-service BI.

Any MS BI professional would be aware of Powerpivot which is a "managed" self-service BI tool for business users in the Microsoft BI Stack. But this is not the only star in the constellation of self-service BI tools in the BI galaxy. Below are the alternatives (in my knowledge and in no specific order) that are worthy to be explored, and you would find that one or more products fit your needs depending on at which stage of BI maturity is your enterprise.

Few of these are cost-effective, few are good for enterprise that are starters in the BI space, few are good for better in-memory performance, some are good for better visualizations, and some are good from an overall partner support and product integration perspective. I am reserving a detailed comparison of few or all of these for a future post. If I am asked the top 3 favorites, as a MS BI professional I would be interested in Powerpivot, Qlikview and PivotLink.

1) Powerpivot

2) Qlikview

3) PivotLink

4) Tibco Spotfire

5) IBM Cognos TM1

6) Advizor

7) Altosoft

8) Vizubi

9) SAP BusinessObjects BI OnDemand

Panorama Novaview with Powerpivot for NON managed self-service BI

Recently, someone made me aware about an interesting article titled "Powerpivot & Analysis Services - The value of both" authored by Panorama Software product manager. This article explains how Analysis Services can be useful for enterprise to model the analytical solution for their known requirements and how Powerpivot becomes helpful to extend the analytical solution by self-service. It also states how Novaview can help business users to build similar to the capabilities and potential of Performancepoint Services i.e. build KPIs, charts, etc.

If I think of a poor man's analytical BI solution, I would think about the cost. Cost can be gauged in terms of expensive licenses of feature rich editions, and IT staff required to facilitate and maintain the BI solution for volatile needs of the business users. PPS is a part of Enterprise Edition of Sharepoint 2010. Also this does not fall in the category of self-service BI, as a business user cannot be expected to develop a dashboard using PPS. A economic recipe of a poor man's analytical solution can be to use Powerpivot as a data source ( by building cubes using powerpivot ) as well as analysis engine too for front-end tools like Novaview. This can eliminate the need for Sharepoint 2010 Enterprise Edition and one can use Sharepoint Foundation Edition too for collaboration of deliverables developed using tools like Novaview. This is the first part of the savings.

Second part of the savings can come from the fact that these tools are claimed to be easy to learn and targeted to be used by business users than the IT services providers. So the dependency on IT Staff to manage and extend the solution becomes less, which effectively translates to savings.

Of course, these tools cannot be a complete replacement for Sharepoint 2010. But if you need a middle path where you want the BI services to build just few Dashboards / KPIs / Charts by business users, at the same time you do not want to invest into Sharepoint 2010 as you might not be sure that you would exploit the full potential of services like PPS, Excel Services, Visio Services and others, Powerpivot with Novaview can be one of the options to try out. Powerpivot is managed self-service BI tool, but by adding a layer with tools like Novaview and using Powerpivot as a data-source, the managed quotient can be reduced to a fair extent. I would not stress on which kind of enterprises would benefit from this recipe of solution, but I am sure that there are enterprises who would want to start slowly and steadily before they hire a full fledged IT Staff or a Solution providing vendor to design a huge enterprise class analytical solution using tools like SSAS and Sharepoint 2010 Enterprise Edition. Those enterprises should give a thought on this recipe.

Powerpivot as data source for Performancepoint Services 2010 - Whitepaper

Performancepoint 2010 supports different kind of data sources that can be categorized into Analytical data sources and Tabular data sources. Analytical (Multidimensional) data sources include any data from SSAS cubes and Powerpivot, Tabular data sources include data from sources like Excel spreadsheets, Sharepoint lists, SQL Server tables, MS Access tables and finally custom data sources. For custom data sources, one needs to use PPS SDK.

Using powerpivot as a data source is not straight forward or I can say that it's different. The reason it's different is that, powerpivot is something that is neither completely multi-dimensional, nor completely tabular. It can be seen as an intermediate between the two types of data sources. So if you are trying out your hands-on for the first time using Powerpivot as a data source with Performancepoint, it can baffle your mind by the way data from powerpivot is classified in hierarchies, measures and dimensions in your PPS Dashboard Designer 2010.

Answer to the above prospective challenge is a whitepaper from Microsoft. This whitepaper explains how to use powerpivot as a data source in performancepoint services 2010 ( in sharepoint server 2010 ). This whitepaper can be downloaded from here.

Powerpivot Architecture Technical Poster / Diagram

It's said that "A picture is worth a thousand words", and it is more easy and interesting to understand any architecture by studying the architecture diagram. SQLCAT Team has released a logical architecture diagram of Powerpivot and components related to it's ecosystem like SSAS 2008 R2, Office 2010, Sharepoint 2010 and other related components and technologies.

This poster can be downloaded in PDF, XPS and VSD format. The original article can be read from here.

SSIS , SSRS , Powerpivot , Sharepoint and .NET with SAP

Today I was going through an article featured on powerpivot-info.com, and I stumbled upon an interesting set of components offered by this company called "Theobald software". Before I go ahead with the entire story, a little background about SAP and why a MS BI geek would be interested in SAP. Every mature and informed professional in BI should be knowing that SAP is the world-class ERP solution for almost every kind of business and Germany can be considered as Mecca of SAP. Now like any Enterprise level products it provides an application level interface and also holds data within itself in a proprietary format. Entire business process workflow for almost any kind of business and any kind of process can be managed using this product, and the biggest USP of this product is that it comes with a proprietary programmable API called BAPI. I can write for an year about this product and still I would fall short, so I would cut this story short here and move ahead to how on earth I know about SAP and what a MS BI guy has to do with it.

I have been working since the past 1 year on a systems integration programme where I am involved in migrating data from a proprietary ERP application to SAP, and the tool we have been using is SSIS for data migration. It has been a very interesting, challenging and one of it's kind of experience as a Technical Lead as this is the first time I am working in such combination of technologies.

Coming back to where we started, this company offers components that can be used as a wrapper between SAP and Microsoft technologies like .NET, SSIS, SSRS, Sharepoint and Powerpivot. I have glided over the features that this product offers and based on my whatsoever little experience of working with SSIS and SAP, below are some points that should be considered before using these or any components while working with SSIS and SAP.

1) Business data in held in proprietary format in SAP, and any change triggers a workflow or a set of processes in SAP. It can be thought of an application equivalent of mainframe systems, and it would take you to be no less than a senior level business analyst or senior SAP functional consultant to change any data into SAP.

2) Based on point 1, it can be deemed that for other applications which may aspire to hook into SAP, like SSIS or SSRS, it can only be a read mode access and not a write mode access.

3) The components that this company provides, claims to have features that facilitate to read data directly from SAP tables. My experience has been that, first you need to have a broad knowledge about what these tables contain, as neither the names would be self-relevant nor it would be that easy to figure out relationships from any table like foreign-keys in a database. For example, in the Materials Management module would have following tables: MARA, MAKT, MBEW, MLTX and others. Even all the column names would be in German. A VC++ programmer would easily co-relate with these kind of nightmares.

4) Another feature that these components offers is of SAP queries, something like our stored procedures. Believe me, you would never require any reporting tool if you are using SAP. Almost any kind of reports can be created with any level of flexibility that can be imagined. To put just one of the fact to the table, just any single module of SAP comes with approximately 800 built-in different kind of reports which can be modified too. Also if the report is provided in Excel format, Excel itself can handle much of the graphical or pivoting features to a considerable extent.

5) If I consider the usage of mySAP portal (which can be thought of similar to Dashboards hosted on Sharepoint created using Performancepoint Dashboard Designer), master data, and BW Cubes which might need to be used and/or analyzed with another system that is hosted on a different platform like Sharepoint, in this case I see a good use of these components to create a BI solution on the top of SAP and any other ERP level product or any data models hosted in a different platform (including Microsoft). I recently read on a whitepaper that Panorama Novaview is able to hook into these SAP ERP Tables, and Microsoft SSIS also has a connector to read from SAP but we do not have any components out-of-box in SSIS / SSRS specially designed to work with SAP.

This company is German in my understanding, has all the German business partners, and claims to have 650 clients out of which half of them are german. But even if I consider the rest, it should be having 300+ clients who are using MS BI integration with SAP, and in my career till date I have worked and even heard of only a single project where SSIS is used with SAP. I really wonder how big is the BI Universe and how smaller is my knowledge !! :) These components are interesting and if you have even any brittle idea of a few terms of SAP, I suggest to check out these components and catalogue them in your list as this cannot be considered less than an MDX library available in the form of Excel Functions ( I should have said DAX in short ).

I plan to publish another post sometime after I recover from the above shock, to share how we use SSIS and SAP R/3 for data migration from a proprietary CRM application that is stored in Oracle Warehouse to SAP for all the modules like Materials Management, Customers, Vendors, Account Receivable, Accounts Payable, Human Resources with Parallel Payroll (the most complex module I have ever worked in my life), Cost Centres, WBS, Fixed Assets, Purchase Orders, GL Balances and many more.

SSAS + Excel Services + SSRS + Performancepoint Services = Powerpivot + Office 2010 + Panorama Novaview ?

The equation mentioned in the subject of this post seems to be something like a combination of keywords that one might throw at Google to find out some results. If you use google with some values with binary arithmetic operators, google might answer the result. But for the above equation, the only engine than can answer the result of the equation are the BI Engines of Microsoft and Panorama.

Panorama has come out with quite a handful of products or product features that works with powerpivot and microsoft office 2010. After going through the features that Novaview offers for leveraging the capabilities of Microsoft Powerpivot, I particularly feel interested with the Universal Data Connector capabilities of NovaView, as it can hook into a variety of data sources ranging from Excel to SAP ERP tables.

My equation might not be precise, but from my initial grazing on the Parorama's datasheets and whitepapers, I have summarized my understanding in a single equation. And as it is said that "Keep your friends close, and your enemies even closer", I like keeping a watch on other competitive (I mean to say Partnering) products. And if you adopt the same philosophy, check out these panorama's integration with microsoft business intelligence whitepapers from here, and one another great source of information that is more precisely banged on target is a post on Ella Maschiach's BI Blog.

Download Video Tutorial or Webcast on Performancepoint Services , Excel Services , REST API , Powerpivot , Sharepoint Business Intelligence

Any IT product that is used by masses cannot work in isolation, and arguably, same holds to true for SQL Server also. Sharepoint as per my opinion, is one of the biggest delivery partners for the Business Intelligence features that come out of the SQL Server MS BI stack of technologies. Sharepoint has more often been seen (in the group of people I know) as a tool for collaboration when seen from the perspective of a database or in fact a MS BI developer. Apart from being used as a collaboration platform, Sharepoint has come down a long way into the arena of Business Intelligence.

The key Business Intelligence features that are now unique to Sharepoint, which even SQL Server needs to complete the eco-system of any Business Intelligence project are Performancepoint Services, Excel Services, Visio Services which facilitate Strategy Maps, web parts that hosts SSRS and Excel Analytical Reports, and Powerpivot. There is a lot to know and learn in Sharepoint 2010 Business Intelligence Features.

How about a book that gives a higher level overview with a demo ? It might seem boring to SQL Server folks. But how about a video tutorial or webcast that takes you on a guided tour of all of these along with a demo, which is also available free of download !!! Yes, I am not kidding. This is a session presented by Mike Fitzmaurice, and this video is available for download from Channel9.

So download this video tutorial / webcast (whatever you call it) and get going on a Sharepoint Server 2010 Business Intelligence Overview. Be advised that this download is over 1 GB and contains over 1 Hr of video training. I recommend it as a must-watch video for MS BI folks.

Excel on browser ( Excel Web App ), Powerpivot and Sharepoint

I was reading Andrew Fryer's Blog on a post regarding Powerpivot management and the first sentence said that "The most important thing about PowerPivot is the ability to share users analysis into SharePoint so that these other users can slice and data form within a browser."

For those who are not aware, Microsoft Office 2010 has come out with a new neat feature that can be thought of as a NANO version of Microsoft Sharepoint, but it brings to the table a very important functionality. This application is called Excel Web App and is a light-weight Excel client that allows to collaborate working on excel workbooks more or less like Sharepoint using a browser. Keep in view, that multiple users can work together on it, but it should not go under assumption that it would have all features that are available while working on worksheets that are hosted on Sharepoint. Obviously, it won't have those features as they are not Excel but Sharepoint features.

The point that I am trying to make is that, if Powerpivot can be used in conjunction with Excel Web App, need of Sharepoint can reduced helping small scale organizations to still have the minimal required features like simultaneous collaboration and analysis capability of Powerpivot. I don't think powerpivot is supported from this application as of this draft, as I read on the Excel Webapp overview post comments that "Excel Web App cannot run add-ins built for the Excel client app". Thru the course of evolution or thru some workaround, it can be effectively used to replace Sharepoint if just collaboration of Excel worksheets is the most used feature on Sharepoint for your organization which would reduce huge costs.

I am NO expert at Excel Web App but having worked with Excel Services on Sharepoint Server and having read the post on Excel Web App overview (do not miss reading the comments on this post) and this post on Collaborative editing using Excel Web app from Excel Team, I am very sure of the point that I am trying to make is possible and a practitioner or Excel team can confirm the same. I am happy to learn if there's anyone out there who have got different views on the same. I have posted question on the same to Excel Team, do check out the Excel Web App overview post for the answer from Excel Team.

Microsoft Codename Dallas and Powerpivot

When powerpivot was introduced, the power of analytics that it brought to Excel and Sharepoint hosted content was spectacular. The point where I didn't feel something in place was, what would powerpivot analyze and when analytics is generally facilitated for MIS systems right out of data warehouse and cubes, would powerpivot really come to that much use for the business analysts keeping the flag of Self-Service BI still waving ?

One of the interesting use of powerpivot came to my attention when I saw use of powerpivot with Microsoft Codename Dallas. Dallas is still in the alpha version, and seems to be an interesting concept. It can be thought of as a potential new tiny Amazon.com in the market place of service subscription business.

Any business that is providing it's content in the form of service can partner with Microsoft and take advantage of the Microsoft's sales channel. Microsoft would provide these services via Dallas, which users can subscribe by paying for the required subscriptions. Content from these services would be made available in a structured format (like in the form of a dataset or in Excel) to the subscribers.

This content can then by readily analyzed using Powerpivot. This is a very interesting part of cloud computing, and powerpivot comes to its most appropriate use for business intelligence, in my views. Powerpivot of course is a very powerful mechanism to analyze huge data in a way that was not possible just using excel, but that felt much more like an add-on to me instead of real time business intelligence. But now when data from Dallas can be analyzed by joining with other sources of data using powerpivot, this is what I feel is real business intelligence.

Pivoting and Business Intelligence

The term "Pivot" just used to be perceived as a small functionality before a couple of years. Over the period of time, it is quite amazing to see how this term has took so much importance in the industry.

Pivoting has made it's journey over the period of time that can evidently be seen in smaller steps. Firstly, it used to be mostly limited to pivot tables that used to exist in Excel. This slowly became one of the key functionality that addicted business users with Microsoft Excel. Office Web Component (OWC) which also contained this functionality, became very popular and started to make it's place in stand-alone and distributed applications.

Sensing the need for the same in database development, Microsoft introduced PIVOT and UNPIVOT operators in T-SQL with SQL Server 2005. This made queries much easier for developers, which used be a lengthy and complex piece of code used to creating resultsets that were typically consumed by some cross-tab reports. SQL Server Analysis Services 2005 (SSAS) cube browsing also got facilitated by using OWC.

Sensing a feature/characteristic of pivoting to aggregate huge information, project Gemini was started which finally resulted into what we know today as PowerPivot. It can aggregate i.e. pivot and analyse huge data from a variety of sources using engine of SSAS and interface of Microsoft Excel and looks very promising with its charting and analysis capabilities. Also it seems like Microsoft plans to go big in this direction, as Microsoft has set up a dedicated lab kind of research setup for pivoting known as Microsoft Livelabs Pivot.

Importance of pivoting is not just recognized by Microsoft, but other industry vendors are also making their move in this direction to get their slice of business. Infragistics has announced release of their Silverlight Data Visualization CTP which consits of two basic controls : OLAP Pivot Grid and Data Chart. OLAP Pivot Grid fetches data from analysis services using ADOMD and visualizations generated by it can be compared to that of Dundas Charts and others.

It seems like Pivoting is turning out to be new big business arena that has not been exploited to the best of its potential.

Powerpivot Books , Training , and Installation

Powerpivot resources are now out and available for public download.

The long awaited learning material on Powerpivot is now available online on BOL, and can be accessed from here.

Download instructions and links to download locations for different flavours of Powerpivot can be accessed from here.

Those who are not able to download and install Powerpivot (as it also requires installation of Office 2010 beta in one or another way), need not to get disappointed. A Virtual Lab of Powerpivot for Excel 2010 Introduction is available from Microsoft. Aspirants can use this lab, and get their hands-on this lab to get the feel of Powerpivot without bothering about any download or installation.

This Virtual Lab can be accessed from here.

Powerpivot Data Analysis Expression ( DAX ) Functions PDF

Data Analysis Expressions ( DAX ) is the new query language or expression language of Powerpivot. It also has a rich set of functions which are almost similar to Excel functions. A dictionary of all the DAX functions are available for download from PowerPivot-info.com. Most of the functions sound very similar to Excel, except the Time-Intelligence functions.

The functions listed in the Time-Intelligence section looks very much aligned towards the structure and usage that we find in the Date / Time dimension. I wonder why all this information is not available on MSDN yet, or at least I am not able to locate down on MSDN.

Powerpivot Client Architecture

With Powerpivot now available to users, more information about architecture and theory revolving it is emerging out slowly on Blogosphere. I recently went thru an article where the author has posted about the client side of powerpivot, which is the powerpivot add-in.

Below is the summary of what I was able to extract out of the article, that I felt of interest:

• PowerPivot processing engine is called VertiPaq
• VertiPaq engine uses AMO and ADOMD.Net for internal processing
• Powerpivot add-in sends requests to this engine using different transport protocols depending upon provider. Transports like HTTP & TCP/IP are supported.
• Powerpivot add-in is developed using C#.Net and other managed libraries of the .NET Framework. .NET Folks can be proud now as they have reserved a seat on this space.
• All the components of the Powerpiovt architecture i.e. Excel, Powerpivot, AMO and ADOMD.Net are implemented and works in-process. This means crashing of any of the component involved in the architecture would crash all the components. Vertipaq crash is an excel crash makes sense to me, but Excel crash is Vertipaq crash is hard for me to digest. As of now, I am not sure if this is a mole or mountain sized limitation, but for sure this is a limitation.
• Powerpivot System Service (PSS) is probably the Sharepoint version of Powerpivot implementation. Pairing of this service with Performancepoint services, would make Sharepoint a big player of Microsoft BI implementation toolset. This definitely has the potential to bring Sharepoint in the league of SSMS and BIDS, or some would probably argue that Sharepoint already is in this league.

Use this link to read the original article.

Project Gemini is now SQL Server PowerPivot for Excel and SharePoint

I posted yesterday regarding the entry of a new tool called powerpivot, but I didn't realize that its Gemini. After a post from Chris Webb's blog, I read it from a post on Microsoft Sharepoint Team Blog that at a Sharepoint Conference, they announced that official name for “Gemini” is SQL Server PowerPivot for Excel and SharePoint.

Below in an excerpt from the post where they declared the same:

Historically, business intelligence has been a specialized toolset used by a small set of users with little ad-hoc interactivity. Our approach is to unlock data and enable collaboration on the analysis to help everyone in the organization get richer insights. Excel Services is one of the popular features of SharePoint 2007 as people like the ease of creating models in Excel and publishing them to server for broad access while maintaining central control and one version of the truth. We are expanding on this SharePoint 2010 with new visualization, navigation and BI features. The top five investment areas:

1. Excel Services – Excel rendering and interactivity in SharePoint gets better with richer pivoting, slicing and visualizations like heatmaps and sparklines. New REST support makes it easier to add server-based calculations and charts to web pages and mash-ups.

2. Performance Point Services – We enhanced scorecards, dashboard, key performance indicator and navigation features such as decomposition trees in SharePoint Server 2010 for the most sophisticated BI portals.

3. SQL Server – The SharePoint and SQL Server teams have worked together so SQL Server capabilities like Analysis Services and Reporting Services are easier to access from within SharePoint and Excel. We are exposing these interfaces and working with other BI vendors so they can plug in their solutions as well.

4. “Gemini” – “Gemini” is the name for a powerful new in memory database technology that lets Excel and Excel Services users navigate massive amounts of information without having to create or edit an OLAP cube. Imagine an Excel spreadsheet rendered (in the client or browser) with 100 million rows and you get the idea. Today at the SharePoint Conference, we announced the official name for “Gemini” is SQL Server PowerPivot for Excel and SharePoint.

5. Visio Services – As with Excel, users love the flexibility of creating rich diagrams in Visio. In 2010, we have added web rendering with interactivity and data binding including mashups from SharePoint with support for rendering Visio diagrams in a browser. We also added SharePoint workflow design support in Visio.

Reference: cwebbbi.spaces.live.com

Data Analysis Tool / Add-In to extract and develop Business Intelligence using Excel and SQL Server : PowerPivot

It looks like Microsoft is all set to deliver a new baby in the parlance of Business Intelligence, and its named PowerPivot. Below is an excerpt from the PowerPivot product site:

Overview:

PowerPivot for Excel 2010 is a data analysis tool that delivers unmatched computational power directly within the application users already know and love—Microsoft Excel. It provides users with the ability to analyze mass quantities of data and IT departments with the capability to monitor and manage how users collaborate by integrating seamlessly with Microsoft SharePoint Server 2010 and Microsoft SQL Server 2008 R2.

BI Offerings from PowerPivot:

Give users the best data analysis tool available: Build on the familiarity of Excel to accelerate user adoption. Expand the existing capabilities with column-based compression and in-memory Bi engine, virtually unlimited data sources, and new Data Analysis Expressions (DAX) in familiar formula syntax.

Facilitate knowledge sharing and collaboration on user-generated BI solutions: Deploy SharePoint 2010 to provide the collaboration foundation with all essential capabilities, including security, workflows, version control, and Excel Services. Install SQL Server 2008 R2 to enable support for ad-hoc BI applications in SharePoint, including automatic data refresh, data processing with the same performance as in Excel, and the PowerPivot Management Dashboard. Your users can then access PowerPivot workbooks in the browser without having to download workbooks and data to every workstation.

Increase BI management efficiency: Use the PowerPivot Management Dashboard to manage performance, availability, and quality of service. Discover mission-critical applications and ensure that proper resources are allocated.

Provide reliable access to trustworthy data: Take advantage of SQL Server Reporting Services data feeds to encapsulate enterprise systems and reuse shared PowerPivot workbooks as data sources in new analyses.

Reference: www.powerpivot.com