In understanding how to access the data in a custom visual I found AB Kimmel’s tutorial incredibly helpful. Here I’m just going to go through the options I need, for a broader explanation I’d recommend reading his sections on data views. There are four steps to go through. First specify what data can be added to the visual via the Fields pane – this data will then be accessible via the dataview. Then create a data model to hold this data and a function to populate it from the dataview. And finally use the data model data to draw the visual. Continue reading “Power BI Custom Visual Part 4 – Accessing the DataView”
Power BI Custom Visual Part 3 – Moving D3 to Power BI
In this section I’m going to create a new power BI custom visual that will display the Tree Funnel chart I designed in Part 2. For now, it will just use the fixed test data. I won’t bind it to an actual Power BI data source till the next instalment. Continue reading “Power BI Custom Visual Part 3 – Moving D3 to Power BI”
Power BI Custom Visual Part 2A – SVG blend lines between elements
When drawing the paths for the Tree Funnel visual I noticed an unsightly line between each horizontal. Initially I thought this would be due to a small error in the stroke width introducing a gap but actually the calculation is perfect and each path is exactly adjacent. The lines are actually due to the anti-aliasing of each path. Continue reading “Power BI Custom Visual Part 2A – SVG blend lines between elements”
Power BI Custom Visual Part 2 – Creating a Tree Funnel Chart
Power BI Visuals basically render a bunch of HTML to display a chart. Typically these will be SVG elements and most likely use the D3 library to help draw them. So when I started on developing a Power BI Custom Visual it made sense to develop the chart rendering separately and worry about how to hook it into the Power BI Visual after I was happy with the basic chart logic. This section then is purely D3. Continue reading “Power BI Custom Visual Part 2 – Creating a Tree Funnel Chart”
Power BI Custom Visuals Part 1 – Introduction
I’d been interested in developing a Power BI custom visual for a while before actually getting round to producing something. The usual constraints on time were one reason but also the rather limited documentation and the number of components required which I have at best a surface knowledge of: Typescript and D3 in particular. Having finally found the time I found that the process was not too painful even starting from a pretty low base. The documentation however was sparse and so before detailing how I got on I thought I’d outline the resources I found useful. Continue reading “Power BI Custom Visuals Part 1 – Introduction”
Charticulator for Dummies
A graphical tool for developing complex chart designs in minutes that can be exported as custom visuals directly to Power BI. Too good to be true? Maybe, but Charticulator is still an amazing bit of kit and to be fair it’s primarily intended to produce charts for the Web not for Power BI.
Charticulator is developed by Microsoft Research which means some very smart people worked on it and though there are plenty of tutorial examples as a lesser mortal I struggled a bit to understand how everything worked. So I’m sharing a very basic example here which explains the issues that confused me. In a separate post I’ll talk about how the export to Power BI and whether it really works. Continue reading “Charticulator for Dummies”
Query Amazon Redshift from SQL Server
There is a connector for Amazon Redshift (a data warehouse product available on Amazon Webservices) in Power BI that works just fine including support for direct query. But what if you want to pull data from a Redshift database to SQL Server? I wanted this to add content from a third party data warehouse on AWS to an on premise institutional data warehouse. In this scenario there is an ODBC driver that you can use create a linked server to the external DB and pull the data straight out. Continue reading “Query Amazon Redshift from SQL Server”
Normalising Multivalued Fields in SQL (Salesforce Pick Lists)
Salesforce’s storage of picklist values is a bit of a compromise of good data modelling and something of a headache if you want to re-normalise the data elsewhere. Where a user can select more than one item in a pick list field on an object the selections are stored as a semi-colon separated list of items on the object record – so… Continue reading “Normalising Multivalued Fields in SQL (Salesforce Pick Lists)”
Where’s the Old Default Power BI Theme?
A small thing this but, at some point, the Power BI default theme was changed to an eyesore of a palette reminiscent of the awful highlighting sometimes used in Excel by finance teams. Aside from being a bit ugly I’ve used the previous default theme for all my historical reports and want to keep this style. The old default is still available as the Classic theme. Continue reading “Where’s the Old Default Power BI Theme?”
Generating an Integers Table
One of the first jobs I do when setting up any new database is to create a tool set of useful objects. And an integers table is normally the first of these – having a source of numbered rows is often handy for generating test data and to use in cross joins to add additional rows to a query. Generating a permanent table of values will save generating these on the fly whenever they are required. Continue reading “Generating an Integers Table”