How to Set Up a Pivot Table as a Spreadsheet Database

spreadsheet Calculations

Pivot Tables aren’t merely a way to interact with your data. You also can use them as a rich source of data for regular reports and analyses…without using the weak function, GETPIVOTDATA.

Charley Kyd is a Microsoft Excel MVP  by Charley Kyd, MBA Microsoft Excel MVP

You can use a Pivot Table as a database in the same way that you can use Simple Tables or Excel Tables. However, I’ve never seen a description of how to do it. That’s too bad, because Excel 2010 gave Excel users the ability to use one or more Pivot Tables as a massive and powerful spreadsheet database. If you don’t set up your Pivot Table as a database, you typically must use the GETPIVOTDATA function to return data from it. That limits your power, because GETPIVOTDATA is a “screen-scraper” function. That is, not-very-powerful function only can return the numbers and text you see on your screen. On the other hand, if you do set up your Pivot Table as a database, you can use Excel’s more powerful functions with it, functions like SUMIFS, SUMPRODUCT, INDEX, MATCH, and so on. In future posts, I’ll show you how these functions can give you significantly more power to return results from your spreadsheet database.

Read Full Article: How to Set Up a Pivot Table as a Spreadsheet Database

Source: ExcelUser

4 Bad BI Habits to Drop in 2015

This article from the Sisense blog has a valuable little list of Bad BI Habits to Break in 2015. To-dos for the new year are suggested, like “embracing big data” and “getting a grip on all data.”  We tend to agree, because data is going to continue to grow in vastness and scope for all applications.  As we step into 2015, we can’t bury our heads in the sand and pretend that big (and growing) data is going to go away.  Resolved: to stand tall and meet the challenge!

Check out the original post from the Sisense Blog

Is HTAP the latest type of OLAP?

HTAP OLAP hybrid transactional systems

Some vendors have run from the four letters OLAP…and yet, articles continue to be published, and assertions made, that “analytical processing” is entering a new phase. There’s a new acronym in the market, HTAP, which stands for Hybrid Transactional/Analytical Processing. Given the close similarity (two out of four words ain’t bad—and it’s three if we consider the once-upon-a-time category HOLAP), is there a difference? This post from one of our favorite bloggers, Timo Elliott, may help you decide…

What is HTAP?

Read more about PARIS’ take on the HTAP technology on the PARIS Tech Blog

Big Data Collection: What’s in it for Me?

Girl with a bar code on her neck, the protection personal data

Many of us stand to benefit from certain types of big data collection. In the medical field, big data can be applied in numerous ways, for example: predictive modeling for R&D, enhancing clinical trial design, or conducting Comparative Effectiveness Research (CER).

The idea behind big data is that everybody’s data goes into a big pool, which is then analyzed to find patterns and trends.  We may learn that x and y are indicators of condition z later on in life, and we can then institute preventative care… and, that’s certainly using Big Data to benefit people.  [Click here to read more about big data in the Healthcare community]

Or, it is mostly used to give companies a competitive edge, like in the Financial Services industry.  Financial services firms are utilizing big data to transform their processes, their organizations and perhaps, their entire industry. This isn’t quite as beneficial on the individual level as in the healthcare industry scenario, but we still stand to benefit, from lower prices, or higher growth in our long-term accounts, or even just by having expectations based on a big data set.  [Click here for an IBM article on big data in the Financial Services world]

But, on the flip side,  big data could also be used to advertise to us in seemingly manipulative ways.  And what if sensitive data gets into the wrong hands? Many people are concerned about that.

In a research article conducted by BI and data software review firm Software Advice, Consumer Positions on Data Collection and Use, they take a look at how people feel about data collection as a concept, as it relates to Medical, Financial, and Employee-related data.

It turns out many of the people they interviewed do not have a strong opinion, but a few themes were clear. The report showed that people over the age of 45 were more uncomfortable or skeptical about sharing their personal data in general.  The older you are, the more likely you have been burned by sharing personal data. People under the age of 45 tend to be more optimistic.  They are still cautious, but are more likely to share their data if there is a clear benefit to them.

 Comfort With Use of Data (Age-in general)Comfort With Use of Data (Age-if benefit)


The full report can be found here, but as a general rule, if a company is going to collect data, people share more generously if the reason and benefit to sharing that information is transparent from the start.


Big Data Changed the Way We Think About Data Warehousing

Data WarehousingData WarehousingData WarehousingData WarehousingData Warehousing

Expectations from organizations have changed the demand for data. The change requires organizations to gather and manage very large data sets – terabytes and petabytes of data – for processes and near real-time analysis and predictive analytics, and is due, in part, to a society that is increasingly fast-paced. This big data challenge impacts data warehouses and the way we think about them. It was once considered that day-old data was a good unit of measure for analysis. However, Internet traffic trends can vary hour to hour or even minute to minute. For example, online advertising may miss a bulk of customers if they are using Web traffic trends that are a day old when trying to market to customers. If something goes viral during the day, it may only be relevant for a couple of hours or days and may become stale very quickly.

Read Full Article: Big Data Changed the Way We Think About Data Warehousing

Source: Information Management