Data Model - Any Benefit Other Than With Pivot Table?

PowerPivot – Data Model - Excel Data Model allows you to store and analyze data without having to look at it all the time. Think of Data Model as a black box where you can store data and Excel can quickly provide answers to you.

Because Data Model is held in your computer memory rather than spreadsheet cells, it doesn’t have one million row limitation. You can store any volume of data in the model. The speed and performance of this just depends on your computer processor and memory.

.

With data in the data model, Excel does not try to load all of the data at once, it loads segments of the data.

.

In the Data Model, data is stored in columns, not rows

Here are several articles that describe the Data model. The series of articles at the end on Optimization get into the nitty-gritty and highlight the key benefit of the Data Model, the ability to handle an "unlimited" number of rows.

.

!Power Pivot - Overview and Learninghttps://support.office.com/en-us/article/power-pivot-overview-and-learning-f9001958-7901-4caa-ad80-028a6d2432ed
Excel for Office 365, 2019, 2016, 2013
Power Pivot is a data modeling technology that lets you create data models, establish relationships, and create calculations. With Power Pivot you can work with large data sets, build extensive relationships, and create complex (or simple) calculations, all in a high-performance environment, and all within the familiar experience of Excel.
Power Pivot is available as an add-in for Excel, which you can enable by following a few simple steps. The underlying modeling technology found in Power Pivot is also found in the Power BI Designer, which is part of the Power BI service offered from Microsoft.
.

@ 1 Table, 50 Rows, 7 Columns: Analyze with PowerPivot? You Bet!              2012 12 18
I was having a conversation with a great guy who happens to be a business consultant and a hard-core convert to PowerPivot.  A True Believer.
At one point he mentioned that only about half of his clients “have enough data to benefit from PowerPivot.”
That’s a myth, but I can’t blame him (or anyone else) for unconsciously believing it.  People like me are responsible for the myth in the first place, because we love showing how PowerPivot takes complex data sets and chews them up.  We like drama.
But guess what folks?  About half of my PowerPivot usage is analysis on 50 rows or less.  Let me show you why, using this made-up data set.
.

.
Extend Data Model relationships using Excel, Power Pivot, and DAXhttps://support.office.com/en-us/article/tutorial-extend-data-model-relationships-using-excel-power-pivot-and-dax-cf7197d3-1938-490e-93fb-20371e8dd67a
In this tutorial, you use Power Pivot to extend the Data Model, create hierarchies, and build calculated fields from existing data to create new relationships between tables.
.  *  Add a relationship using Diagram View in Power Pivot
.  *  Extend the Data Model using calculated columns
.  *  Create a hierarchy
.  *  Use hierarchies in PivotTables
.
PowerPivot Help TopicsThis link is the home page for links to A LOT of articles and tutorials. Well worth digging throughhttps://support.office.com/en-us/article/power-pivot-help-241aac41-92e3-4e46-ae58-2f2cd7dbcf4f
Power Pivot provides advanced data modeling features in Microsoft Excel. Use the resources below to learn about how you can use Power Pivot to help you gain new insights into your data.
.
Get started
.  *    Learn more about data analysis tools in Excel
.  *    Power Pivot: Powerful data analysis and data modeling in Excel
.  *    Start Power Pivot in Microsoft Excel
.  *    Power Pivot keyboard shortcuts and accessibility
.
.
Understand the Power Pivot Data Model
.  *    Create a Data Model in Excel
.  *    Create a memory-efficient Data Model using Excel and Power Pivot
.  *    When to use Calculated Columns and Calculated Fields
.  *    Lookups in Power Pivot Formulas
.  *    VLOOKUP Functionality in Power Pivot
.  *    Version compatibility between Power Pivot Data Models in Excel 2010 and Excel 2013 and Excel 2016
.  *    Upgrade Power Pivot Data Models to Excel 2013 or Excel 2016
.  *    Data Model specification and limits
.  *    Data types in Data Models
.  *    Freeze columns
.

@ Doing Power BI the Right Way: Pt 0: Intro
https://sqlserverbi.blog/2020/07/12/doing-power-bi-the-right-way/
This is an introduction to a series of posts and pages that will provide a comprehensive set of best practices for successful Power BI solutions. My goal is to provide a set of guidelines and practices that provide the best chance of success as you navigate many decisions about how to stage and transform source data, how and where to perform data shaping and calculations, how to model data and the best way to visualize the results. The biggest question of all may be how to make the right decisions so that the small project you design today will work when you add more data, more users and transition into a formal, managed solution.
.
Pt 1. Futureproofing Power BI solutions 2020 07 29    Paul Turley
https://sqlserverbi.blog/2020/07/29/doing-power-bi-the-right-way-1-futureproofing-power-bi-solutions/
When starting a Power BI project, you have many choices to make. Decisions like how to source your data, where and how to create queries to cleanse, transform and reshape the data; where and how to create calculations and the nuances of modeling are just the tip of the iceberg. Most Power BI solutions progress through a few stages in their lifespan
.
Pt 2: Preparing, shaping & transforming source data       2020 08 16
https://sqlserverbi.blog/2020/08/16/doing-power-bi-the-right-way-2-preparing-source-data/
In a business intelligence solution, data must be shaped and transformed. Your data is rarely, if ever, going to be in the right format for analytic reporting. It may need to be consolidated into related fact and dimension tables, summarized, grouped or just cleaned-up before tables can be imported into a data model for reporting. If source data is in a relational database, chances are that it is optimized for transactional processing or application development. The column names, data types and formats are likely not suitable nor user-friendly for simple reporting. If source data exists in files or spreadsheets extracted from business reports, without data preparation you are likely just to reproduce the existing reports without adding business value.
.
Pt 4: Power Query design best practices       2021 02 14              Paul Turley
https://sqlserverbi.blog/2021/02/14/doing-power-bi-the-right-way-4-power-query-design-best-practices/
I’ve created my share of informal Power BI reports that were put together quickly, with the goal to create something “good enough” rather then achieving perfection. This guide is about designing proper and formal solutions but these practices apply to any Power BI project that needs to survive future maintenance.

Pt 6a: Data modeling essentials and best practices in Power BI and AS tabular       2020 12 25
https://sqlserverbi.blog/2020/12/25/doing-power-bi-the-right-way-6-data-modeling-essentials-best-practices-1-of-2/
One of the most important lessons I have learned about data modeling over the past 20+ years is that there isn’t one model that fits all business needs. However, there are simple patterns we can follow to model data according to different business needs. Each pattern or schema has a different set of rules. At a very high level, we can think of these schemas in three different categories.
.  *  Flat Model/Table
.  *  Master/Detail
.  *  Dimensional
.

! What is Excel PowerPivot- The Powerpivot Revolution (the new perception of the Excel user) ****    2013 03 25
https://www.poweredsolutions.co/2013/03/25/the-powerpivot-revolution/
Powerpivot has been called “the best feature to hit Excel in 20 years” by many MVP’s, developers, BI experts and excel users like myself. You’ll probably be asking yourself why is this the best feature of Excel thus far?
Powerpivot is part of a bigger picture. A bigger and richer plan that comes with the following term:
.   The Tabular Model
The tabular model or SSAS Tabular it’s basically an in-memory database in SQL Server Analysis Services. There’s nothing beyond this type of scalability, this is the best there is in the MS BI Stack and it’s what you’d encounter in a company that has a MS BI Solution deployed at the top of the game.
How is this related to Powerpivot and its users?
This is where the fun part comes- people always think about the Excel user as people that know how to create scenarios, create clean and visual reports and that can create simulations in excel but people have never seen the true definition of an excel user until now (thanks to Powerpivot)
Powerpivot IS the tabular model. The data model that you create inside Powerpivot it’s the same that could be used inside SSDT. (Should we call the Powerpivot users developers now? I say yes!)
In a short summary, you can say that now Excel users have the complete tools to create their own applications and even help the BI and Database experts to improve their systems because they are more aware now of how things are being calculated. The role of the excel user is changing to one that no longer has limitations that we used to know with traditional Excel and with so many possibilities now with VBA, Excel 2013, Sharepoint, Data Explorer and others, it’s up to our imagination to decide where we’ll be heading.
The possibilities are endless! The revolution has just started and I welcome you to become part of it.
.

! What is Excel PowerPivot? 2013 04 12
https://www.youtube.com/watch?v=mPK0WGM2GaE 4min20
My friends at Portalfronthosting created this AMAZING video with subtitles in english and spanish! Sí, en español.
What is PowerPivot, how it works and what makes it so revolutionary? Visit PortalFront PowerPivot Site for more information http://www.portalfronthosting.com/exc....

!  What is Power BI, Power Query and PowerPivothttps://chandoo.org/wp/what-is-power-bi-power-query-and-power-pivot/
Power BI is a software to create and publish interactive, web-enabled reports & visualizations for your audience. You can use Power BI on a PC or web to create things. Once you publish a report (or few visuals), your audience can consume them by using any device – PCs, Macs, Web browsers, Apps on cell phone / tablets etc..! Power Pivot: Powerful data analysis and data modeling in Excel
https://support.office.com/en-us/article/Power-Pivot-Powerful-data-analysis-and-data-modeling-in-Excel-A9C2C6E2-CC49-4976-A7D7-40896795D045
Power Pivot is an Excel add-in you can use to perform powerful data analysis and create sophisticated data models. With Power Pivot, you can mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.
In both Excel and in Power Pivot, you can create a Data Model, a collection of tables with relationships. The data model you see in a workbook in Excel is the same data model you see in the Power Pivot window. Any data you import into Excel is available in Power Pivot, and vice versa.
.  *  Top features of Power Pivot for Excel
.  *  Tasks in Power Pivot or in Excel
.  *  How the data is stored
.  *  Saving to SharePoint
.

! PowerPivot Beats IBM/Cognos: Textbook Example of ROI (and Going Beyond “Informal BI”)              2012 06 21
We recently completed our second joint case study with Microsoft, describing the work we did with one of our Cloud PowerPivot clients, Building Materials Distributors.
This was a fun project to watch.  And that’s mostly what I did, was watch.  Because the good folks at BMD did the work – all we did was give them the environment in which they could do it.
A Quick Read, and Good Justification!
It’s basically a one-page case study and a very quick read.  I don’t want to just duplicate it here, so I highly encourage you to go read it real quick, then come back here for the points I want to emphasize.
Click here to view the case study
I think this one will come in handy for PowerPivot Pros who are advocating broader adoption at their companies.  You may know in your bones that PowerPivot will revolutionize the way your organization operates, but convincing others (especially non-Excel pros) can be tricky.  And let’s face it, blog posts on a site that uses the word “sucks” and features pictures of Samuel Jackson aren’t the most convincing evidence are they?
But big money savings, quick deployments times, and successful examples of PowerPivot “defeating” much more complex and expensive solutions, all packaged up in an official case study – that counts.
.

! What is Power Pivot and Why You Should Carehttps://exceleratorbi.com.au/what-is-power-pivot/
The answer to the question “What is Power Pivot?“.Power Pivot is a tool that allows you to prepare your data in a way that Pivot Tables can use.  Power Pivot is actually a data modelling tool.  Power Pivot allows you to do the following:
.  1.  Take data from one or more sources and combine it together into a single data source (this is called the data model).
.  2.  Enhance the underlying data so that you can create new derived concepts out of the data.  So if you have Sales $ and Cost $ in your source data, you can create Margin $ and Margin % and make these new concepts available to a Pivot Table.
.  3.  The new concepts you build in Power Pivot are infinitely reusable in your Pivot Tables.  In traditional Excel you need to create formulae in individual cells by pointing to other source cells.  If you want to reuse a formula you have created on another Sheet that is not 100% identical to the first sheet, then you effectively have to rewrite the formula.  This is not the case with Power Pivot.  You write the formula once in the data model and then it can be reused over and over in as many Pivot Tables as you like.
.  4.  Power Pivot compresses the source data and stores it as a database within the Excel workbook.  This  means that you can store 10s (even 100s) of millions of rows of data directly in an Excel workbook in a highly compressed and performant way, and distribute it to other people without them needed to have access to the source database.  Excel can “natively” only store 1 million rows of data. Excel it is not optimised for large database crunching work, Well before you hit the 1 million row limit, Excel will start performing very poorly, particularly if you have lots of VLOOKUPs etc.
.
So Power Pivot is not “Pivot Tables on Steroids” because Power Pivot does not change the functionality of Pivot Tables.  What Power Pivot does is change the source data so that you can use data that could not previously be used, and you can get more out of data that you previously used inside of a Pivot Table.  So Power Pivot is actually your source data on steroids.
.

A "data dictionary" is a way to document your data "fields"/columns. It is a concept used in big data bases to keep your data consistent ie use the same name and data type definition for specific data everywhere. You can generate one in PowerPivot/Data Model.

@ Data dictionary- Automatically create data dictionary for your PowerPivot model                    2014 03 11    Avichal Singh
There are many reasons why you would want to document your Excel PowerPivot or SSAS Tabular model in an effective manner:- to better understand your model yourself as it moves from simplistic to advanced, facilitate the use of your model by other users, ability to transition model development to another person and a few more.
Diagram View is good, but you can do better <Smile>. You could automatically build a data dictionary – listing all the Dimensions, Attributes, Measures and their Description – and maintain it with almost no effort at all. Watch the video and read the rest of the article to find out how.
.

Optimize

! Compress 2016 02 09- Sort Your Data on Load for Improved Compression
The topic for today – the compression benefits of sorting your data on load.
.
2016 01 05- Restructure Your Data Table for Improved Compression                   Matt Allington
https://powerpivotpro.com/2016/01/restructure-data-table-improved-compression/
One of the main things to know about compression is that a high level of cardinality is your main enemy (ie a large number of unique values will mean poor compression).  I started to think about how I could reduce the cardinality of one or more columns in a large data table I use for a client.  This blog covers the process I went through to test a couple of concepts – warts and all – including a simple error I made during the early testing for this blog post.  I think you can sometimes learn more when you make and then find a mistake, hence I have kept the error I made in this post for others to see along with the fix.  What I think is interesting about this is the process of discovery to find the problem.
The data table I used for my test is a typical sales table, and it includes line level extended cost, line level extended sell, quantity of cases sold among other things.  With this data structure in mind, there were 2 opportunities that immediately came to me.
.
2015 12 08- Compression with Power Pivot https://powerpivotpro.com/2015/12/compression-with-power-pivot/
I have read a lot about how good Power Pivot compression is over the last couple of years, and I have a pretty good understanding of how it all works.  But there were some doubts in my mind after some recent reading, so I thought I would simply do some tests to firm up my understanding – I am sharing that testing and learning in this blog today.
.
2014 09 22- How does Power Pivot store and compress data?           Scott Senkeresty
http://tinylizard.com/how-does-power-pivot-store-and-compress-data/
I was recently spending time trying to better understand the impact of various data formats, sort orders, columns that were correlated with another, columns that were ordered vs random, etc… and relying heavily on Kasper’s script.
.
2014 09 26- What is Eating Up My Memory in Power Pivot? Scott Senkeresty
http://tinylizard.com/script-update-what-is-eating-up-my-memory-in-power-pivot/
Let’s look at what the script does, and does not, include… and figure out how much we care.
.
2013 10 03- PowerPivot – the ULTIMATE anti-bloat feature (propaganda, no technique shown)          https://chandoo.org/wp/PowerPivot-the-ultimate-anti-bloat-feature/In Rob Collie’s excellent book *DAX Formulas for PowerPivot – the Excel Pro’s Guide to Mastering DAX*, Rob makes the point that before PowerPivot came along, Excel pros spent lots of their time ‘flattening’ data in order to feed their pivots. In fact, many Excel Pro’s had become Excel Pro’s largely on the back of those data flattening skills. Your picky PivotTable will only eat flat, boring old (Excel) Tables. In fact, it will only eat ONE table, and that table better have good labeling of all the ingredients (i.e. column headers) or your precious PivotTable will not even open it’s mouth.  The BOSS wants you to mix a little bit of this table with a tiny bit of that table, then add a sprinkling of some other table over the top as garnish. So what did you do? You used as many VLOOKUPS as you have rows in your final flat data-set to join just one column of one of those additional tables onto the first table, didn’t you. And then you repeated this VLOOKUP frenzy for each and every other column that you ended up bringing into your steam-rolled mega-flat pivot-ready data-set. All of which resulted in one very bloated filesize, compared to the original footprint of the underlying tables.
PowerPivot cuts through potential bloat, because it is a lot less fussy than Old-School-Pivots about what it eats.
.
.
2013 07 31- Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/dn393915(v=msdn.10)?redirectedfrom=MSDN
http://download.microsoft.com/download/d/2/0/d20e1c5f-72ea-4505-9f26-fef9550efd44/performance%20tuning%20of%20tabular%20models%20in%20sql%20server%202012%20analysis%20services.docx (downloaded)
e pq 2013 07 31- performance tuning of tabular models in sql server 2012 analysis services.docx
Tabular models hosted in SQL Server 2012 Analysis Service provide a comparatively lightweight, easy to build and deploy solution for business intelligence. However, as you increase the data load, add more users, or run complex queries, you need to have a strategy for maintaining and tuning performance. This paper describes strategies and specific techniques for getting the best performance from your tabular models, including processing and partitioning strategies, DAX query tuning, and server tuning for specific workloads.
To review the document, please download the Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services Word document.
.
2013 04 28- Workbook Size Optimizer
https://www.microsoft.com/en-us/download/details.aspx?id=38793
The workbook size optimizer inspects the composition of the data model within your PowerPivot or PowerView enabled workbook, sees if the data in it can take less space and if possible, enables better compression.
.
2012 06 12- Optimizing Your BI Semantic Model for Performance and Scale Ashvini Sharma, Allan Folting
https://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI414 (83min)
e- Optimizing Your BI Semantic Model for Performance and Scale.mp4 (downloaded)
e- Optimizing Your BI Semantic Model for Performance and Scale DBI414.pptx (downloaded)
This session goes into the internal architecture of the engine and describes implications on performance and scale of the tabular models and how they can be tuned effectively. #TEDBI414
.
Do You “Savvy” Big Data?                   2012 06 28
https://powerpivotpro.com/2012/06/do-you-do-big-data/ We recently received some advice, from some very smart and well-connected people, that we should be using the term “Big Data” in our marketing. 
Now, some of the PowerPivot models we build at **Pivotstream** have hundreds of millions of rows of data in them.  By any reasonable metric, that is big.  And when you consider that PowerPivot is a free extension to Excel, the world’s most popular analytics and reporting tool, PowerPivot may already be the world’s most ubiquitous Big Data tool.
So I think we qualify to use the term.  But most of our Cloud PowerPivot customers, at least so far, aren’t using nearly that volume of data.  Tens of thousands of rows is much more the norm, which is something Excel has always “done.”
So while a marketing mind sees “hot term” and immediately begins throwing it around like candy, a more analytical type of guy like me wants to make his peace with it first.
Some of my conclusions lead me to believe that YOU in fact may already be doing a flavor of big data, and could fairly lay claim to it.
.
2010 02 08- Surprising Example of PowerPivot Compression
https://PowerPivotpro.com/2010/02/surprising-example-of-PowerPivot-compression/
today I was playing with a data set on my desktop machine that was really getting me down.  The data was provided as a (regular, non-PowerPivot) Excel file with 500K rows in it.  And my machine labored to do anything at all with that data – opening it took forever.  Deleting a single row took forever.  And so on. But this was the same machine I had used to do a PowerPivot demo with a 100M row data set, which ran with no problem!  0.5% of the data was bogging me down in Excel! So I decided to do a “before and after” comparison.  I expected that PowerPivot would do a little bit better than Excel on disk, and a lot better in RAM. Didn’t quite turn out that way
.