skip to Main Content

Coffee Talk

Welcome to the latest PowerPivotPro Coffee Talk, where members of the community discuss various topics related to Power BI, Power Pivot, and Analytics/BI in general. These conversations take place during the week on a Slack channel and are then lightly edited for publication. In this installment of our Coffee Talk series, we’ll be chatting with The Power Pivot Utility team: Bertrand d’Arbonneau and Marco Russo, along with P3’s Reid Havens and  Matt Allington. Let’s introduce everyone.

Bertrand d’Arbonneau, Author of Power Pivot Utility. Currently dedicated to driving the adoption of Self-Service BI at Gemalto. As a teacher and mentor, Bertrand empowers business analysts across the company to create flexible analytic solutions with the PowerBI suite. Bertrand has a broad experience in a global international and industrial High-Tech environment, with roles in dashboard design, performance improvement projects, quality management and R&D.

Marco thumbnail

Marco Russo,

Marco is a business intelligence consultant and mentor. He has been working on Analysis Services since 1998 when the product was called OLAP Services.

With the experience of many end-to-end projects, with Alberto Ferrari, he wrote several books about Power BI, Analysis Service, and Power Pivot. They also regularly write articles and white papers that are available on www.sqlbi.com. Marco is a Microsoft MVP and an SSAS Maestro, the highest level of certification on Microsoft Analysis Services.

Today, he spends most of his time in training and consulting on DAX and data modeling for Power BI and Analysis Services. He travels in several continents visiting customers and teaching public classes.

Marco is a regular speaker at international conferences like Microsoft Ignite, PASS Summit, and SQL Bits. He is also happy to deliver evening sessions at local user groups during his trips.

 

Reid Havens, Editor, Principal Consultant and Instructor here at PowerPivotPro.  Reid’s background includes a formal education in data analytics, organizational leadership, and technology. He’s had senior industry roles as a project manager, BI consultant, database developer, and business analyst.  He has taught Excel / Power BI at Bellevue College and the University of Washington in addition to our Foundations & Advanced Power Query classes here at PowerPivotPro.

Matt Allington,  BI Professional with over 30 years’ experience in the Consumer-Packaged Goods industry, Principal Consultant here at PowerPivotPro, owner of the company Excelerator BI and author of “Learn to Write DAX – A Practical Guide to Learning Power Pivot for Excel and Power BI” and “Supercharge Power BI – Power BI is Better When You Learn to Write DAX”.

Welcome and Introductions

Matt
Hi, Reid.

Reid
Hi Matt, welcome!

Bertrand d’Arbonneau
Hi Reid, Hi Matt!

Reid
Bertrand, great to be speaking with you.

Bertrand d’Arbonneau
Reid, thank you for setting this up.

Matt
Hi Bertrand, nice to meet you finally.

Bertrand d’Arbonneau
Yes!

Reid
Bertrand, I’d love a brief introduction from you, for our readers.

Bertrand d’Arbonneau
Sure. I joined Schlumberger Smart Cards 30+ years ago. After an MBO, a merger and many acquisitions, the company is now Gemalto. I occupied many varied positions: R&D, Marketing, Manufacturing projects, Lean 6 Sigma improvement projects. It is the need to measure project improvements which led me to become a BI practitioner. 4 or 5 years ago, I discovered Power Pivot. So far, I had become an advanced, traditional, Excel user. I became responsible for designing dashboards for the Manufacturing Management of Gemalto, and eventually to the senior management as a whole. This is when I became preoccupied with documenting the data models that I had developed. Then, 3 years ago, Marco delivered a training in Paris. So, I sure did not miss it. And he encouraged me to finish the add-in I had started to develop.

Reid
That’s great that your connection came through a meeting through a training of his, and led to the encouragement to finish the add-in. It’s one of my all-time favorite add-ins in Excel.

Matt
Mine too.

Bertrand d’Arbonneau
It really started with a personal need, but sharing it brought a lot of encouragement. It has been a very fulfilling experience.

Reid
I probably used it for a month or two before even discovering the extra features via right-click for PivotTables too. Which I also love.

Matt
Extra features?

Matt
Ah yes, I forgot about those!

Reid
These beautiful extras 🙂.

Matt
Bertrand, I think those right-click features are probably very little known by users. Maybe you could put a menu option “list right click features” or something, to make it clear to all that they are there. Just an idea. 🙂

Reid
Exactly, Matt, they were hidden a bit. But so useful! Especially Show Connected Slicers.

Bertrand d’Arbonneau
Yes, you are probably right. They are documented in the help file, but it’s like the small text in contracts.

Matt
Documentation is for wimps. 🙂

Bertrand d’Arbonneau
😀

Bertrand d’Arbonneau
Also, I use the number formatting options from that menu all the time.

Matt
I’ve just been looking at the latest version of PowerPivot Utilities 1.09. It’s very nice, love the new measure dependency feature.

Bertrand d’Arbonneau
I did not borrow the code from you for the new measure dependencies feature, but the idea.

Matt
I was going to ask how you did it. 🙂

Matt
Did you use the DMV? $SYSTEM.DISCOVER_CALC_DEPENDENCY?

Bertrand d’Arbonneau
Yes, I am using the DMVs, and recursive calls to explore all the dependency chain.

Matt
I think it is a great addition.

Bertrand d’Arbonneau
Thanks.

Matt
It is such a gap in all Power Pivot products I think. I am pretty sure there is an idea for PowerBI for this.

Reid
Bertrand, I’m also loving the new dependency chain features!

Matt
It would be great to get a visual dependencies view just like in Power Query.

Marco
Hello – sorry for the delay I didn’t find the link.

Matt
Hi Marco.

Reid
Marco welcome as well. 🙂

Matt
It must be late where you are Marco and Bertrand.

Marco
9:15 pm in Central Europe.

Matt
Marco, not too bad.

Marco
Not bad – there is something you should know about measures in Power BI and Power Pivot. We currently use search & replace in DAX Studio and other tools for identifying measures. However, it is possible to create measure names with the same name of column names.

Matt
I think I heard you talk about this before. Bad I think?

Marco
It is not possible in Power Pivot and Analysis Services, but it is possible in Power BI – it is *not* a best practice.

Matt
Agreed.

Marco
But the worst is…the underlying API (TOM) allows creating multiple measures with the same name in the same data model.

Reid
I agree with Marco, we teach unique names between columns and measures as well.

Marco
But luckily MS might change this.

Matt
There are a few “working as designed” things like this I think.

Marco
I have already seen data models where measures have the same name of columns and it’s already a big pain.

Matt
I think some of the PMs need to put their foot down and tidying some of this stuff up.

Bertrand d’Arbonneau
This would indeed confuse the analysis of dependencies.

Marco
Indeed.

Bertrand d’Arbonneau
Best practices must be repeated and repeated.

Marco
However, my idea is that if a model is not compliant, I would prefer to stop the feature rather than try to implement the feature so that it works with ambiguous names.

Matt
Definitely. Fix it at the source, the design of the software that is.

Marco
So, it could be using a validation of the data model – if there is a measure with the same name of a column, big red flag and stop…

Bertrand d’Arbonneau
I agree. Best option.

Reid
Marco, I like that idea.

Matt
So, Bertrand, any chance you could enhance PPU to connect to Power BI Desktop? My Localhost workbook is a hack compared to your tools.

Bertrand d’Arbonneau
I have thought about it. But it requires quite a bit of coding.

Reid
I second Matt’s question. That was one of mine as well.

Bertrand d’Arbonneau
DMVs are not the same in the different versions.

Matt
Yes, I notice that Microsoft has ‘broken’ the dependencies DMV in a recent version of PBID. Kasper says it is not supported.

Marco
We are working on an alternative approach. DAX Studio will save a file that extracts information from the data model. Then VertiPaq Analyzer will open this file (a JSON file compressed in a ZIP) showing all the info in Excel. This way, DAX Studio does the dirty job of connecting to any kind of data model and already solve differences between versions (if any). But at the moment we don’t save dependencies DMV

Bertrand d’Arbonneau
So, you would still rely on PPU or equivalent for the DAX formatting?

Matt
Marco, dependencies don’t work anymore, for PBID.

Matt
Anyway, Bertrand, I think there is a BIG need for PPU to connect to PBID. I would love to see it. My VBA is not as good as yours, but I would be happy to help.

Marco
Bertrand, the DAX formatting is already implemented in VertiPaq Analyzer (we borrowed some idea from PPU!) but we don’t have all the features of PPU, e.g. VPA doesn’t have a list of unused columns.

Bertrand d’Arbonneau
I already have the code to connect to PBID instance.

Marco
Matt, the problem is that there is no API in PBI to get info about report dependencies.

Reid
I agree with Matt. The PBI world would benefit so much from having the tools in PPUtilities available for pbix files.

Matt
Bertrand, did you see that the Microsoft Store version and Download version of PBID uses a different file location?

Marco
So, you cannot get what is the unused columns/measures, whereas PPU can do that for Excel because there is an API for that.

Bertrand d’Arbonneau
Matt, what location do you refer to?

Matt
Sorry, the temp SSAS storage. I use that to source the Port and DB names.

Bertrand d’Arbonneau
I see. No, I had not noticed.

Matt
I think Marco told me that DAX studio uses a different approach. Maybe using a C# call or something.

Reid
So, there’s a literal software limitation of PBI Desktop preventing certain features from being implemented for it re: unused columns / measures.

Bertrand d’Arbonneau
Yes. lack of API

Marco
Yes, DAX Studio has a different approach that is safer, it’s based on C# code and Darren wrote it – the code is available on GitHub.

Matt
I think the benefit of PBID connectivity greatly outweighs any missing features.

Marco
Matt, but what are the PPU features you’d use with PBI?

Bertrand d’Arbonneau
The code I use is based on exploring the running processes.

Matt
Anyone reading this blog article, make a comment below to tell Bertrand that you want this feature. 🙂

Marco
Besides the list of measures and columns.

Bertrand d’Arbonneau
Time is a scarce resource, I cannot make promises!

Matt
Since I have your ear, Bertrand, can I make a couple of comments/suggestions for enhancements?

Bertrand d’Arbonneau
Sure.

Matt
How about merging the column size information into the “unused column report”? I love this unused column report, and seeing the size of the column would help in the deletion decision process.

Bertrand d’Arbonneau
Credit goes to Idan Cohen. I see what you mean though.

Matt
Then sorting on unused columns, largest to smallest would be good. 🙂

Bertrand d’Arbonneau
Sort of merging the model memory usage and the list unused columns features?

Matt
Yes. Just to help decide which columns to call.

Reid
I think having those in the same location would be great as well.

Matt
Anyway, just an idea for the enhancement list. 🙂

Bertrand d’Arbonneau
I’ll take it.

Matt
Another thing I would like to ask you about. I always have problems with PPU when I open Excel. The menu doesn’t load. I don’t know if it is just me, but I have had the problem since day one. I have to de-register and re-register the add-in to make it appear – I have VBA for that. 🙂 Do you know if this is a common problem?

Bertrand d’Arbonneau
I remember your comment. I have not heard of anyone else with the same issue.

Reid
The not loading issue is something I think many of us experience the not loading issue here at P3, including myself.

Matt
Reid, you have seen it too?

Reid
I have to turn it off in Excel add-ins, then re-enable to get it to show.

Matt
Yes, that is what happens to me.

Bertrand d’Arbonneau
I have this issue with OLAP pivot table extensions, but not with PPU.

Matt
Strange.

Marco
I simply open the XLAM all the time I want to use it. I open the XLAM first, then I open the Excel file I need.

Matt
I will be in Seattle in March. Maybe I can ask the MS Excel team.

Marco
So, I have the ribbon.

Reid
I heard that was an issue from students I had in training, who had installed the add-in as well. Though ALL say it’s still worth it for the features.

Bertrand d’Arbonneau
I have no idea how to troubleshoot such an issue. Maybe the load order of other add-ins. Matt, please ask the Excel team in March.

Matt
Reid, I agree. I just have some VBA on my quick menu to toggle it on and off.

Marco
Maybe we are all using Office 365 Click2Run and Bertrand is using a standard MSI install?

Bertrand d’Arbonneau
Yes. I will soon have the O365 version as well. I hope this will help eliminate such quirks.

Matt
Ah, maybe that is it.

Marco
Nice.

Matt
These are such minor things. I don’t want to distract from such a great product. Marco, do you track how many downloads for PP Utilities?

Bertrand d’Arbonneau
And the count is …

Marco
I don’t have the number here, I’m out of office and that report is not on Power BI yet! 🙂 But, let me try to check if I can access it.

Reid
We’ll be wrapping up soon, is there anything any of you really want to share with our readers, about PP Utilities?

Matt
For those reading this, here are my top 4 reasons to get PPU. 1) List Measures. 2) List Memory usage. 3) Insert Pivot Table (Excel 2013). 4) List precedent and dependent measures.

Bertrand d’Arbonneau
+ format number and + add data bars.

Reid
I love the add data bars button, super convenient!

Marco
Matt, why you don’t use VertiPaq Analyzer for memory usage?

Matt
Marco, habit I think? I remember you teaching it in your DAX class – Optimizing DAX I think. Also, I think there is a lot of stuff in VertiPaq Analyzer that I don’t use. So, I wrote my own that just fetches the simple data I wanted to see.

Bertrand d’Arbonneau
Have you noticed significant differences in terms of memory size reported by the two tools (PPU Vs. Vertipaq Analyzer)?

Marco
Thanks for the feedback Matt, Unfortunately, I don’t have updated statistics about PPU, I can follow up by email later (Wednesday).

Matt
I have never compared the tools, but I am aware of differences in general between approaches. I have never worried about it. A big column is a big column. A small one is a small one. That’s my view anyway.

Marco
Matt, cardinality is also very important, there is a difference between a large dictionary and a large data size for compressed data (not dictionary). But a small column is a small column I agree.

Matt
Marco, that is a nuance that I don’t have the experience to understand the impact. But I would love to chat with you about it sometime. 🙂

Bertrand d’Arbonneau
For the average report, optimization is – at least in my experience – not critical. But I have not dealt with very large models so far.

Reid
Bertrand, I agree. It’s useful to have optimization features, but I don’t think they’re as universally applicable for the average report.

Matt
Coming from a business background, I learned that perfection is the enemy of progress. So, if I get 20% of the information that helps me get it 80% right, then that is good enough for most instances. I am not saying there is no place for perfection. Just that in most instances, “better” is good enough. In Marco’s world, he needs to do that stuff, with data models with billions of rows etc.

Reid
A great example of diminishing returns in regards to report and model design.

Bertrand d’Arbonneau
Same for me. As my role is now to teach and coach users, I try to not lose them with too much technical detail.

Marco
Matt, yes but even Power Pivot and Power BI with iterators over 100,000 rows or more can have performance issues. For this reason, VertiPaq Analyzer is useful to locate performance issues, even if the model is not really a large one.

Bertrand d’Arbonneau
But I appreciate that there are people like Marco who can explain how things really work in the details.

Matt
Me too.

Marco
Thanks. 😀

Matt
I just wonder if Marco has time to do his own work. 🙂

Bertrand d’Arbonneau
The white paper on the event in progress optimization!

Reid
I used that white paper the other week for a project that needed those, haha. However, I think this can be a good stopping point for today’s conversation. Any closing comments from anyone?

Marco
My work is mainly to create content and to help people who have issues – we don’t have time to work on large projects, we help the teams working on them.

Bertrand d’Arbonneau
P3, Marco, Matt: so many thanks for your blogs.

Marco
Power Pivot Utilities is a great tool for Power Pivot. There should be a similar one for Power BI, but shame on Microsoft that doesn’t provide us an API to do that!

Matt
🙂

Reid
Marco, we can keep wishing for those API’s someday…

Bertrand d’Arbonneau
I think there is an idea to vote for, though I do not have the link at hand.

Marco
That idea is almost 3 years old…we have no hopes! 🙂

Matt
For those reading that want to see some detail. I note in my blog I say “hidden gems in the context menus”. I forgot about them! Anyway, bye all. Nice to chat. See you soon Marco.

Reid
Absolutely, Matt, that’ll be a great take away from this read. Well, I want to thank everyone for joining today. I’m glad we could have this water cooler discussion about such a great tool, with the developer no less!

Marco
See you in Redmond Matt.

Bertrand d’Arbonneau
Thanks to you all for your warm comments.

Marco
Goodbye.

Bertrand d’Arbonneau
Good night. Hope to meet you in the real world someday.

Reid
Agreed Bertrand, thanks everyone!

 

Forget bending spoons with your mind – there’s no money in it.

It takes a special kind of mindset to “bend” data (and software!) to the human will.  As this article demonstrates, we at PowerPivotPro can twist Power BI into a pretzel if that’s what an organization needs. (A robust, trustworthy, industrial-strength pretzel of course).

The data-oriented challenges facing your business require BOTH a nimble toolset like Power BI AND a nimble mindset to go with it. And as Val Kilmer / Doc Holladay once said, we’re your huckleberry.

Connect with the Experts

 

Reid Havens

Reid Havens is the Founder & CEO of Havens Consulting Inc. He has a formal background in technology and organizational management, having obtained separate degrees in Organizational Development & Business Analytics. Reid has experience working with Fortune 500 companies such as Microsoft as well as with Non-Profit Organizations. He is also the Executive Editor, contributing author, and Principal Consultant at PowerPivotPro. Additionally he's an instructor at the University of Washington and other universities, teaching Business Intelligence, Reporting, & Data Visualization.

This Post Has 9 Comments
  1. PPU is a FFT Tool!! So glad to have jumped on reading this CoffeeTalk before everyone got in so I had time to download and start checking it out. I have found that the tab does not load and I have to re-select it from the AddIns menu but, Matt and Reid are right, well worth a little inconvenience. I kind of think I had a similar issue with DAX Studio a few years back The only slightly unfortunate thing I have found is that the Delete unused columns feature only works for workbooks using PQ. BUT, I was able bounce between the Unused Column Report and the Memory Usage model and in about 10 minutes reduce usage on a model by 54%. I have been delaying us jumping from 2010 to 2016 here but, this tool has really given me a KITA to get everyone aligned and make the switch… Thank you so much for such a great combination of tools.

  2. Interesting discussion ! Thanks guys.
    Bertrand is coming to all our meetups in France and it’s always a pleasure to welcome him 🙂 interesting questions and good feedbacks.

  3. This was a very great opportunity to hear from the computing master minds of Microsoft BI reporting. Thanks to Reid, P3, and the master minds! Your blogs and tools and other resources are much appreciated.

  4. As far as having to unload/reload the Power Pivot Utilities I was having the same issue. For me, since I was in doubt, i believe I ran a trace with Microsoft’s Process Monitor while started Excel and saw some permission-like issues with Excel trying to load the add-in After a bit more research it turned out to be because the install location of the ‘PP_utilities.xlam’ file was in a location that Excel didn’t “Trust”. So went to Excel -> Options -> Trust Center -> Trust Center Settings… -> Add new location…. and added the path where file was installed, which for me is: C:\Users\mattbrice\AppData\Roaming\Microsoft\AddIns. Once added to Trust center, problem solved.

    1. Great! Thank you for sharing your findings and method. Let’s hope it solves this issue for all the users who experienced it.

    1. It’s on the right but, you need to type your email address in (to the left of the Download button) to get the download to start

Leave a Comment or Question