I'd like to make below formula more dynamic. Arriving Here from a Search Engine or via Excel Help?I have an Excel pivot table that has the data model as a source. To pull data from a cell in a pivot table, you can use a normal cell reference, such as B5, or you can use the GetPivotData function, which is specially designed to extract data from a pivot table. The GETPIVOTDATA function can actually be quite useful for creating customized reports, and Ill explain that in a future post, but we can also turn this feature off.
Excel Getpivotdata Data Model How To Make ThoseWe’ll see how to make those formula more flexible, so you can quickly change the results. When you type an equal sign, then click on a pivot table value cell, a GetPivotData formula is automatically created. Refer to Value Cells in GetPivotData Formula.Dany’s article was a great intro to the capability of this unsung capability of Excel available since Excel XP. Excel CUBE FunctionsA few weeks ago my friend Dany Hoter wrote a piece here about the use of the CUBE functions in Excel with PowerPivot data. And now, on to Dick’s excellent article…Using Excel Cube Functions with PowerPivotToday I am going to give you a quick and dirty example of what I think is one of the key features of PowerPivot that will give it a much broader initial and on-going impact for experienced power spreadsheet developers.This is where existing spreadsheets can get the value-add of having PowerPivot data available to them in a way that is not only understandable for the traditional spreadsheet junkie (as opposed to the typical BI one) but that also that will add major value while integrating into existing models. The New Visitor page has information on how to get started, including download links. Cube functions work the same with PowerPivot as they do with other OLAP sources like Analysis Services.Feel amazed using GET PIVOT DATA in Excel.While summarizing huge data with pivot tables in Excel, ever wondered if a result can be extracted using a.I highly recommend reading both for examples and ideas.But if you want to use cube functions with just plain tables of regular data, you can do that with Excel 2010! Just download PowerPivot (free addin from MS), copy/paste or link your tables of Excel data into PowerPivot sheet tabs, and you are off and running. This article below by Dick Moffat, as well as the one by Dany Hoter, is an excellent, detailed example of how to use cube functions with *any* OLAP data source, and NOT just PowerPivot.In the purest sense it is a ROLAP Cube – created at run-time from Relational data.This default “Virtual” Cube is named “PowerPivot Data” and is exposed when you click the Connections Button on the Data Tab:This is inherently an OLAP Cube conceptually and so is an acceptable source for Excel’s native CUBE Functions. But one fact that may not be obvious is that the data set that is created by these PowerPivot objects, and by their relationships, is in fact a “Virtual” Cube in itself. A Cube Automatically?I’m sure I don’t have to explain here how one creates a PowerPivot data source consisting of multiple Relational data sources. This is a big thing and I hope to show you another reason why. Once again though, if one wanted to use an OLAP Cube in a Connection it required availability of an Analysis Services Cube of data.But in Excel 2010, thanks to PowerPivot, users can now create their own “Cubes” inside PowerPivot and they automatically present themselves as an available Connection inside the Excel parent file.![]() This is where a Pivot Table attached to one of these Cubes is a natural presentation and analysis mechanism. Then you want to be able to “Drill up” or Drill Down” on any value in any dimension down to the lowest level or up to the highest easily and automatically. So for example you want to see total sales for March 2010 of a particular Product and its SKUS in a particular Country…. Freestyle xbox 360The ExampleThe data being used in this example is available in the “AW_CompanySales.accdb” database supplied as an example for use with PowerPivot. But the Cube functions give the traditional spreadsheet maker (and his/her boss) the flexibility to present this data in the classic free-form spreadsheet style as Income statements with analysis or to group them on the sheet in any way they want. This is the work that PowerPivot is designed for – so it can reconstitute your OLAP “Virtual” Cube at run-time inside your Excel file itself (and with compression to boot).So if you bring in your sales data (like in the example Bike data available with PowerPivot samples) and set it up in a PowerPivot “Connection” you can refer to that info from your spreadsheet layer using not just Pivot Tables, but also using the Cube functions. But this is the 21 st Century with incredibly cheap and sizable RAM memory on every PC and with processors so fast that it’s hard to believe. In the original implementations of OLAP Cubes the hardware available had limited RAM and slow processors and so many OLAP Cubes had to be created over-night and written to disk in the classic De-Normalized format that allowed for relatively quick queries to be made against data that otherwise would simply not be possible in a Normalized format. Winscp host key cacheThere is little or no impact on file size for the two ways of importing the source data however. There will be certain scenarios where this might work to your advantage over bringing in individual tables and joining them inside PowerPivot, but that’s an issue for another day. The result is exactly as PowerPivot does virtually within its “PowerPivot Data” Connection and hopefully will help me explain this functionality better.This is how that PowerPivot window looks (in an unfortunately stiched together image I’m afraid):Do you notice how there are multiple instances of Country and State across records (?) This is in effect a result set from an Access query or SQL View or SQL Statement and is truly de-normalized. This is the fundamental design of a “Snow-Flake” OLAP design (read about it!) and it should be fairly intuitive to anyone who knows the data sources and how they are related conceptually.In my example, however, I have pulled together this same data into a single large De-Normalized PowerPivot table by creating an Access Query. So Product Categories in the “ProductCategory” table have children in the “ProductSubCategory” table, which have children in the “Product” table. ![]() It is now available to the Workbook, not only to any Pivot Tables you might create, but also to any CUBE functions that you might want to use.This does NOT mean you have to have a Pivot Table in your Workbook, BUT you do have to have started the process in order to create the Measure or Measures you want to refer to in your CUBE functions. By default the Pivot Table assigned this as a “SUM” function and it created the Measure automatically.
0 Comments
Leave a Reply. |
AuthorNathaniel ArchivesCategories |