image

Director of Product Management, Microsoft BI

(At left: the many faces of Kamal Hathi:  1 – “Seriously, we have to do formal headshots?”, 2 – Explaining, 3 – Explaining, and… 4 – Explaining. Today, I ask him to Explain some things, and he happily obliges.)

Yeah folks, this is a rare treat. Kamal’s title can be taken at face value – he’s right there at the top of the MS BI org, reporting directly to the VP of BI. Like Ron Burgundy, he is Kinda a Big Deal.  (But me saying that will make him uncomfortable. Ha ha! Nothing he can do about it in this medium!).

Even though I used to work with many of the leaders in BI at Microsoft, it’s not often I get this sort of chance. Everyone’s just so busy – them AND me – so it’s nice every now and then when we get an excuse to talk. Most recently, Kamal was the ultimate approver of MS’s decision to sponsor the ad in the back of our 2nd edition book, and, at the suggestion of the one and only Chris Finlan, we leveraged that as an interview opp as well.  Here goes.

The more things change… the more it’s still DAX

ROB:  a lot has changed since Project Gemini – the original codename of the Power Pivot project – kicked off back in what, 2007?

KAMAL:  Yes and no. The product and the team have matured immensely. Power BI is a complete and attractive product that does so much more than Power Pivot. At the same time it is still the same “soul” of the product at the core. Highly customer centric, solving the problems that our customers have been asking for. In some senses Power BI 2.0 is a return to the approach we had with Power Pivot, moving fast, listening to the user base and delivering a very functional product that is super easy to acquire and get started with.

ROB: You kinda “stole” my second question, which was, what ALSO strikes me is how much is still the SAME – the Power Pivot / Tabular engine, and the DAX language, remain at the heart of things.  It’s amazing to me how COMPLETE that engine is.  Any thoughts on why that original vision has proven to be so robust?

KAMAL:  Yes, a lot IS still the same. The goal of empowering users, the deep desire that the team has to stay in touch with our users and certainly a lot of the technology. We were well ahead of the market in many ways with Vertipaq and Power Pivot with a wicked fast and highly scalable in-memory engine, a business user oriented expression language and a self-service approach.

ROB:  Yeah, let’s focus in on that “business user oriented expression language.”  Kamal, DAX is amazing.  AMAZING!  Every day I marvel at its completeness, and that people like ME (and other Excel people) can learn the same language that is already becoming the language of choice for the long-time MS BI celebrities like Chris Webb, Marco Russo, and Alberto Ferrari.  As a hard-core software critic, I’m telling you that DAX belongs in the Software Hall of Fame.  I often say that DAX’s robustness owes to it being one of the rare cases where a long-running product team got to “re-imagine” itself and sand off rough edges, without sacrificing the power of the original. 

So, how?  How did the team navigate that?  Yes, I was around for part of that effort but wasn’t aware, at the time, that something truly special was happening.  I honestly think there is something profound here that all software teams could learn, but I haven’t distilled it out yet.

going with “user first” instead of “tech first” is in my opinion the magic sauce here.

KAMAL:  DAX is an outcome of our learnings from MDX. (***Note from Rob:  MDX was Microsoft’s “apex predator” formula language for BI before DAX. I was not smart enough to be able to learn it – despite many good teachers trying).

We used to talk about the “steep second step”, of working with MDX. Getting started is easy but to do anything really meaningful requires a lot of work. We also wanted to really speak to the Excel users and so looked closely at Excel’s existing languages and what users did with these. Ultimately it boiled down to being “true to the medium”, Power Pivot is all about the Excel users and so too is DAX. So much so that we deliberately use the same function signatures where possible and extend existing semantics whenever possible.

Rob, you of all people know how we closely worked with the Excel team and had folks like you with Excel in their DNA come over and work on Power Pivot.  As trite as it sounds, DAX was developed with a specific user in mind and going with the “user first” instead of tech first is in my opinion the magic sauce here.

Ah yes, let’s talk about Excel…

ROB:  It seems to me that you folks have kinda come full circle on Excel – “all in” on the Excel versions for the first few years, obviously.  Then I perceived a bit of a “backing away” from Excel, a bit, but only briefly, in the earliest phases of PBI Desktop.  And now, with Excel Services in PowerBI.com, and PBI Desktop able to import Excel Power Pivot models, and Excel 2016 Power Pivot receiving so many improvements, I think MS is striking a nice balance.  What’s your reaction to that narrative?

KAMAL:  “Nice balance” is exactly what we are after. Excel is an amazing product, it is rich, yet approachable, with a consistent and easily understood interface. We want Excel to be really good at being a spreadsheet and remain true to its roots and core experience. Power BI is a great complement to Excel and a focused BI tool that inter-operates really well with Excel. We still have a few wrinkles to iron out here, but that is what we are shooting for.

ROB:  Yes indeed, Excel is a spreadsheet!  But all of the Excel people reading THIS interview, however, view it as the best BI tool in the world, rather than through the traditional “spreadsheet” lens. 

People who are graduating from pivots and VLOOKUP are *not* spreadsheet users in the traditional “it’s a better calculator” sense.  So for us, the phrase “let Excel be a spreadsheet” makes us a bit uneasy. Let me say it differently:  I have fallen in LOVE with Power BI Desktop.  Love it. And the phrase you used, “complement to Excel,” is EXACTLY how I see it – as a complement to Excel Power Pivot, an extension and magnifier.  So, serious question:  deep down inside, have you come around to truly viewing PBI that way, or are you still adjusting to that phrase?

it makes a lot of sense to provide a companion application from Microsoft and design it to be a real companion and not a competitor the way other products  try to be.

KAMAL:  Well, we actually went back and forth on the relationship between the Desktop and Excel. Should we have another add-in (something we obviously have a lot of experience with) or a standalone app ? Ultimately it became clear that many users were using other products alongside Excel. Pretty much every Tableau or Qlik user also is an Excel user. So it makes a lot of sense to provide that companion application from Microsoft and design it to be a real companion and not a competitor the way other products  try to be. Which is what we have done. 

With all this said and as we continue to deliver capabilities that make the interoperability with Excel tighter and more natural, I am getting more comfortable with the role that the Desktop plays wrt Excel and I think users will as well.

ROB:  Our book is definitely “aimed” at the Excel crowd (even though 2nd edition now incorporates the Desktop), so we are thrilled to have the Power BI team advertising with us.  I guess you see Power Pivot as a bridge to Power BI, just like we do?  Cuz otherwise, ya know, waste of money to advertise in our book, heh heh.

Power Pivot (and Excel as a frontend, too) is a great authoring environment for Power BI.

KAMAL:  Yes, Power Pivot (and Excel as a frontend, too) is a great authoring environment for Power BI. All the way from the simplest lists to the most complex data models. What you create in Excel is easily distributed via and consumed in Power BI. Power BI can amplify the insights from Excel.

ROB:  OK, “rude” question:  it’s still amazing to me how few “Excel people” have even heard of Power Pivot or Power BI.  I estimate maybe 1% of that audience is aware, today, that this amazing “gift” exists.  Whose “job” is it, at MS, to fix that – the SQL team or the Office team?

KAMAL:  To be clear we are one team here, with some organizational nuances. We are a BI focused team and we are working together to make sure that we have a cohesive and user centric product. You will see much clearer and broader messaging coming from us, so that everyone will have the joy of experiencing this “gift”.

ROB:  Oh man, that makes my spidey-sense tingle.  ‘Organizational nuances” – I’m going to steal that joke.  When I hear “we all own it,” I insta-translate that into “no one owns it,” and it’s gonna keep dropping between the outfielders, so to speak.  To be fair, I know the SQL and Excel teams are communicating better than ever these days, so I’m not doubting that part.  It’s whether this is even acknowledged as a real problem, an URGENT problem, that I’m still left wondering about.

KAMAL:  One of the things that has changed over the past year or so is that the “Engineering team” has evolved to being a true “Product team.”

In the past we had a very technology oriented approach and built some really awesome technologies (as you were witness to with Vertipaq and Power Pivot when you were here) and then a bunch of different teams across Microsoft took that technology and “got it to market”. Now the focus of the folks who develop the product, is the full product, from end-to-end. A complete view of everything that makes the user successful with the product. This means the technology, the web site, the documentation, the community, samples and yes the messaging.  Essentially we now start with the user first and not tech first.

In particular, I personally am responsible for making sure that we have a big and growing community of users, who love our products and this obviously starts with awareness of all that can be achieved with our products.

So, yes we have learned that the old ways didn’t quite work when it comes to deeply reaching users and we have changed how we operate.

ROB:  Sounds encouraging.  I’ll be watching closely for sure, but you already knew that.

OK, this question is self-serving but I can’t resist sneaking it in here:  how often do the BI software teams at MS read PowerPivotPro.com?

KAMAL:  I visit your site almost daily. Not because of you, though 🙂 . You say all sorts of stuff that I have actually learned to tune out!

But all the users who visit your site and the great comments that they leave, make it really interesting.

Your books also are good resources for new people who join the team and I see these a lot at work.  Even I have one, that I crack open from time to time as I work on demos etc.

ROB:  Wow, that last part rocks!  I want a picture of you prepping for a demo, reading one of the books.  (Then we can add the Reading face to the four-square collage at the top of this post, naturally.)

“I’m Kamal Hathi.  Ask Me Anything.”

ROB:  It’s a bit unfair that I’ve gotten to ask all the questions, so let’s change that.  You’re still up for a Reddit-style “open microphone” experience where our readers can bounce questions off of you?

KAMAL:  Absolutely.  Let’s do it.

ROB:  Thanks Kamal! 

OK folks, fire it up in the comments.  Questions, feedback, praise, criticism, whatever, but remember, always be polite to our guests, even when we have “tough love” to share with them, because we want them to listen, to like us, and to come back.

Also:  Kamal is a pretty busy guy so I’m not sure for how long he will be able to answer.  I’ll close comments on this post whenever he needs to cry uncle.

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 68 Comments

  1. Hi Kamal, I have a question. The number 1 reason I haven’t personally been able to make the leap to PowerBI.com is the lack of Pivot Tables. Is this on the road map, and if so how far off?

  2. Pivot capabilities in tables is indeed something that we get a lot of requests for and it is something that we are looking at including in Power BI. Do not have a time line for this though. You also can use Excel PivotTables in the Excel Web experience in Power BI.

    1. A bunch of the pivot table features I use most frequently are show values as (% of row total, % of running total in etc.) Also grouping of rows and/or columns ad-hoc. Are you working on these features being available as well?

      1. Clarke, the Power BI team had solicited user input around this back in August (See their Survey http://www.instant.ly/s/3vFzx/nav#p/186a0 ). They called it “Quick Calcs”.

        So I know it’s on their radar. I certainly don’t know the timeline and in general Microsoft never promises a specific timeline. But I expect it to popup inone of the future monthly announcements.

    2. Hi Kamal,
      How good is Power BI as an input tool and a BI app at the same time?

      I have an opportunity that requires simple data entry across a large demographic of clients whilst still keeping track of the data within pre-defined parameters. Can Power BI handle this?

      Get in touch

      1. Power BI doesn’t do data input. You will want to develop an application. If you don’t have an app developer, consider using PowerApps, which can use Excel, SharePoint, the Common Data Service, or even traditional databases as the data source.

  3. Which versions of Office 2016 include the Power Pivot addin? I can’t seem to find a definitive answer. The decision to only include it in the Pro Plus version of 2013 was controversial, to say the least – has it been widened out to other versions now, especially given the wide availability of Power BI?

  4. Until earlier this year I was one of Rob’s 99 percenters who didn’t know DAX existed and to whom PowerPivot was just a pivot table. To his question of how we can make this better for people to understand, I have some thoughts to run by Kamal. Other folks are welcome to chime in to say their mileage may vary…

    First, the online help seems poor. It uses the Excel formula format, which is good for Excel but bad for DAX. If I want to learn about SUMPRODUCT I have a good idea what the output will be and getting there is just arithmetic, so the examples in the help can be pasted into my spreadsheet to follow along. With DAX help topics the syntax is self-referential. Here’s a sample from FILTER

    “In this example, the expression FILTER(‘InternetSales_USD’, RELATED(‘SalesTerritory'[SalesTerritoryCountry])”United States”) returns a table that is a subset of Internet Sales minus all rows that belong to the United States sales territory. The RELATED function is what links the Territory key in the Internet Sales table to SalesTerritoryCountry in the SalesTerritory table. ”

    Huh? I have to deal with RELATED as well, and I’m returning a table to a table. Can you imagine how daunting this is for someone trying to discover DAX? On top of that I have to know some sort of source cube data (AdventureWorks? Contoso?) that I didn’t know existed. To what data are your referring? Sure, DAX deals in data rather than arithmetic, but you have to make it easier for us!

    Second, I’ll hammer on about the sample data again. It’s hard to find and download, so much of the language around it is designed for SSAS freaks, and I have to buy multiple books that hopefully use those particular data sets in order to grasp some fundamental objectives. And it has data from a decade ago… not necessarily a deal-killer, but as a rookie I admit I looked around to see if I’d pulled a prior version of the data!
    And the data is structured pretty simply. Again, I need to buy a book to see any sort of advanced discussion of how to manipulate the data the way I need to in the real world, and it’s hard to take the simple sales catalog structure and adjust it to the convoluted relationships we business users deal with.

    Third, pivot tables are ultimately of limited use to me if I want to get work off people’s backs and into the tool. The executives I deliver them to won’t use them – the answer to their question should be directly in front of them. The pivot table may answer that question in its current format but they won’t manipulate it for their “what-if” questions… especially not if I’ve added a bunch of sophisticated DAX measures. They’ll expect me to do that . Or my staff. Yes, it’s just 30 seconds of dragging and dropping but I didn’t save my team any work. Yes, initially I did save hours of custom queries and subsequent cutting and pasting into a spreadsheet by using Power Query and developing the DAX measures, but that’s water under the bridge at this point. Then we have to reformat the page when the new pivot results push the columns and sliders off the screen and our users wonder why everything went away.

    Fourth is the many:many or related table curse. I’ve seen the blog posts on the “magic” formula to resolve many:many, but my data doesn’t always follow the simple relationship there. Those are set for two lookup tables joined to a fact table… but I have two fact tables joined to a lookup table. A simple question – how many items were issued from A but not B – is a brainbuster for a newbie like me when A and B are separate processes and can’t be joined into a single table and the only thing relating them together is an item number and issue date. (A items are purchased directly from the vendor via POs while B items are issued via MSR from our warehouse/depot. Totally different mechanism and source tables. We’d like to have an item always be one or the other, but how do we find out which ones?)

    That leads to dashboards and the various CUBE functions. Using MDX for a top 10 list of something based on the combined value of 10 slicers is tough. How do multiple slicer values go into that query? Can I return both the name and the value or is it a separate way to get at each? Should I instead create a dummy pivot table on a hidden sheet and copy the filtered results to the executive page? How many blogs do I need to search to come up with that answer (I still haven’t found it) when I can’t even construct the MDX yet? CUBE functions appear valuable to me (I think, since I’m still a rookie) because if my executives are looking for a single number *and* their what-if questions, I can give them a bunch of key numbers and the detail underneath.

    Example: What percentage of my ordered products are received at my facilities within 24 hours, and what are the top 10 vendors in terms of delivery percentage along with that value? I’ll have slicers by street location, department, delivery method, order date, PO type, and so on. A lot of lookup tables are connected to my factOrder table and that connects to my factReceiving so if I am applying a PO type filter from my dimPO table and a delivery method from dimDelivery, am I correctly ensuring the filters trickle down to the factReceiving table? This may be second nature to you, but not to me! And how can I relate the Contoso or AdventureWorks data to my real-world structure?

    I would suggest the following…
    a) Multiple sample databases, each one to highlight a particular data structure and set of solutions. Perhaps have one each for selected industries or functions. Move away from the simple sales and financial examples you have to shipping, warehousing, transport, marketing, …
    b) Explain what each database and related set of measures is for in words of one syllable
    c) Upgrade your help. Steal the “DAX Pattern” idea from Marco and Alberto – or pay them handsomely – and expand the help to explain what pattern each DAX command can fit tied to the respective databases.
    d) Clarify how and why Power Pivot or CUBE functions should be used according to those patterns

    PowerBI has the worthy goal of trying to bring data analysis to the masses in the same way spreadsheets delivered mass market numeric analysis. It’s tough, but I think you have a winning product here. It’s buried under code words and shibboleths at the moment, and I’m not sure how you can change that.

    1. Great feedback and suggestions. I think we actually will do some of this.
      One of the places where we are putting in a lot of effort going forward is explaining how to use the product. This means better docs, samples, videos etc.

      1. Thanks. I immediately grabbed the Procurement Analysis sample. It’s pretty elementary… the pictures are nice, but the structure is simple… The measures are basic AVERAGE, SUM calculations and the charts just break those out by various dimensions. Changing the structure of a graph is still daunting to a user not schooled in PowerBI. Still, it’s a good start that I wish I’d known about several months ago.

        Ultimately I need help in traversing data between fact tables joined by lookup tables at the parent or even grandparent level. I can see how a sample database with that sort of relationship would take a long time to explain. But, setting up the data is 90% of the battle.. once the measures are right, even an idiot like me can generate a graph!

        I’d also like to keep this in PowerPivot rather than PowerBI. Our organization hasn’t rolled out PowerBI Desktop, we’re still upgrading users to Excel 2013 (!), and most users don’t have admin rights on their machine to install updates themselves. I imagine most other large organizations are like us – compatibility testing of new releases with other apps can take a long time.

  5. Hi Kamal. Do you have any advice on how to get organizational decision makers to jump on-board with the Power BI Desktop? I have made headway with the adoption of PowerPivot within the last year, but am not sure how to push this option (which I love!).

    1. The best bet I think is to show the full Power BI experience, from the Dashboards and Reports in PowerBI.com to the Desktop. The Desktop can create powerful yet really attractive reports with all the formatting capabilities that have been added. I think that for a lot of decision makers seeing is believing and if you can have them to experience some interesting insight that looks stunning, it will get them to ask for more. This is where you can show the power of the Desktop. Oh and by the way, did I say it was for free ($0.0) ?

      1. I’ll add my 2 cents. Showing the new experience is powerful = PowerBI.com Dashboard & Reports. But if you can run a pilot project with your own data and present that in PowerBI.com dashboard/reports (rather than a sample dataset) that would be truly compelling. We often go onsite and build these in as few as 2-3 days. So target something small and just build it in Power BI.

  6. Kamal, just got Excel 2016 last week, and I’ve been having a great time with all the new features since I’m upgrading out of 2010. However, I’ve had one big annoyance with the way slicers are implemented—it’s almost impossible to organize them cleanly on the page, and even creating them is difficult since the slicer box was removed from the field list. In 2010, slicers would drop automatically into defined boxes on the sides of my data, but in 2016, there’s no way for me to organize them efficiently (at least, to my knowledge).

    Are there any plans to update slicer functionality to make reporting and analysis more clean/viable? Thanks!

    1. We are working on refining the experience. As always we are already working on the next iteration of the product capabilities and are looking at this. No final plans yet though. So, sorry nothing definitive I can share.

  7. Hi Kamal – we have been using Power BI (online) since March 2015 and love, love, love the product. We have been a huge fan of Powerpivot for the last couple years and have built our data model with the 2010 add-ins of Powerpivot and Powerquery, thanks to Rob and powerpivot(pro)’s book and online training videos. We have been utilizing the trial version of Power BI pro and would like to sign up for the service in order to utilize all the visualization and sharing tools going forward. However, we have had difficulty understanding how to give Microsoft our money :). We do not have Office 365, but have Office 2016 Professional. We prefer to not transition to Office 365, but would rather use Office 2016 Professional with Power BI (online) and Power BI Desktop. Is there going to be an option to pay for Power BI (online) without signing up for Office 365?

      1. While we work great together with other O365 services and I think you will get a ton of value together, as Avi says, Power BI can be acquired without having to purchase other O365 services.

  8. If I build a chart in Excel 2016 (standalone) and then convert my data model to Power BI Desktop, only the connections and tables convert. None of the charts made in Excel 2016 move into PowerBI Desktop. This is cumbersome as then I have to rebuild all the charts again before publishing online. I get the same results if I publish directly from Excel to PowerBI Pro online, a rebuild of the charts is needed.

    Really it’s not that bad if it’s one or two charts, but when I create 15 charts with multiple slicers easy conversion for sharing would be awesome.

    1. Power BI is evolving rapidly and we are adjusting features and adding capabilities as we hear back from you and others. So we have added the initial Excel integration capabilities and now are going to evolve these. Your feedback really helps us in this.
      As I indicated for the Excel features, so for the Power BI features, please do go over to https://support.powerbi.com/forums/265200-power-bi and submit/vote for the idea.

      With all this said, it is important to understand that the sort of easy conversion that you are asking for is very hard and not something that we could do in a short period of time.

  9. Our top challenge in enterprise adoption is still the 64-bit requirement. It takes a 5 minute conversation to guide people into the manual process to remove the 32-bit version of Visio and get 64-bit installed and check for incompatible add-ins and macros. What would help is a compatibility checker tool that we could configure and provide guidance on next steps to get the right software removed and added. Any thoughts on how to improve the roll out for large enterprises?

    The second challenge we are fighting is how to create training for software that is being enhanced so quickly. No sooner do we create help documents then they are completely out of date. The buttons have been moved, renamed, pictures changed, or the process is completely different. We like the new features, but the change makes it really hard to onboard users. Do you foresee a point where this rapid development phase slows down? It seems like the focus was on Power Query (last year?) and now it’s all out on Power BI.

    1. Mike, if it is an option, you may be able to sidestep the Office/Excel 32/64 bit issue – if you can go to Power BI Desktop. You can jst install the 64-bit, no conflicts 🙂
      But I agree some tools (like compatibility checker) would help the Office/Excel users deal with this.

  10. With Excel 2016 Professional we noticed that the data model supporting Powerpivot and Powerquery breaks/disappears if there is anything in our PERSONAL macro workbook. As power users, it sucks to have to choose between Powerpivot/Powerquery and macros. Is this a known issue?

    1. I believe it is a known issue. I was troubleshooting this with Microsoft R&D during the Preview. I also have a support ticket open through Office 365.

      However, we never figured out that it was related to having a personal macro workbook with contents (which I do). So it’s pretty helpful to know I might be able to work around the issue in the meantime. Thanks!

        1. In Excel 2013: 1) Have a Personal Macro Workbook 2) Open a PowerPivot file 3) Open PowerPivot window 4) Exit Excel 5) Get prompted “Want to save your changes to ‘Personal.xlsm’?”
          I have always assumed that something under the covers is either writing to my workbook or setting a flag that Excel thinks it’s been updated. Whatever is doing this I consider a bug in 2013.

          1. I always assumed this was a 2016 issue since it didn’t start arising for me in 2013 (in which I used Power Pivot extensively). Interesting.

    2. I confirmed today, even without the macro workbook (moved it out of XLSTART folder), Excel 2016 will sometimes inexplicably delete the data model.

      1. Yes, actually this burned me last week too, and I just now connected the dots back to this thread here. I’m slow sometimes 🙂

        Definitely did not have personal macro workbook, this was a clean install.

        1. Hi Rob. I have been struggling with crashing excel (and now a disappearing data model) in EXCEL 2013 64bit for months. I think the problem is the interaction between Powerpivot and Macros. As a workaround I have to open the crashing workbook (without enabling macros)>add a comment to the VBA and save the file>close and reopen the file. Then macros can be enabled without crashing the file. I do not have personal macro folder but I have narrowed the problem to the Excel15.xlb file. This file seems to be overwritten every time excel closes or saves. Sometimes the file gets corrupted and my workaround overwrites the file with a working version. I hope this insight can help Microsoft solve this problem.

  11. With Power BI Desktop, this should not be as much of an issue in that the install and deployment is independent of other applications including Office.
    For Excel the suggestion for a best practice/roll out guide and tool is a good one. Will pass that on. Also, please do use http://excel.uservoice.com so that others can provide their feedback and vote on this.

    In terms of the pace of development, we want to make sure that the product is as full featured and capable as possible as soon as possible. Frankly, we are catching up in some places and need to keep pushing hard to have a product that you all really love. So, I think that we will continue on the rapid update cadence, though I do not think that you will see as much of large scale experience changes as you did in the past months. With that said, we do respond to the community feedback and if there are places where such changes are required we will respond and you may see more drastic changes in places.

    We are also investing in providing more in terms of webinars, videos, samples etc. so that your training challenges will be somewhat mitigated and you can onboard users more easily.

  12. Hi Kamal,

    1. Do you have any kind of a tentative road map for the future of the Data Catalog (when the PowerBI and Azure versions will be merged, which features will make it into the final product, what new features will be added?)

    2. Has the idea of implementing a proper Data Virtualization platform (like Denodo, JBoss DV, etc) been discussed at Microsoft? If you look at how with SQL Server + PolyBase you can now transparently query and join SQL tables with Hadoop tables, and combine that with the already existing capabilities within Power Query to query almost any data source imaginable, if MSSQL/PolyBase could be extended to support running Power Query M queries, then you could write a plain SQL query that joins MSSQL to Oracle to Excel to Hadoop to ODataFeed to ActiveDirectory, and so on – and furthermore, because this query is taking place within SQL server, it is then available to any client capable of calling MSSQL (so, basically everything), and additionally, this complex query could be written once and saved in the Data Catalog for consumption from Excel & PowerBI desktop.

    Of course there are other things that would be necessary to make this practical (semi-intelligent caching capability, ability to read remote statistics without existing role limitations, ability to join to stored procedures as you can with openrowset, lineage and dependency tracking, execution logging, etc) but it seems to me that most of the fundamental capabilities already have been written, they just happen to spread out over multiple different products. (I actually emailed David Dewitt some more specifics on these ideas but not sure if he replies to unsolicited emails from the internet.)

    Is this functionality on Microsoft’s radar???????

    3. Are there plans to add “proper” support for Stored Procedures and TVF’s to Power Query and the Data Catalog? As it is, you’re basically limited to handcoding ‘exec mySP’, but it would be better if parameters were formally recognized (with differentiation between required and optional), and for those published in the Data Catalog, it would be very beneficial to have the ability for the administrator to very explicitly document acceptable values, etc. It seems to me stored procedures would be one of the primary ways DBA’s would be willing to allow external access to their databases – carte blanche read access to tables (as seems to be the recommended advice in most examples) is not very realistic in the real world.

    4. If you look at this demo video of the *Azure* Data Catalog: https://www.youtube.com/watch?t=18&v=GcAbjWOpcNg you see **much** richer metadata and search capabilities – are there plans to bring a richer experience like this to the Data Catalog search in Power Query? As is, the usability **really** starts to break down once you get beyond 30 or so saved datasets.

    5. Just a comment: as others have mentioned, better documentation would be very welcome – I’ve had some issues with proxy configuration, afaik there is no formal documentation on this, each product has different configuration requirements, and the error messages you get when something is not right are not very helpful.

    That’s probably more than enough questions for now…thanks for doing this AMA, I hope other MS people will make themselves available from time to time! 🙂

    1. Overall, a better metadata story, data virtualization etc. are certainly on the list of things that we are looking into.
      Data Catalog is a really good start for the metadata management and we are exploring ways to integrate with this, no firm plans yet but this does come up a lot in terms of customer asks, and we (if nothing else) do listen to our customers.

      We are working to improve docs and I will pass on your feedback on the proxy configuration.

  13. Kamal, I worked for a very large company that viewed Power Pivot in Excel as self-service BI. Since I left, Power BI Desktop has been released, and I am sure that it is viewed the same way. However, I think that everyone will agree that the leap from “Excel to DAX” is not a trivial process. It’s nowhere close to being the same experience as becoming an Excel power user. PowerPivotPro and others have stepped to fill that training and evangelistic gap, but IMO Microsoft has not provided a clear path to facilitate or motivate the Excel users of the world to upgrade their skills. What would need to happen within Microsoft to promote the usage of this fabulous tool for the masses?

  14. Rob essentially asked a similar question in the main part of this “interview”. As I said, we are going to be pushing awareness of Power BI Desktop, DAX etc. really hard and I personally am taking this challenge on.

  15. Thank you for taking on the promotion of this great tool. Anytime I talk about PP with anyone that is promoting the use of data… At a conference.. Selling me on a dashboard tool.. An educator.. Or even just someone that says they love Excel.. They either say ‘haven’t heard of it’ or ‘yeah I read something about it’.

    I am sold on all the tools, but in a growing medium sized company the obvious selling point are the adhoc reports that we can generate from Excel.

    Frankly I love the look of the designer/BI.com.. But in my opinion two hurdles for us will be 1. The accountant selling the investment in a server to share the visualizations properly.. And 2. Feeling that there is a seamless connection between the established Excel model and what ports over to BI.

    I look forward to updates that bring this all together. DAX and the education on PPPro are nothing short of amazing for an Excel power user. Keep up the great work!

  16. @Kamal

    Power BI Desktop has a great potential of replacing Access as a Desktop Database (ETL (PQ) + Aggregator(PP) rolled in to one)

    The only missing piece is allowing people to directly connect to a pbix file from Excel without using a cumbersome work around – like using DAX studio to find out the the local host number

    How soon should we except connectivity from Excel to the Data model in pbix

      1. @Avi – Have spent all my 3 votes on this already !!!
        If this does get implemented it is going to be a Game changer for Power BI (Desktop) adoption with ALL my Clients

  17. Would love to see a version of Power BI that is completely on premise. My company won’t be loading data into the cloud anytime soon!

    1. Fraser,
      Bizarrely there is a third-party solution by Pyramid Analytics (a MSFT Partner) which provides an in-house server to host your Power BI files.Bizarre that MSFT does not yet have such a product, but I am sure it is coming soon. What I am not sure and perhaps Kamal can advice, would that be the next version of SharePoint? Or a standalone option? Or instead of On-Prem it would be more like Dedicated Office 365 option?

      Pyramids Analytics: On-Prem publish of Power BI Desktop files
      Read: https://powerbi.microsoft.com/en-us/landing/pyramid

      1. Pyramid is a close partner and actually helped with parts of Power BI. In general we want a strong partner eco system and so not so bizzare really.
        In terms of our on-premises plans, nothing to disclose yet, but no question that on-premises is super important and you can see the sorts of investments that we are now making in SSRS as an example.

  18. Kamal,
    My favorite part was the piece where you talk about how the “Engineering teams” have pivoted to be a true “Product team” – handling end-to-end technology to messaging. Once I read it, it is obvious that the Power BI team is operating in this fashion. But it took your words for that to register. Now that I get it , I would say that is a fantastic change and bodes really well for Microsoft’s future. I can already see the positive impact in the Power BI realm (the customer engagement is off the charts).

    I did have one question: there were several news articles recently about how Microsoft is changing its financial reporting structure to: Productivity and Business Processes (Office), Intelligent Cloud (Azure and Power BI?), More Personal Computing. Is that just a financial reporting change or that is deeper? Are Office and Power BI in separate camps now? I know you speak about how the teams work closely. I am hoping this change does nothing to change that.

    1. Glad that you like the customer engagement. Having a vibrant community that loves all aspects of the products is super important to us.
      Financial reporting and product stewardship are not the same thing and you shouldn’t get too caught up on that.

  19. Thank you, this clarification helped in many ways.

    Yet: Still not quite clear which team I should adress to help making this vision come true:

    “After Power BI showed you where you actually stand compared to budget and helped you discover the reasons for it, you then use X to update your planning figures.
    These will then automatically passed into the central database/-model where the subsequent processes will be fed from (like refresh of depending calculations, approvals and other communication tasks that can better be organized centrally).”

    What stands the X for in your eyes?

    1. @Imke the X can be Excel 🙂 And it often is in many organizations. The resultant planning figures (manually maintained) can then be sucked into the model if needed.

      Or you can use third party tools. One team we work with is Power Planner: Check out http://www.power-planner.com/Features
      If interested, then contact them for a demo. The website does not do justice to the product.

    2. Grüß Imke,

      As Avi mentioned, Power Planner(http://www.power-planner.com) is extending Power Pivot models into a Forecasting and Predictive Analytics tool for Excel and Power BI. For your example, we would apply the ‘Goal Seeking’ feature of Power Planner that analyzes the supporting data from a SQL table or Power Query to make predictions and estimates about ‘X’ and then writes the results back to your SQL Data Warehouse.

      There is a demo you may download here that shows the high level feature set of Power Planner: https://poweronbi.sharepoint.com/sites/Partners/_layouts/15/guestaccess.aspx?guestaccesstoken=b11pebb9WsDEUeMnY8NN3P2QMhNjHhFemXnp8niut2s%3d&docid=0f4a0b65c5efa485e8c9c6f5259bb8888

      Please reach out to cf@power-planner.com if you have any questions.

      Best, Chris.

  20. Kamal,

    Thanks for taking questions and giving us a peek inside of Q Branch!

    Is it more fun to work at MS with Steve out and Satya in? 🙂

    Also, do we have any early returns/metrics/analytics on how the “Frying Nemo” demo was/is being received?? 🙂

  21. With pivot tables and resizable objects in Power BI Desktop it could possibly replace Excel as the power user’s BI tool of choice. Thanks for clarifying the positioning of Power BI Desktop; competing head on with Tableau and Qlik. I think most people reading this blog get this. However, outside of this small group, not many people has got a clue, and that, unfortunately for MS, includes a lot of people involved in upgrades of the MS platform. Personally, I lost faith in MS with regard to Excel along time ago. It is, obviously, a product with lots of features that needs a great deal of regression testing once you decide to incorporate new features. Hence, the Office team will always be conservative. An example: Visual Studio gets upgraded every now and then (2005, 2008, 2010, 2012, 2013, 2015), but in Office you still have to settle with VB6/7 for Applications. Not having made any major upgrade to the scripting language for 15-20 years is not that great. Perhaps, integrating VSTA into Excel some 10+ years ago wouldn’t have been such a bad idea. Judging from what can be done by scripting SSIS (by using VSTA and C# or VB.NET), I wonder if MS didn’t make a huge mistake a decade or so ago. I am, however, quite positive to what is being done with Power BI Desktop etc in the short term. By 2020 I forecast that accountants are still using ordinary pivot tables and Excel formulas, BI consultants are still doing the more advanced data modeling, some power users are capable of doing basic data modeling (few will know how to model many-to-many relationships etc), Excel users have to settle with a 20-25 year old scripting language, Power BI is mature, has changed name to Cortana or possibly Trump and no more updates will be available until 128 bit computers enter the scene. In essence, all the same but with more advanced tools. Sorry Rob, I see no revolution coming anytime soon on the user side. There will, however, be more usage of these more advanced tools. Agile BI tasks will mainly be performed by BI consultants or Excel power users like yourself with an interest in BI. Report writing is easy and data modeling can be very difficult. One will have to invest a lot of time in order to learn DAX, M, data modeling etc. Regarding education, in some European countries the only way to learn DAX the professional way is when Marco comes to visit, once a year or so. Unfortunately, not many people registred for the course in my country this year and it was canceled. Anyone who thinks there is correlation with sales of Power BI, upgrades of Office etc?

    1. so, no revolution coming anytime soon on the user side. Hmm.

      Guess we need to put some metrics around what is or is not a revolution to settle that one..How many changed careers, transformed companies or millions of screens does there need to be to constitute a revolution?

      MS is giving us the tools PowerPivotPro is evangelizing or doing their part to show a way that non-coding, non-technical folks (the people) can stay relevant in this crazy new data world we live in.

      Also, “you can lead a horse ……

  22. Let me first point out that Microsoft is offering something great (for free), and that PowerPivotPro is a great resource for learning about Power Pivot, DAX etc. Learning this stuff is, however, not easy, especially not the data modeling part. Here are some early comments from bright people who were once students themselves:

    – Although DAX looks kind of friendly up front, it gets pretty gnarly pretty fast. Cathy Dumas 2012

    – I agree with your comment on “Although DAX looks kind of friendly up front, it gets pretty gnarly pretty fast.” Julie Koesmarno 2012

    – Ever since PowerPivot got released, one of the questions I’ve heard debated over and over is whether it’s easy for non-IT users to learn and use DAX or not. The stock answer from Microsoft, and I agree with them here, is that anyone with basic Excel knowledge can do simple calculations in DAX, but the more complex calculations (for example, those which need to use the CALCULATE() function) are probably only ever going to be written and understood by BI professionals. Chris Webb 2011

    – I have to admit that I am having trouble with DAX after years and years of straight Transactional data with SQL and with Excel formulas. Dick Moffat 2010

    And some words from Marco Russo and Alberto Ferrari (excerpt from Microsoft Excel 2013 Building Data Models with PowerPivot):

    – Because you are reading this introduction, you are probably interested in joining the self-service BI wave, and you want to learn how to master PowerPivot for Excel. You will need to learn the basics of the tool, but this is only the first step. Then, you will need to learn how to shape your data so that you can execute analysis efficiently: we call this data modeling. Finally, you will need to learn the DAX language and master all its concepts so you can get the best out of it. If that is what you want, then this is the book for you.
    We are BI professionals, and we know from experience that building a BI solution is not easy. We do not want to mislead you: BI is a fascinating technology, but it is also a hard one. This book is designed to help you take the necessary steps to transform you from an Excel user to a self-service BI modeler. It will be a long road that will require time and dedication to travel, and you will find yourself making the adaptations you need to learn new techniques. However, the results you will be able to accomplish are invaluable.

    SOME PEOPLE on the business side will successfully manage to learn these skills, but they will probably need to have a very genuine interest in BI as they have to put in a lot of time and effort. At some time they probably move on to become BI (or management) consultants. At present, I do not see that happen very often. “Those guys” that Donald Farmer talked about back in 2008 are quite rare in my view. If MS decides to offer proper training, however, or Mr Collie expands his business to the Nordic countries, who knows what might happen here.

    Until then, if someone knows anyone who knows anything more than basic tabular modeling in Power Pivot and that person is located in the Nordic region and also has an accounting background, that special one will most likely get a lot of job opportunities once MS learns how to market this great tool.

    1. Mr T.,

      Super thoughtful post! Thoroughly enjoyed it.

      I love Italy as much as the next aspiring DAX master but it misses the point it seems to.

      As Rob has made abundantly clear over the years, the #PowerBi game is not only or even primarily for the Stanford C.S. PhD’s…it’s for the rest of us. Perhaps “Basic tabular modeling” IS the self-service revolution. And, yes, the strong swimmers will end up on the road to Italy or perhaps die trying get there 🙂

  23. Great comments.. Being a long time Excel guy from the business side, looking for the next level, power pivot (and this site) is such a good fit. Coding is not for me, but learning a query language like DAX is a natural fit to what I was already doing with spreadsheets. I have to wonder how many on the business/analyst side are just like me, and if properly marketed to, would get far enough to realize the vast improvements and flexibility of DAX. There is a learning curve, however the benefits far outweigh the cost for a financial analyst in Excel everyday.

    The comment about business side people that take to DAX probably already have a BI interest is interesting. As I learn more about DAX the more I want to learn more about the BI side of things.

    The big win is that medium sized companies with a committed Excel person can learn from this site and use a powerful, free, self-service product to provide timely insights for management.

  24. Difference between PowerBI and PowerPivot.

    I am a user of PowerPivot for Excel 2010. We create sales dashboards with slicers.

    Even though I have been reading on various forums, I still don’t understand what PowerBI provides more than PowerPivot, if excluding sharing features and some new visualizations.

    1. Roger, you nailed it. Power BI is Excel BI (including Power Pivot and Power Query) with new visualisations and cloud sharing capabilities. In addition, it is stand alone which means the update and development cycle will be superior to Excel moving forward. 3 months ago I really didn’t give Power BI any of my focus – in my view there we’re too many gaps. Today all that has changed and it is worth my focus. By early next year I think it will be superior to Excel JMO

      1. Matt’s answer is an understatement. Take a look at the “Q + A”, custom visualization, and “Get Insights” button as just a few of the examples of what you can’t do with Excel. Would you rather have new features WEEKLY, or every 3 years? Power BI Desktop is a free download. Get it and check it out.

Leave a Comment or Question