Category Archives: Power View

BI Announcements at PASS Summit 2014 #sqlpass #powerbi #powerpivot

This morning the PASS Summit 2014 started in Seattle and during the keynote there was several announcements from Microsoft. I’m considering here only the ones about Business Intelligence (you will find other blogs around about SQL Server).

  • In the coming months, Azure SQL Database will get new features such as column-store indexes, which can be very interesting for creating data marts on the cloud
  • Another upcoming feature in SQL Server will be an updateable columns-store index on in-memory tables. Real-time analytics will like this feature.
  • For a store analysis, an interesting demo using Kinect capturing heatmap to display which areas of a shop store have been visited more using Power Map. Just a demo, but it’s an interesting idea and the best big data demo I’ve been so far (something you can implement in the real world using big data technologies without being Twitter or Facebook).
  • New Power BI dashboards: many new visualizations and a new user interface to place data visualizations on a dashboard (similar to the grid you have in DataZen if you know that product)
    • You can connect to your data source from the cloud, without creating a local data model and sending it to the cloud
    • Q&A is integrated in the new user interface – the web site is a domain, it seems not in SharePoint
    • Q&A generates the report in HTML5, no Silverlight signs here
    • The entire editing is done in a web browser – a preview of that was presented at PASS BA Analytics keynote, this seems a more refined version (still not available, however)
    • TreeMap is available as a new visualizations
    • You can upload an Excel file from your disk or from OneDrive – just Excel file, no Power Pivot data model required (it is created on the fly on the cloud?)
    • Combo chart combining line and bar chart visualization available
    • Private preview now, public preview available soon
    • Request access to public preview on
  • Azure ML is publicly available for free in trial mode

The Power BI story seems the real big news. Combining this with the fact that you can query *existing* on-prem databases on Analysis Services without moving them on the cloud opens up interesting scenarios. Many questions now about when it will be available and how it will be deployed. Interesting times ahead.

Announcing the PASS 24 HOP Challenge

Calling all data junkies! How smart are you?  Want to get smarter?

Play along with #pass24hop Challenge on Twitter starting at 5:00 AM PT Tuesday, September 9, 2014 to win a free Microsoft Exam Voucher!  Simply watch 24 Hours of PASS and be the first to answer the question correctly. At the beginning of each 24 live 24 Hours of PASS sessions (approximately 5-8 minutes into each hour) a new question regarding the session will be posted online on the  @SQLServer Twitter account. The first tweet with the correct answer will win a prize.  Your answer must include hashtags #pass24hop and #24hopquiz.

To take part in the #pass24hop Challenge, you must:

  1. Sign in to your Twitter account. If you do not have an account, visit to create one. Twitter accounts are free.
  2. Once logged into your Twitter account, follow the links and instructions to become a follower of @SQLServer.
  3. From your own account, reply your response to the question tweeted by @SQLServer.  
  4. Your tweet must contain both the #pass24hop and #24hopquiz hashtags to be eligible for entry.
  5. Your tweet must include the complete answer to the question, or it will be disqualified.
  6. The first person to correctly tweet a correct reply to the corresponding question will win the prize described below.  

Register now for 24 Hours of PASS and get ready for 24 hours of play!  

Learn more about the 24 Hours of PASS read the official rules below.



These are the official rules that govern how the ’24 Hours of PASS Social Media Answer & Question Challenge (“Sweepstakes”) promotion will operate. This promotion will be simply referred to as the “Sweepstakes” throughout the rest of these rules. In these rules, “we,” “our,” and “us” refer to Microsoft Corporation, the sponsor of the Sweepstakes. “You” refers to an eligible Sweepstakes entrant.


This Sweepstakes starts at 5:00 AM PT Tuesday, September 9, 2014 and ends at 5:00 AM PT Tuesday, September 9, 2014 and ends at 7:00 AM PT Wednesday, September 10, 2014 (“Entry Period”). The Sweepstakes consists of 24 prizes. Each Prize Period will begin immediately following each of the 24 session and run for 60 minutes.  


You are eligible to enter this Sweepstakes if you meet the following requirements at time of entry:

· You are professional or enthusiast with expertise in SQL Server or Business Intelligence and are 18 years of age or older; and

o If you are 18 of age or older, but are considered a minor in your place of residence, you should ask your parent’s or legal guardian’s permission prior to submitting an entry into this Sweepstakes; and

· You are NOT a resident of any of the following countries: Cuba, Iran, North Korea, Sudan, and Syria.

PLEASE NOTE: U.S. export regulations prohibit the export of goods and services to Cuba, Iran, North Korea, Sudan and Syria. Therefore residents of these countries / regions are not eligible to participate

• You are NOT an employee of Microsoft Corporation or an employee of a Microsoft subsidiary; and

• You are NOT involved in any part of the administration and execution of this Sweepstakes; and

• You are NOT an immediate family (parent, sibling, spouse, child) or household member of a Microsoft employee, an employee of a Microsoft subsidiary, or a person involved in any part of the administration and execution of this Sweepstakes.

This Sweepstakes is void wherever prohibited by law.


At the beginning of each 24 live 24 Hours of PASS sessions (approximately 5-8 minutes into each hour) a new question regarding the session will be posted online on the  @SQLServer Twitter account. The first tweet with the correct answer will win a prize.  Your answer must include hashtags #pass24hop and #24hopquiz.  Failure to use this hashtag will automatically disqualify you.

To enter, you must do all of the following:

  1. Sign in to your Twitter account. If you do not have an account, visit to create one. Twitter accounts are free.
  2. Once logged into your Twitter account, follow the links and instructions to become a follower of @SQLServer
  3. From your own account, reply your response to the question tweeted by @SQLServer  
  4. Your tweet must contain both the #pass24hop and #24hopquiz hashtags to be eligible for entry
  5. Your tweet must include the complete answer to the question, or it will be disqualified.
  6. The first person to correctly tweet a correct reply to the corresponding question will win the prize described below.  

Limit one entry per person, per session.  For the purposes of these Official Rules, a “day” begins 5:00 AM PT Tuesday, September 9, 2014 and ends at 7:00 AM PT Wednesday, September 10, 2014 (“Entry Period”). If you reply with more than one answer per session, all replies received from you for that session will be automatically disqualified.  You may submit one answer to each session, but will be eligible to win only one prize within the 24 hour contest period.

We are not responsible for entries that we do not receive for any reason, or for entries that we receive but are not decipherable for any reason, or for entries that do not include your Twitter handle.

We will automatically disqualify:

  • Any incomplete or illegible entry; and
  • Any entries that we receive from you that do not meet the requirements described above.


The first person to correctly respond will receive a Microsoft Exam Voucher.  Approximate Retail Value each $150.  A total of twenty four prizes are available.

Within 48 hours following the Entry Period, we, or a company acting under our authorization, will select one winner per session to win one free Microsoft Certification Exam.  Voucher has a retail value of $ $150.  Prize eligibility is limited to one prize within the contest period.  If you are selected as a winner for a session, you will be ineligible for additional prizes for any other session.  In the event that you are the first to answer correctly on multiple session, the prize will go to the next person with the correct answer. 

If there is a dispute as to who is the potential winner, we reserve the right to make final decisions on who is the winner based on the accuracy of the answer provided, ensuring that the rules of including hashtags is followed, and the times the answers arrives based on what times are listed on

Selected winners will be notified via a Direct Message (DM) on Twitter within 48 business hours of the daily drawing. The winner must reply to our Direct Message (DM) within 48 hours of notification via DM on Twitter. If the notification that we send is returned as undeliverable, or you are otherwise unreachable for any reason, or you do not respond within 48 business hours, we will award the prize to an alternate winner as randomly selected. Only one alternate winner will be selected and notified; after which, if unclaimed, the prize will remain unclaimed.

If you are a potential winner, we may require you to sign an Affidavit of Eligibility, Liability/Publicity Release within 10 days of notification. If you are a potential winner and you are 18 or older, but are considered a minor in your place of legal residence, we may require your parent or legal guardian to sign all required forms on your behalf. If you do not complete the required forms as instructed and/or return the required forms within the time period listed on the winner notification message, we may disqualify you and select an alternate winner.

If you are confirmed as a winner of this Sweepstakes:

  • You may not exchange your prize for cash or any other merchandise or services. However, if for any reason an advertised prize is unavailable, we reserve the right to substitute a prize of equal or greater value; and
  • You may not designate someone else as the winner. If you are unable or unwilling to accept your prize, we will award it to an alternate potential winner; and
  • If you accept a prize, you will be solely responsible for all applicable taxes related to accepting the prize; and
  • If you are otherwise eligible for this Sweepstakes, but are considered a minor in your place of residence, we may award the prize to your parent/legal guardian on your behalf.

There will be 24 opportunities to respond with the correct answer. Your odds of winning this Challenge depend on the number of responses and being the first to answer with the correct answer.

By entering this Challenge you agree:

· To abide by these Official Rules; and

· To release and hold harmless Microsoft, and its respective parents, subsidiaries, affiliates, employees and agents from any and all liability or any injury, loss or damage of any kind arising from or in connection with this Challenge or any prize won; and

· That Microsoft’s decisions will be final and binding on all matters related to this Challenge; and

· That by accepting a prize, Microsoft may use of your proper name and state of residence online and in print, or in any other media, in connection with this Challenge, without payment or compensation to you, except where prohibited by law

This Challenge will be governed by the laws of the State of Washington, and you consent to the exclusive jurisdiction and venue of the courts of the State of Washington for any disputes arising out of this Challenge.

If cheating, a virus, bug, catastrophic event, or any other unforeseen or unexpected event that cannot be reasonably anticipated or controlled, (also referred to as force majeure) affects the fairness and / or integrity of this Challenge, we reserve the right to cancel, change or suspend this Challenge. This right is reserved whether the event is due to human or technical error. If a solution cannot be found to restore the integrity of the Challenge, we reserve the right to select winners from among all eligible entries received before we had to cancel, change or suspend the Challenge. If you attempt to compromise the integrity or the legitimate operation of this Challenge by hacking or by cheating or committing fraud in ANY way, we may seek damages from you to the fullest extent permitted by law. Further, we may ban you from participating in any of our future Challenge, so please play fairly.

To find out who won, send an email to by September 15, 2014 with the subject line: “SQL Server QQ Winners

Microsoft Corporation 
One Microsoft Way 
Redmond, WA 98052

Tips for adapting Date table to Power View forecasting #powerview #powerbi

During the keynote of the PASS Business Analytics Conference, Amir Netz presented the new forecasting capabilities in Power View for Office 365. I immediately tried the new feature (which was immediately available, a welcome surprise in a Microsoft announcement for a new release) and I had several issues trying to use existing data models.

The forecasting has a few requirements that are not compatible with the “best practices” commonly used for a calendar table until this announcement. For example, if you have a Year-Month-Day hierarchy and you want to display a line chart aggregating data at the month level, you use a column containing month and year as a string (e.g. May 2014) sorted by a numeric column (such as 201405). Such a column cannot be used in the x-axis of a line chart for forecasting, because you need a date or numeric column. There are also other requirements and I wrote the article Prepare Data for Power View Forecasting in Power BI on SQLBI, describing how to create columns that can be used with the new forecasting capabilities in Power View for Office 365.

Excel 2013 stand-alone now includes #powerpivot

This time Microsoft listened to the many complaints received since Excel 2013 release and the stand-alone version of Excel now includes Power Pivot and Power View! This is a very important news because there were customer who didn’t upgrade from Excel 2010 to Excel 2013 just because they would have lost the Power Pivot add-in!

This news outdates my previous posts written in February and I am very happy for that!

Microsoft updated this blog post describing the SKUs that includes Power Pivot and Power View, and now the list includes the Excel stand-alone version that anyone can buy (for example on Amazon). If you already have Excel 2013 and do not see Power Pivot active, check you have this upgrade.

As a side note, Power Pivot (formerly known as PowerPivot) has now an official name that is made by two words and is no longer a single word. The reason is that Microsoft announced Power BI, which includes Power Pivot, Power View, Power Query (code named Data Explorer), Power Map (code named GeoFlow) and much more. For a long time, we will see this name written in both ways, I think…

New PowerPivot 2013 book available! #excel #powerpivot

Our new book about PowerPivot 2013 is finally available in printed edition, too!

excel2013powerpivotThe title is Microsoft Excel 2013: Building Data Models with PowerPivot and it is a partial rewriting of the previous book about PowerPivot for Excel 2010. In the previous book we had a target audience that included advanced Excel users and BI developers, because at that time there was no option to get the same engine in Analysis Services. But 30 months are elapsed, a new version of Analysis Services has been released and in this new book we focused mainly on Excel users. For this reason, we wrote a comprehensive book of all the feature of PowerPivot, but most important we tried to pass concepts of data modeling that might be pretty obvious for a DBA and a BI developer, but are completely new to an Excel user that never had the ability to create a data model with more than one table.

This book is focused on Excel 2013, so we included specific feature of this release related to PowerPivot, such as writing DAX queries and linked back tables, and features unique to Excel 2013, such as Power View. However, all of the PowerPivot features (so the 85% of the book) are good also for PowerPivot for Excel 2010 in its latest release (SQL Server 2012 SP1 PowerPivot for Microsoft Excel 2010), so you can safely use this book for both version of Excel.

You can download the first chapter of the book from the book page on SQLBI web site. And if you want to attend a training in a classroom or online, look at the complete list of available trainings on PowerPivot Workshop web site. The next online courses are scheduled on April 22-24, 2013 and June 17-19, 2013 (following online workshops are every other month).

Here are the links to directly order the book on Amazon around the world:

And here is the list of chapters:

  • Chapter 1 Introduction to PowerPivot
  • Chapter 2 Using the unique features of PowerPivot
  • Chapter 3 Introducing DAX
  • Chapter 4 Understanding data models
  • Chapter 5 Publishing to SharePoint
  • Chapter 6 Loading data
  • Chapter 7 Understanding evaluation contexts
  • Chapter 8 Understanding CALCULATE
  • Chapter 9 Using Hierarchies
  • Chapter 10 Using Power View
  • Chapter 11 Shaping the Reports
  • Chapter 12 Performing Date Calculations in DAX
  • Chapter 13 Using Advanced DAX
  • Chapter 14 Using DAX as a Query Language
  • Chapter 15 Automating Operations Using VBA
  • Chapter 16 Comparing Excel and SQL Server Analysis Services

This book should help you starting with PowerPivot at the very beginning, and you will probably use only the first chapters at that point. Over time, you will use following chapters and will learn more advanced techniques. This is not a book you can digest in a couple of days (after all, it is 500 pages long!), it will be your companion for several months, until you will master PowerPivot!

O’Reilly Strata: Busting Big Data Adoption Myths with Halo 4–Part 3

Structured or unstructured – what to store? How about all of it?

I spoke at the O’Reilly Strata Conference last year and am back again this year to share thoughts about big data. It’s been a remarkable year for Microsoft’s data platform team. While we’ve had a long history in the data space, last year was noteworthy for several reasons.

  1. Dave Campbell Keynotes at Strata Santa Clara 2013We announced our commitment to the Hadoop community and we partnered with Hortonworks to provide enterprises with an Apache-Hadoop compatible implementation on Windows.
  2. We released new versions of our business intelligence (BI) tools, providing business users access to powerful analytics through familiar tools including Excel 2013, Power View and PowerPivot. The columnar in-memory analytics engine technology in PowerPivot, available in Excel, is the same technology which is inside SQL Server Analysis Services Tabular model. We’ve made it very easy in SQL Server 2012 to migrate PowerPivot models into Analysis Services so you can take solutions and insights developed in Excel and scale them up to server based deployments which can serve many users.
  3. And tomorrow you can start ordering SQL Server 2012 Parallel Data Warehouse (PDW), the next generation of our parallel processing data warehousing appliance. The latest version of PDW includes a technology we call PolyBase. PolyBase makes it very easy to meld Apache Hadoop with structured relational data using a standard T-SQL query, enabling large businesses to do efficient information production at scale with the existing skills and knowledge they already have in-house.

While that may read like a list of features, here’s why I think they are noteworthy. A recent Microsoft study revealed that 38% of respondents’ current data stores contain unstructured data and 53% rated increased amounts of unstructured data to analyze as extremely important. This trend is only increasing as businesses realize their unstructured data holds the key to new value not accessible in their existing structured data. In my personal experience, over the last two years, many businesses have shifted from thinking of big data as a challenge to perceiving it as an opportunity.

I’m often asked, “Where is the ultimate value in big data and how do I tap into it?” There are two key measures in my mind: 1) time to insight, and, 2) return on accessible data. These measures are, in turn, enabled through a process I call information production.

Information production is the process of converting data or information from one domain into another. Consider the following example. Assume you have an ambulance fleet which is equipped with GPS units which collect telemetry. Information production techniques allow you to convert the raw GPS telemetry – a sequence of records containing {Timestamp, Latitude, Longitude} elements – into an incident response time. The magic of information production is that it takes data which is difficult to deal with in traditional information systems, such as raw GPS telemetry, and transforms it into information, (incident ID and response time), which is both more structured and more business relevant. Once we’ve produced the incident response time, we can logically join it with models which predict patient outcome as a function of response time.

Great information production tools allow you reduce the time to insight. They allow you to get from a hunch to validation very quickly. In fact, there is an emerging class of information production tools which stimulate hypothesis generation by finding correlations in diverse data sets which may hold the key to new value.

Valuable answers require logically joining different data sets – something every database person is familiar with. In traditional databases the “accessible” data is constrained to data which is contained within the database. This data has been normalized, cleaned, and indexed so it can be used to efficiently answer a fixed set of questions over that data domain.

Big data and information production enable a much larger definition of accessible data though. Going back to the ambulance example, where would you get the patient outcome model to determine how many lives you could save by reducing response time? By using accessible demographic and population data, you could determine how many heart attack victims lives could be saved by moving or adding ambulances.

We will know big data has made it big when it makes every day experiences better. In fact, one of the things I spoke about in my Strata talk today is how our Halo 4 team is using our HDInsight and big data tools to create a better gaming experience. By using a preview of the Windows Azure HDInsight Service to do Hadoop-based analysis on their unstructured data, the team gained invaluable insight on usage patterns and as a result had the agility to make changes to improve the overall gaming experience.

Information production is a key part of our vision and product offerings and enables you to achieve fast time to insight and greater return on accessible data. Our BI tools, like PowerPivot and Power View, are geared to making it easier for a certain class of users to reduce their time to insight and then to be able to effectively share those insights with others. Data Explorer, which we released in preview yesterday, makes it easy to find, transform, and join information to both ease information production and increase the range of accessible data. HDInsight Service, our Apache Hadoop based service, available in preview on Windows Azure, is being used by our Halo 4 team and external customers to realize new value from their unstructured data. SQL Server 2012 PDW with its PolyBase technology enables extremely large scale information production for the largest business needs.

For all of us involved in big data, and me personally, it is an incredibly exciting time. The next 5-10 years are going to be breathtaking.

You can find out more about our big data solutions by visiting, or for those interested in reading the Halo 4 case study on their cloud-based big data solution  – it’s now available online here.

Dave Campbell
Technical Fellow, STB

See more from the Busting Big Data Adoption Myth blog series: