Posts Tagged powerpivot
When uploading a PowerPivot model into an existing SharePoint 2010 Powerpivot Gallery, I received the infamous “an error occured while capturing snapshots” error, where the hourglass changes into a disturbing red cross.
Quite odd seeing as I had used this exact library in the past and successfully created snapshots.
Now, there were errors that were quite prevalent in the CTP builds of SharePoint and related to security and underlying services. So, I used this thinking according to Dave Wickert’s blog, http://bit.ly/eQGCWD. And my server passed all of the checkpoints, i.e. everything was setup correctly. I did check the ULS logs, and only found the generic Timeout Expired error.
So, in a move of desperation, I uninstalled Internet Explorer 9 Beta.
And the snapshots started working again!
Another reason not to experiment with IE9 just yet… g
=CALCULATE(MAX(Table1[Date]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[Date])>Table1[Date])))
Note: I’m still coming to grips with the EARLIER function, seems to work semantically the same as an INNER join… will hopefully post a better explanation when I understand some more…
2. Using similar thinking, I could then join back to [Table1] and return the Value at the point in time established in the previous step.
(I would love any feedback if it’s possible to merge this with the previous step, may be more efficient)
=CALCULATE(SUM(Table1[Value]), (FILTER(Table1, EARLIER(Table1[Code])=Table1[Code] && EARLIER(Table1[DatePrevious])=Table1[Date])))
3. Finally, Profit is then just the difference between the current value and the previous value for the CLOSE rows.
=if(Table1[Position]="CLOSE", Table1[Value]-Table1[ValuePrevious], BLANK())
Results in the table below.
The Microsoft BI front end tools provide a fantastic mechanism to create reports, dashboards and free form analytics. So, how do we use these tools when the underlying data is provided by SAP, either R3 or BW?
Well, I’m in a session at the Microsoft BI Conference this year with Scott Cameron, and he’s discussing some scenarios on how to get data into PowerPivot. These techniques are quite generic, and can allow data to be exposed and consumed by a number of front ends, namely…
- Excel 2007 / 2010
- Reporting Services 2008 / 2008 R2
- Data Dumps
Third Party Providers
- Theobald Xtract PPV
- ERP-Link iNet.BI
- Simplement Data Liberator
Interestingly, only 2 of the techniques involve Microsoft-provided drivers and techniques. The others are either custom data exports and imports, or third-party providers. Let’s discuss them a little further.
Scenario 1: SAP BW à Excel 2010
Excel 2007 / 2010 natively supports browsing and reporting on SAP BW infocubes. No need for additional add-ins, or 3rd party software. Only requirement is the install and configuration of the SAP GUI front end.
This SAP GUI front end provides two things:
SAP BW OLE DB Provider
- limitation here is that only supports 32 bit Excel.
- SAP Login (Description and Client Number)
Once the driver has been installed, and we’ve used SAP GUI to create a SAP Login; we can then use the Get External Data wizard in Excel to connect to SAP BW, in a very similar manner to connecting to Analysis Services cubes. Some differences though:
- When connecting to external data, select Other Data Sources
Select the SAP BW OLE DB Provider
- Again, if it’s not there, it’s probably a 64 bit machine ;(
- Data Source = Description of the SAP Login created in SAP GUI
- User Name and Password are the SAP credentials
- Under the All tab, add an Extended Property for the Client Number, SCF_CLIENT = <Client Number>
And you’re done!
Check http://bit.ly/cLrN7t for further details.
I like this approach personally because I am quite a fan of Excel to analyse data. It also allows re-use of the existing BW cubes, and no data needs to be physically moved between systems.
Scenario II: SAP BW à Reporting Services
SQL Server 2008 and SQL Server 2008 R2 allows reports to be written directly against SAP BW data sources.
These reports use the Microsoft .Net Provider 1.1 for SAP NetWeaver Business Intelligence. This driver is installed as a part of the Client Tools install on the SQL Server install media.
To enable these queries on the BW Server, you need to enable the QueryCube for external access. (This is done through the SAP BEX Query Designer)
The connection string is the XML/A connection URL. This URL allows for the passing of parameters (as the dataset itself doesn’t support parameters) Once connected, the report design experience is again similar to reporting off of Analysis Services.
Again, a neat method to create reports, as tools like Report Builder 3.0 do allow more people to create and consume these reports. And the data is not physically moved into a staging environment.
Check http://bit.ly/cWTfaE for further details.
Scenario III: SAP Open Hub à CSV File
SAP provides an Open Hub license. This allows data to be exported. These data exports are natively written to CSV file, however it is also possible to export directly to SQL Server (which would be preferred!)
Clearly, this is the most manual method to get to the data. The biggest negatives here are
- the security is lost in translation
- the Open Hub license is not a trivial purchase from my understanding
Scenario IV: Third Party Providers
Theobald Xtract PPV
- Very neat software. Basically creates a data feed that PowerPivot can consume to directly import data. (Without having to use the “traditional” reporting services middle tier.
- Check http://bit.ly/9oO59D for further details
- Provides a host of tool that use a .Net Data Provider to extract data from SAP.
- Interesting one is the Data Source View Creator, as it creates an Analysis Services cube. (which I’m a massive fan of)
- Check http://bit.ly/bWZNEx for further details
Simplement Data Liberator
A replicator to move data from SAP to SQL in real-time
- (Most SAP administrators are nervous to do this, as you need to enable replication in SAP)
- Neat solution to create a fully-fledged SQL Server data warehouse
- Check http://bit.ly/b5D8Cn for further details
So, I’m in a session at MS BI Conference 2010 with Dave Wickert, and he’s doing a great job of working us through things to look out for when installing the PowerPivot client.
This is an Add-In that runs in the context of Microsoft Excel 2010. Any version of Excel that supports add-ins will support the PowerPivot add-in (which is almost any version!)
The download is available from http://www.powerpivot.com/download.aspx
Make sure you download and install the version that correlates to the version of Excel (not your Operating System). So, basically 32-bit Excel = 32-bit Add-in (regardless of 64 bit OS) And yes, you want to use the 64-bit version if at all possible. It allows you to create and manage larger datasets, any size that fits in RAM will work.
However, please note that only 4G files can be saved to disk, and only 2G files can be uploaded to Sharepoint. Yes, only J
Preconditions for the Add-in to work include:
VSTO installed and running
- The Complete install of Office manages this by default
- If installing only Excel, be sure to select “Shared Components” as well to include VSTO
The PowerPivot add-in is enabled. (once installed)
- Check under File – Options – Addins
- Ensure that the add-in is not in the Disabled State
If the add-in is still gone, even if both are ok, it is possible to expose the VSTO exceptions on Excel start up
- Create an environment variable, VSTO_SuppressDisplayAlerts, and set to 0
- Check www.powerpivotgeek.com for full details
Debugging options for the client add-in can be set through PowerPivot à Settings. It’s possible to log events off to a log file, which can be opened and analysed in SQL Server Profiler. Also possible to create a snapshot of the current memory usage, by clicking “Snapshot”.
PowerPivot provides a feature in SharePoint Central Admin, where administrators can see the usage history of published PowerPivot workbooks. This PowerPivot Management Dashboard is essentially a custom web part web page that shows a number of views on the current state of the system, including:
Infrastructure – Server Health
- Query Response Times
- Average CPU Usage
- Average Memory Usage
Workbook Activity – Chart and List
- This is the killer time based chart showing animated history of workbook activity
- Data Refresh – Activity and Failures
- Essentially a document library that has been exposed through a web part
Elements that cannot currently be updated:
- Overall Layout. The “dashboard itself is not written in something like Performance Point, so the layout and content can’t be changed.
- The Silverlight Control. Sigh. There are no plans to produce a separate reusable bubble chart control. Bit of a pity, would be great to embed in other dashboards. Maybe next release J
Elements that we can update:
Any of the Excel based workbooks, including the Workbook activity book
- These workbooks are in themselves PowerPivot workbooks, which feeds data from the PowerPivot Shared Services Database. (The Usage schema tables)
- The Reports document library (bottom right section of the dashboard.)
- This is simply a reference to a document library in All Site Content à PowerPivot Management à Instance ID à 1033
For those that haven’t heard all the news yet … this year is going to see some of the most exciting product launches from Microsoft; particularly in the Business Intelligence space.
Now, Business Intelligence is a far bigger
concept than simply a sexy front end tool, or a super quick ETL tool. Rather it is that sum of the parts that creates a magic whole, and hopefully helps business people grow their businesses.
To support this, the Microsoft Business Intelligence picture includes three main products, all of which are getting new versions in 2010; namely
- Office 2010
- Sharepoint 2010
- SQL Server 2008 R2
I’ve had the opportunity of playing with all of these products in pre-Beta and now in Beta releases, and have seen them improve with each new release. The key new features in each of these products that have got me the most interested include:
Excel Slicers for Pivot Tables
- No more horrible unrelated drop down lists in Excel. Slicers really open up your data in an intuitive and ‘touch-ready’ way.
Reporting Services Map Control
- Wonderful method to display geographical information, within the context of Reporting Services. No custom code. No messy data connections. Flexible, and very useful.
Master Data Services
- One of the features that gets very little attention in the SQL space; but has great potential to help businesses manage their critical business data. No more messy Excel spread sheets, or phone calls to IT to update that mapping table.
- Still one of the most powerful visualisations for performing root cause analysis. Available within PerformancePoint Services. (Would love to see it introduced into Excel as well…)
- No Microsoft Business Intelligence blog would be complete without a reference to this wonderful in-memory tool. A plug-in into Excel 2010 as a fully-fledged modelling tool, as well as a Sharepoint feature allowing for online access and sharing. A must have for every financial manager J
These are just a handful of the vast array of features that are coming from Microsoft this year. I would encourage you to download the public betas and start becoming familiar with these products.
Sharepoint 2010 – download
Office 2010 – download
SQL 2008 R2 – download
PowerPivot, the topic that has been getting all the big attention here at PASS, has introduced ‘yet another’ expression language to define calculations. So, introducing DAX, Data Analysis eXpressions.
The idea behind this language is to provide OLAP type query functionality to the Excel end user. So the basic syntax looks and feels exactly like standard Excel functions. A set of Excel functions have been implemented (80 in total), with additional functions from the Vertipaq engine to allow OLAP functionality.
Ed Note: The additional functions in DAX actually come directly from the VertiPaq engine. Meaning that it is not a wrapper for MDX, it is actually the language used to define and execute the calculation within the engine.
DAX can be used in two places
- Calculated Columns
- Similar to creating measures in fact tables, or in the DSV in SSAS. Basically used for line-by-line calculations. These values are calculated and stored at design time, and not recalculated at execution time.
- Similar to Calculations in SSAS. Calculated at run time, in the context of the current pivot table or query. Best place to define ratios or custom aggregations that don’t make sense in a line-by-line method.
IMHO, the single most important aspect of this language is the underlying relationships defined within the PowerPivot model. As most calculations require some form of navigation between tables, whether it be date calculations or aggregations over groups.
The basic groups of functions that are supported include:
- Excel Functions
- Table Functions
- Time Intelligence
The Time Intelligence ones are by far the most fun ones for me. it is possible to define MDX-type calculations including Year on Year growth, Period to Date and Parallel Period navigation. A bit of a different model though, as the “time dimension” is assumed based on date fields. Only limitation is that it assumes calendar time.
e.g. YOY Growth looks like
Fact([Sales]) – Fact([Sales])(DateAdd(Time[Date], -1, YEAR)
Overall impression is very positive. Full descriptions available at http://blogs.msdn.com/powerpivot/archive/2009/10/01/introduction-to-data-analysis-expressions-dax-in-gemini.aspx