Articles

Power BI Workspace Metrics

Power BI Data Dictionary @vizbp

How to automate a Power BI Data Dictionary

Whether you want to analyze an individual workspace or your full Power BI environment (all workspaces and solutions you are an admin of), you will find the Power BI Data Dictionary solution invaluable. YOU DO NOT HAVE TO BE AN ADMIN with this product – just an admin of the Premium workspaces you want to analyze!

Premium is required currently until XMLA connectivity is opened up to non-premium workspaces. The only other requirement is for the models to be documented – for the developers to enter table, column, and measure definitions when creating the model. Unfortunately, there is not much documentation regarding this specifically for Power BI – for Power Pivot and SSAS, there are plenty of references.

There are other solutions available for this functionality but most are only available for single models or SSAS solutions (I will reference these at the end of the post).

What do you do if you want to document or analyze all of your solutions? What if you want to review solutions you did not author but are published in your environment? You would have to develop a solution like this from scratch or analyze each of the solutions one at a time. You still can – don’t get me wrong. The value in this product just gives you a huge head start on that development and costs much less than the hours it would take to do so. It even has some capabilities that the other solutions do not.

    It is a fully functional data model – joining all Power BI models and DMV queries to be able to see capacities, workspaces, tables, columns, and measure DAX expressions. It also has all of the visuals built out to easily analyze your Power BI environment.

    It utilizes the Power BI API to query your Power BI environment for all workspaces of which you are an admin and the underlying datasets. It then queries each one utilizing DMV functions to extract the data definitions.

    If you want to connect to the Power BI API and build this solution yourself, you very well can. You just need to create an Azure Active Directory Application and download some freely available connectors and/or utilize various methods to skirt around the OAuth2 authentication protocol (I will include links below) and you can begin connecting to your Power BI environment. In order to build reporting, you have to do some hi-jinks and jump through some hurdles (and be pretty good at M) in order to build out a functioning model.

    You also have to know DMV queries and how to join them all together:

    But, believe me, it takes dozens to hundreds of hours to understand and build out a solution like this – not even mentioning the documentation that is required to make a product presentation ready for sales.

    If you have the time, I encourage you to go for it. It is an experience building something like this that is so valuable to an organization or to the individuals like me. I want my Power BI solutions and environment (Pro or Premium) to be fully optimized. I thought:

    1. There has to be a way to see all of my solutions I have access to.
    2. There has to be a way to easily filter to find all solutions by owner ( in this case, the person who imported/created the dataset).
    3. There has to be a way to see and search all data model metadata in a single view.

    I have built a product that does these things and more!

    Take a look at the product and see if it would be valuable for you or your organization. It is available on vizbp.com:

    Power BI Data Dictionary

    While there, take a look at other solutions people like me are making available for free or for purchase. Take a look at becoming a seller yourself and offering your solutions in the marketplace.

    https://vizbp.com/sell/

    While here, take a look at my other articles related to vizbp.com:

    References to other Power BI data dictionary solutions (my apologies if I did not reference yours – let me know if I failed to and I will add yours as well):

    Continue Reading
    Logo-Text

    Why I started vizbp…..


    Visualization Blueprints – vizbp – is the first marketplace built for reporting and data analysis. Jumpstart your reporting projects with ready-to-use, proven reporting templates/solutions from creators around the world.

    https://www.vizbp.com

    I am a hustler. All (most) consultants are in one way or another. You have to be to find consistent work.

    But, my mind is always thinking of other ways to make money and provide something valuable to others. What can I do or make to make things easier for others.

    In my career, I have implemented or been involved in so many different software initiatives where the application or reporting was lacking features or reporting was non-existent. In those scenarios, my first inclination is to look to see if there are any solutions available for download or for purchase. This is typically not a successful venture.

    So, I have written code, built reporting or enhanced these solutions myself. My first thoughts thereafter are

    1. How much time could I have saved if I was able to purchase something – even if it was to just get me started and I was able to enhance it? The cost to purchase would likely be much less than the hours put in to start from scratch.
    2. How valuable is that time to a business? The opportunity cost has to be considered. It is not just the developer time, it is the time to solution functionality or data analysis from the business. What is the cost of that delay in business processes?
    3. How can I provide these solutions to other people or businesses like me?

    I have tried a few business ventures and have finally found a good platform to bring this idea to fruition. vizbp (short for visualization blueprints) was created as an eCommerce marketplace in the same vein as Etsy, Themeforest, or eBay. It is a digital marketplace where developers (and non-developers) can sell or provide their business intelligence, reporting, code, or applications to what I believe is a huge customer base.

    Some code and solutions will be free. Other solutions will be for a cost but will again be much cheaper than if developed from scratch and will provide a head start to productivity or efficiency.

    What I believe is the most beneficial feature of this business is the conglomeration of these solutions into a single marketplace/site. There are plenty of sites out there where developers or businesses are selling their individual solutions. Because they are individual sites, they are harder to find. Bringing those solutions together and making them easy to find will improve their sales and make it much easier for the consumer.

    From a consultant perspective, placing content or solutions on this site will in turn drive consulting business. If a consumer purchases or downloads free code from the marketplace that you have designed, who will they turn to for support? Who will they contact for enhancements?

    After you build those enhancements, what can you do with the enhanced product? Sell it or provide it to others on vizbp!

    Bottom line – this site will drive revenue – from passive sales of your already developed products to the consulting revenue to enhance or support the product(s).

    If you are interested – or know someone else who may be interested – and have developed code or designed solutions for software that is widely used, go set up a profile. Go let others know about the site. Let your customers know about the site.

    The site will only be as good as the content you provide!

    You can visit the site to see the features available to you as a customer or as a vendor. You can also contact me with any questions you may have regarding the site:

    sales@vizbp.com

    https://www.vizbp.com

    Continue Reading
    Power BI Workspace Metrics @vizbp

    How to analyze your Power BI environment and review solution performance

    Whether you want to analyze your personal “My Workspace” environment, your gateways, or all workspaces and solutions you are an admin of, you will find the Power BI Workspace Metrics solution invaluable. YOU DO NOT HAVE TO BE AN ADMIN with this product – just an admin of the workspaces you want to analyze!

    Until now, the Power BI Capacity Metrics solution was the only real solution provided for this functionality but it is only available for Premium environments. What do you do if you want to document or analyze your solutions in non-Premium environments? You would have to develop a solution like this from scratch. You still can – don’t get me wrong. This product just gives you a huge head start on that development and costs much less than the hours it would take to do so. It even has some capabilities that the Power BI Capacity Metrics solution does not.

      It is a fully functional data model – joining all Power BI artifacts to be able to see lineage, relationships, types of data sources utilized, alignment to gateways, ownership, etc. It also has all of the metrics and visuals built out to easily analyze your Power BI environment.

      If you want to connect to the Power BI API and build this solution yourself, you very well can. You just need to create an Azure Active Directory Application and download some freely available connectors and/or utilize various methods to skirt around the OAuth2 authentication protocol (I will include links below) and you can begin connecting to your Power BI environment. In order to build reporting, you have to do some hi-jinks and jump through some hurdles (and be pretty good at M) in order to build out a functioning model.

      But, believe me, it takes dozens to hundreds of hours to build out a solution like this – not even mentioning the documentation that is required to make a product presentation ready for sales.

      If you have the time, I encourage you to go for it. It is an experience building something like this that is so valuable to an organization or to individuals like me. I want my Power BI solutions and environment (Pro or Premium) to be fully optimized. I thought:

      1. There has to be a way to see all of my solutions I have access to.
      2. There has to be a way to easily filter to find all solutions by owner ( in this case, the person who imported/created the dataset).
      3. There has to be a way to see all of those refresh schedules in a single view.
      4. There has to be a way to see refresh history and performance over time.
      5. There has to be a way to marry those two things to show how many datasets are scheduled to refresh or are still running at a specific time so I can intelligently select an optimal refresh time for a new solution.
      6. There has to be a way to view all failed refreshes that have run today so I can proactively resolve issues.
      7. There has to be a way to review lineage and see all solutions aligned to a specific data source in the case there is a production issue.

      I have built a product that does these things and more!

      Take a look at the product and see if it would be valuable for you or your organization. It is available on vizbp.com:

      Power BI Workspace Metrics

      While there, take a look at other solutions people like me are making available for free or for purchase. Take a look at becoming a seller yourself and offering your solutions in the marketplace.

      https://vizbp.com/sell/

      While here, take a look at my other articles related to vizbp.com:

      Continue Reading
      Power BI Model Analyzer @vizbp

      How to analyze your Power BI data model size

      Whether you want to analyze an individual solution or your full Power BI environment (all workspaces and solutions you are an admin of), you will find the Power BI Model Analyzer solution invaluable. YOU DO NOT HAVE TO BE AN ADMIN with this product – just an admin of the Premium workspaces you want to analyze!

      Premium is required currently until XMLA connectivity is opened up to non-premium workspaces.

      There are other solutions available for this functionality but most are only available for single models (I will reference these at the end of the post).

      What do you do if you want to document or analyze all of your solutions? What if you want to review solutions you did not author but are published in your environment? You would have to develop a solution like this from scratch or analyze each of the solutions one at a time. You still can – don’t get me wrong. The value in this product just gives you a huge head start on that development and costs much less than the hours it would take to do so. It even has some capabilities that the other solutions do not.

        It is a fully functional data model – joining all Power BI models and DMV queries to be able to see data model size, table size, column size, measure DAX expressions, object dependencies, relationships, compression, and encoding. It also has all of the metrics and visuals built out to easily analyze your Power BI environment.

        If you want to connect to a single model, there are a lot of options (I will list those at the bottom of the post). I even built a version that would find the open pbix port via R and query that single model. This works great during development but not when wanting to analyze your full environment.

        It utilizes the Power BI API to query your Power BI environment for all workspaces of which you are an admin and the underlying datasets. It then queries each one utilizing DMV functions to extract the dataset components (tables, columns, measures, relationships and dependencies, compression metadata, encoding metadata, etc.).

        If you want to connect to the Power BI API and build this solution yourself, you very well can. You just need to create an Azure Active Directory Application and download some freely available connectors and/or utilize various methods to skirt around the OAuth2 authentication protocol (I will include links below) and you can begin connecting to your Power BI environment. In order to build reporting, you have to do some hi-jinks and jump through some hurdles (and be pretty good at M) in order to build out a functioning model.

        You also have to know DMV queries and how to join them all together:

        But, believe me, it takes dozens to hundreds of hours to build out a solution like this – not even mentioning the documentation that is required to make a product presentation ready for sales.

        If you have the time, I encourage you to go for it. It is an experience building something like this that is so valuable to an organization or to the individuals like me. I want my Power BI solutions and environment (Pro or Premium) to be fully optimized. I thought:

        1. There has to be a way to see all of my solutions I have access to.
        2. There has to be a way to easily filter to find all solutions by owner ( in this case, the person who imported/created the dataset).
        3. There has to be a way to see all data model sizes in a single view.
        4. There has to be a way to proactively monitor solutions published to watch for solutions not utilizing best practices or over a specified size.

        I have built a product that does these things and more!

        Take a look at the product and see if it would be valuable for you or your organization. It is available on vizbp.com:

        Power BI Model Analyzer

        While there, take a look at other solutions people like me are making available for free or for purchase. Take a look at becoming a seller yourself and offering your solutions in the marketplace.

        https://vizbp.com/sell/

        While here, take a look at my other articles related to vizbp.com:

        References to other Power BI model analyzer solutions (my apologies if I did not reference yours – let me know if I failed to and I will add yours as well):

        Continue Reading

        Copy Power Queries &/or Folders Between Power BI PBIX Files

        I am not sure how I missed the ability to copy Power Queries and/or Folders between PBIX files in Power BI.

        Most users are aware of the template capabilities of Power BI – you can create a Power BI model, export the model as a template, and use the template for new solutions.  In some scenarios, that template has too many queries, sources, etc. and you have to remove quite a bit to get a usable model.  If you have been in a situation where you only want a subset of the queries or have a single query that you reuse again and again in multiple models, this capability is golden.

        You simply open the model you would like to copy a query or multiple queries (either individually or from a folder), open another model (existing or new), and paste the query(s) in to the new model……simple.

        Copy Power Queries

        After notifying my coworkers of the ability, one of my coworkers pointed out that if you copy and paste a single query, the tool is smart enough to copy all related queries (i.e. joins, merges, appended, etc.) along with the original query copied.  As well, if a query already exists in the new model with the same name,

        Hope this helps improve your data modeling experience.

        Continue Reading

        Power BI Bookmarks

        Power BI bookmarks add so much valuable functionality to the application.  They allow so many capabilities that were missing prior.  Some are true workarounds but some are on par with other BI tools.  Take MicroStrategy and their use of panes – historically difficult to work with – with Bookmarks and the Selection Pane, it is very easy to duplicate this capability in Power BI.  This allows you to change visuals dynamically – or make it look like you are dynamically changing the axis by using bookmarks, the selection pane, buttons/tabs, and either multiple visuals or drill down levels to change the axis.

        I have curated a decent list of wonderful blog postings on use cases for bookmarks.  Please visit them, thank them, and give these authors credit.  I hope you find these as valuable as I have.

         

        Use Cases for Bookmarks

        http://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

        Excellent writeup to show useful use cases including dynamically changing the axis, changing colors/types of charts, etc.

        Build a Collapsible Slicer Pane for Power BI

        Build a collapsible slicer pane to show/hide filters w/ sample pbix file.

        https://www.wiseowl.co.uk/blog/s2585/main_menu_bookmarks.htm

        Four examples of use cases for bookmarks (clearing filters, tabbed navigation, expand/collapse menu, page navigation).

        https://www.blue-granite.com/blog/create-an-app-like-experience-in-power-bi-with-bookmarks

        Using bookmarks to create an app-like experience in Power BI.

        Power BI Feature Spotlight: Using Bookmark Buttons to Create Pop-Up Windows

        Using bookmarks to create pop-up windows.

        https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmarks-and-buttons/

        Dynamically changing a chart axis using bookmarks and buttons

        Power BI Feature Spotlight: Bookmarks

        Switching the visible visual shown based on bookmark selection.

        http://microsoft-bitools.blogspot.com/2018/01/power-bi-bookmarking-feature-update.html

        Another writeup on dynamically changing visuals using toggles.

        https://www.youtube.com/watch?v=OcxTUNlyIVw

        Four use-cases for using bookmarks (pagination, clear filters/slicers, switch visuals, and hyperlinks) w/ pbix sample

        https://www.youtube.com/watch?v=_Afcj8mT5_Q

        Excellent overview from Guy In a Cube with pbix sample

        https://www.blue-granite.com/blog/using-data-driven-images-for-navigating-power-bi-bookmarks

        Using html, data-driven images and bookmarks to dynamically change visuals

        How to use Bookmarks in Power BI for Seamless Report Navigation

        Using bookmarks for pagination/navigation

        http://radacad.com/clear-all-slicers-in-power-bi-a-bookmark-story

        Using bookmarks to clear filters – no pbix

        https://www.wiseowl.co.uk/blog/s2585/clear_slicers.htm

        Good walkthrough on using bookmarks to clear filters – no pbix.

        https://www.youtube.com/watch?v=j-KP1G_c8PQ

        Another good walkthrough on clearing filters (video).

         

        Continue Reading

        Blogging Update

        Where do people find the time to blog?  I am envious and appreciative at the same time.  Blogging takes time, initiative, dedication, and a desire to share.

        Starting a website for your consulting practice is fairly standard.  Blogging allows you to show your knowledge about the topic but also to bring potential clients to your site, build a reputation and credibility, etc.

        When I started blogging, I had high hopes (too high).  I wanted to write about topics that were overlooked or provide more detail on topics that were skimmed over, etc.  During that time, the continued growth of Power BI as an industry leader has made the community grow by leaps and bounds.  With that growth, the number of high quality blogs have also grown.  I find it hard to find topics that are original and don’t want to add one more blog post on the same topic being discussed by a dozen others.  Don’t get me wrong, again, I am appreciative of the other blog posts and the multiple different points of view on the same topic definitely adds color and value.

        To that end, I want to help curate those topics on my site and will point readers to those other high quality articles as I come across them.  I will add any blogging topics I come across from quality sources that I think are valuable to me and should therefore be valuable to the community as well.

        I will add value where I can by posting on any topics I see that can use another perspective or may just be a fun topic.

        This community is very giving and I want to give back as well.  I am working on a few other projects in that vein and am looking forward to promote those as well

        Continue Reading

        Using a Card as a Slicer in Power BI

        Using a card as a slicer in Power BI is a common question I receive.  The quick answer is it is not possible – I hear this much too often from others.  However, that is a very rare statement I would make about Power BI.  It is a rare statement that I use with anything data related.  I feel like so much more is possible if you think outside of the box.  Too many people stop when they cannot find the answer they were looking for.

        I will add a quick posting to show a couple of options on how to work around this “issue”.

        • A customized bar or column chart

        Pro(s): standard chart, looks like a standard card

        Con(s): the bar would only look transparent if your background is a solid color, requires re-sizing for larger data labels

        Customization: add the KPI, add the attribute you want to slice by on the axis, use visual filters if necessary, turn off x and y axis, turn bar color to the same color as your background color, turn on data labels with placement centered inside of bar

        You can’t even tell the difference between the original KPI visual and the bar chart!

         

        • A donut chart overlaying a KPI visual

        Pro(s): standard chart, looks clean

        Con(s): have to click on the donut, cannot click inside of the donut, requires proper sizing of the overlay for largest number expected in the KPI

        • Custom visual ‘Ring Chart’

        Pro(s): easy, looks like donut chart option above but should be cleaner for variable measure lengths, etc.

        Con(s): non-standard chart

        Continue Reading

        Power BI Dashboard Filters

        Power BI Dashboard Filters sometimes cause confusion.

        In one of my many communication channels, there was a question posed related to the ability to add Power BI dashboard filters in order to filter Power BI dashboard tiles.  There is some confusion at times and I would like to make a short article to clear some of them up if possible.

        1 – Tiles pinned to a dashboard are displayed based on the filters applied to them via the filter pane OR via slicers OR via cross filtering at the time of pinning.

        For example, I have a report that is currently unfiltered.

        I pin the top combo chart to my dashboard unfiltered.

        I cross-filter for reseller sales, pin the resulting visual to my dashboard, cross-filter for internet sales, and pin the resulting visual to my dashboard.

        The dashboard will show the tiles as they were at the time of pinning….i.e. one visual unfiltered, one filtered for reseller sales, and one for internet sales.

         

        2 – Use dynamic calendar filters for dynamic dashboards

        Create calculated columns on your calendar table that flag your required date periods – i.e. ‘Current Day’, ‘Current Month’, ‘Current Quarter’, ‘Current Year’, etc. or a Period selection table and measures that utilize the period selection (i.e. http://www.bipatterns.com/select-time-period-with-slicers-and-dax/).  Filter the dashboard utilizing the calculated columns or the Period selector and pin those visuals to your dashboard.

         

        3 – Dashboards do not currently have filters that will allow filtering across tiles

        This makes sense as you are able to have tiles from different reports that do not share filters.

         

        4 – If you would like the ability to filter specific tiles on a dashboard, pin live report pages

        However, design the report page specifically for a dashboard tile.

        For example, create one visual w/ on main slicer:

        Pin this visual to your dashboard and you will have the ability to change the filter on that specific dashboard tile.

        and I can now filter the tile from the dashboard w/o going to the report:

        I hope this helps clear up some confusion or assists you in a project.

        Continue Reading

        Power BI Training Resources (Free)

        Power BI Training

        Power BI Training?  My most common request, by far, is for training resources for Power BI.  There have been some posts I have used to acquire more/better/different options for learning Power BI but I have accumulated, filtered out, and singled out the best resources below.  I will add/modify periodically as new resources are added and if any of these sources become stale.

         

        Power BI Guided Learning

        http://bit.ly/2dcMhRc

        “Start your learning journey through Power BI with this sequenced collection of courses, and understand the extensive and powerful capabilities of Power BI.”  Great Power BI training resource – always kept up to date.

         

        Microsoft YouTube Training Curriculum

        http://bit.ly/2es4nnb

        Full Microsoft provided training curriculum delivered in 100+ short duration YouTube videos.

        Microsoft will point you to these videos when asking for training resources.  They cover all of the different aspects of connecting to data, shaping data, calculated columns, measures, time intelligence, hierarchies, working w/ different visualizations, R, Power BI Services, Q&A, Quick Insights, mobile, dashboards, distribution/sharing, gateways, etc.

        My go to Power BI training resource I send to those that ask.

         

        edX Course – Analyzing and Visualizing Data with Power BI

        http://bit.ly/2dJEez9

        In this data science course, you will learn from the Power BI product team at Microsoft with a series of short, lecture-based videos, complete with demos, quizzes, and hands-on labs. You’ll walk through Power BI, end to end, starting from how to connect to and import your data, author reports using Power BI Desktop, and publish those reports to the Power BI service. Plus, learn to create dashboards and share with business users—on the web and on mobile devices.

        Good Power BI training resource for classroom type learning.

         

        udemy Course – Learn Power BI Basics for Free

        http://bit.ly/2dwiX8Z

        In this tutorial you will be learning all basic knowledge required for Power BI and understanding Power BI component.

        7 Lectures – 2 Hours – Beginner Level

        Good Power BI training resource for classroom type learning.

         

        Microsoft Virtual Academy: Power BI Jump Start

        http://bit.ly/2d7Arws

        A bit dated and I always worry about sending this one as this class shows the Power tools in Excel (Power Query, Power Pivot, Power View, Power Map) and calls them “Power BI”.  This typically causes too much confusion.  As long as you understand that this was the first iteration of Microsoft BI Power tools that were merged and improved into Power BI, it is still beneficial.

        Not really a Power BI training resource specifically, but the Excel Power tools utilize the same functionality.

         

        Microsoft Virtual Academy: Data Visualizations with Power BI in Excel 2013

        http://bit.ly/2dWFduq

        Same comments as above.

         

        eBook – Introducing Microsoft Power BI

        http://bit.ly/2dLaj8x

        “Introducing Microsoft Power BI is a book that covers the basics of the tool and, at the same time, shows you what the main capabilities of Power BI are. The first chapters are similar to a step-by-step guide to start using the service, but the goal is also to show you all available features and the business scenarios covered by them.”

        Good Power BI training resource for those that enjoy learning from a book type resource.

         

        Power Pivot and Power BI Reference Card

        http://bit.ly/2dwvmtI

        Great 8-page reference card from an excellent resource of Power Pivot, DAX, and Power BI knowledge…..mainly addressing best-practices and understanding of DAX.

        Good Power BI training reference.

         

        Webinars – SQLBI

        http://bit.ly/2dcH8Zg

        Videos and webinars from two DAX masters:  Alberto Ferrari and Marco Russo.

        Good Power BI training resource for visual type learning.

         

        Webinars – Microsoft

        http://bit.ly/2egiYCZ

        List of upcoming and on-demand webinars covering introductory training, design concepts, content-packs, and feature-specific concepts from Microsoft and community members.

        Good Power BI training resource for classroom type learning.

        Continue Reading