Power BI : Custom Visual Development

Wherein our hapless hero attempts to understand the in and outs of developing Custom Visuals in Power BI…

Background

First, let me say that… the decision to open source the visuals for Power BI was such a great decision.  We have seen some very cool custom visuals.  Also, the ability to hit https://app.powerbi.com/devTools and just start typing out a new visual in a mostly javascript way is super approachable and vaguely fun.

Unfortunately, that’s kind of where the good news ends.  While there are some docs… they aren’t super complete… and you will end up slogging through code of the default visuals in hopes of understanding how to implement your custom visual.   And that code tends to be a bit fancier than you expect 🙂

Worst, imo, is an absurd message that https://app.powerbi.com/devTools is being deprecated.  This is a horrible decision… as it takes something super approachable, and turns it into a confusing mess of sadness and despair.  The deprecation notice also sends you to a page that isn’t the main repo and says a new framework will be here in a few weeks… and that was a month ago.

I had to install and re-install npm/gulp and some broken packages to eventually possibly maybe get something to work… after it downloaded (literally) 25000 files at 250mb.  Then the playground didn’t actually work. Super. Frustrating. And finding help isn’t exactly easy.  If you are tough, just start hitting   http://microsoft.github.io/PowerBI-visuals/index.html and https://github.com/Microsoft/PowerBI-visuals … I’m sure you will be fine 🙂

If I needed a custom visual, and I needed it done fast and high quality… I would reach out to http://okviz.com/ from our friends at sqlbi.  I don’t know that they offer custom visual creation, but I bet you could talk them into it 🙂

Hello, World!

I am going to boldly hope https://app.powerbi.com/devTools doesn’t’ go away for awhile… and use that to dig into how to create a custom visual.  My goal will be to use as few libraries as possible.  At this point, I am just not ready to learn about D3 (or WebGL, Canvas, SVG, etc)… it’s bad enough I have to learn TypeScript (a fancy version of Javascript).

There is really only 1 class you must implement… IVisual.  There are many of the methods on IVisual that are optional, but I believe this is what is actually required:

module powerbi.visuals {
    export class YourVisual implements IVisual {
        
        public static capabilities: VisualCapabilities = { };

        private hostContainer: JQuery;

        public init(options: VisualInitOptions): void {
            this.hostContainer = options.element;
            this.hostContainer.append("<h3>Hello, World!</h3>");
        }

        public update(options: VisualUpdateOptions) {
            var dataViews = options.dataViews;
            if (!dataViews) return;
        }
    }
}

Bam!  Best.  Visual.  EVER!!!

… and Beyond!

Okay, this doesn’t seem too bad.  We need to implement init(), update() and expose our capabilities.  The open question is “how do I know what the data is…” ?

You see those VisualUpdateOptions that are passed to update() ?  That’s your huckleberry.  What you need to do is go read this page… where you learn that the same data is cleverly exposed in many ways… as categorical, table,matrix,tree,single, etc.  Your visual can access the data via any of these formats that make sense for your visual – which is pretty cool.

So, I wanted to write a custom visual that dumps out the data in pretty raw text form… just to get a feel for how each of these data formats “work”.

module powerbi.visuals {
    export class YourVisual implements IVisual {        
        public static capabilities: VisualCapabilities = {}
        private hostContainer: JQuery;

        public init(options: VisualInitOptions): void {
            this.hostContainer = options.element;
        }

        public update(options: VisualUpdateOptions) {
            var dataViews = options.dataViews;
            if (!dataViews) return;
 
            // Only create our html once...    
            if (this.hostContainer.children().length > 0) return;
                                   
            // create all the html...
            this.displayMetadata(dataViews[0]);
            this.displaySingle(dataViews[0]);
            this.displayTables(dataViews[0]);
            this.displayCategoricals(dataViews[0]);
        }

        private displayMetadata(dataView: DataView): void {
            this.writeHtml("<b>Metadata</b>", true)

            var meta = dataView.metadata;

            for (var c = 0; c < meta.columns.length; c++) {
                var dn = meta.columns[c].displayName
                var gn = meta.columns[c].groupName
                this.writeHtml(dn + " (" + gn + ")  ")
            }

            this.writeHtml("<br/>")
        }

        private displaySingle(dataView: DataView): void {
            this.writeHtml("<b>Single</b><br/>")

            var single = dataView.single;
            if (!single || !single.value) return;

            this.writeHtml(single.value + "<br/>")
        }

        private displayTables(dataView: DataView): void {
            this.hostContainer.append("<b>Table</b><br/>")

            var table = dataView.table;
            if (!table) return;

            for (var c = 0; c < table.columns.length; c++) {
                var dn = table.columns[c].displayName
                var gn = table.columns[c].groupName
                this.writeHtml(dn + " (" + gn + ")  ")
            }
            this.writeHtml("<br/>")

            for (var r = 0; r < dataView.table.rows.length; r++) {
                var val = table.rows[r].join("  ");
                this.writeHtml(val + "<br/>")
            }
        }

        private displayCategoricals(dataView: DataView): void {
            this.hostContainer.append("<b>Categorical</b><br/>")

            var cat = dataView.categorical;
            if (!cat) return;

            if (cat.categories) {
                for (var i = 0; i < dataView.categorical.categories.length; i++) {
                    var c = cat.categories[i];

                    var dn = c.source.displayName
                    var gn = c.source.groupName
                    this.writeHtml(dn + " (" + gn + ") <br/>")
                    this.writeHtml(c.values.join(", ") + "<br/>");
                }
            }

            for (var i = 0; i < dataView.categorical.values.length; i++) {
                var val = cat.values[i]
                var dn = val.source.displayName
                var gn = val.source.groupName

                this.writeHtml(dn + " (" + gn + ") <br/>")
                this.writeHtml(val.values.join(", ") + "<br/>")
            }

            this.writeHtml("<br/>")
        }

        private writeHtml(s: string, newline?: boolean): void {
            this.hostContainer.append(s)
            if (newline) this.hostContainer.append("<br/>")
        }
    }
}

Now, I’m not saying this is a sane way to write your visual.  Sane people use D3 and a clean ViewModel and such.  But my goal has never been to be “sane”… I wrote this to understand how the interfaces work, not as some sort of “best practice”.  This might be a worst practice 🙂

But hey… it does display a bunch of data in the dev environment.  #winning

Does it Work In Power BI?

Spoiler:  No.  The reason is that we never filled out our capabilities. This is actually the part I understand the least…

        public static capabilities: VisualCapabilities =
        {
              dataRoles: [
                {
                    name: 'Category',
                    kind: powerbi.VisualDataRoleKind.Grouping,
                },
                {
                    name: 'Measure',
                    kind: powerbi.VisualDataRoleKind.Measure,
                },
            ],
            dataViewMappings: [{
                categorical: {
                    categories: {
                        for: { in: 'Category' },
                    },
                    values: {
                        select: [{ bind: { to: 'Measure' } }]
                    },
                },
            }]
        };       

And then, if I export to Power BI, it does work… like a darn Christmas Miracle!  You can see the “Category” and “Measure” fields available in the UI, as defined by my capabilities.  And when I added DeptName and a measure with the fantastic name “Metric”, my custom visual indeed shows the information in the Metadata, and also in both Table and Categorical form.  The Single form… isn’t appropriate so it doesn’t have anything.  However, if I removed DeptName, it would indeed be filled in a well.   The dataRoles of of the capabilities seems pretty understandable… the dataViewMappings… not so much.  Docs are light there, so good luck 🙂

Wrap up

Well, there you have it.  I have dipped my toe into custom visual development, and I didn’t die.   I can confidently say that if I wanted to create a custom visual that displayed simple tabular data in a nicely styled html table with custom css… I could totally do that!  So, that makes me fairly awesome.

However, I didn’t even touch what happens if you try to load a massive amount of data, or using D3 or reasonable Model / View separation or testing, etc.  Certainly doing a good job would require more understanding.

Here’s hoping that some of the folks creating brilliant visuals will start blogging about their experiences 🙂   <hint hint>.