Articles

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 Wide World Importers Demo

This blog will start off with a word (or a lot of words) of advice for job seekers looking for a new Power BI development position.

Bring Power BI examples.

DO NOT come empty-handed and explain that you cannot show your prior work because it was confidential data.

There are plenty of free data sources that you can get your hands on, show that you have initiative, and show that you fine tune your craft outside of the assigned projects you have worked on.

I am not going to list all of the free data sources available for building sample data models and visuals. There are plenty of resources out there if you search for them. I will include a few links to sites that aggregate those free data sources below:

But…..

DO NOT provide samples with confidential data that you created at a prior employer. This is a huge red flag that you are not concerned about privacy, protecting data, etc.

DO NOT steal and present someone else’s solution as your own.

DO NOT quickly build a model without implementing best practices.

DO take the time to ensure the data model is clean, you have parameterized your data sources (or at least referenced them on other queries, you are implementing query folding where possible, used friendly/descriptive naming on your Power Query steps, use query grouping for organizational purposes, add documentation in code as necessary, denormalized as necessary to move towards a star schema and away from snowflake, etc.

DO take the time to show you pay attention to details. Align visuals, evenly distribute them, use friendly titles on visuals, rename visuals (in the selection panel is easier) and while there, go ahead and group them nicely (make sure you are using modern headers to do so), make sure visual interactions are optimal, make sure column spacing is wide enough to accommodate totals, use conditional formatting where appropriate, etc.

DO take the time to update your old models to utilize new features and/or visuals; showcase them in your solutions. This shows that you keep up with new features and functionality.

Veering away from that topic now…..I am providing a quick sample of a Power BI model (pbix) built on a portion of the Wide World Importers data warehouse provided by Microsoft: https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

Feel free to use it, put your own spin on the visuals, and provide it to perspective companies as an example.

There are other fairly standard models here for use as well: https://docs.microsoft.com/en-us/power-bi/sample-datasets#the-power-bi-samples-as-pbix-files

They could definitely use an overhaul on the front end but that is why they are perfect for this scenario! You can show the original and then show your upgraded (hopefully) version to detail improvement.

What would be the best example to give? Find a poorly modeled model, implement best practices, and show the before and after – along with all of the noted improvements and best practices implemented. Reduce the granularity from 10 million to 2 million records? Reduce the size of the model by 50%? Increase the speed of a DAX measure by 30%? Add metadata definitions to the model? Ensure query folding was occurring where possible? Note it all and what the benefit of doing so is.

PROVIDE POTENTIAL EMPLOYERS EXAMPLES THAT SHOW YOU HAVE AN UNDERSTANDING OF THE TECHNOLOGY!

Continue Reading

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

    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

      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.

        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