Announcing Sparkline Visual

A sparkline is a miniature graph, typically drawn without axes or coordinates. Commonly used on dashboards, sparklines typically visualize trends over time, such as to show profit over the past several years. Although other Microsoft reporting tools, such as Excel and Reporting Services include sparkline elements, Power BI doesn’t have one. That is until now.

I contributed a “classic” sparkline custom visual to the Power BI Visuals Gallery. Once you import and add the sparkline to a report, you bind the sparkline to data using the Data tab of the Visualization pane. For example, you can aggregate a Freight field added the Value area over the CalendarQuarterDesc field added to the Category area. The resulting graph will sho how freight fluctuates over time. You can use any field to group the data, not just a field from the Date table.

The sparkline supports several formatting options. The General section lets you change the line color and width. The default properties are “steelblue” as a color and one pixel for the line width. The Animation section lets you turn on an animation effect that draws the line gradually from left to right (who says that Power BI visuals have be to static?) Although in general I advise against animations and other visual distractors in real-life reports, I wanted to emphasize the point that Power BI visuals can support anything clients-side JavaScript can do. The Duration setting controls how fast the line draws (the default setting is 1,000 milliseconds) and the Delay settings controls the interval between redraws (the default is 3,000 milliseconds). The sparkline supports also a tooltip. When you hover anywhere on the viewport, a tooltip pops up that shows the name of the field added to the Value area and the data point values.

The main limitation of the current implementation is that similar to the Power BI gauge visualization, it doesn’t render over multiple lines, such as for each product category on rows. Preferably, at some point Power BI would support a repeater control similar to the SSRS Tablix region. This would allow nesting the sparkline into other visualizations, such as a table, that will repeat the sparkline for each row. As Power BI stands now, the only way to implement this feature is to draw the visual for each value of the category field, which would require also drawing labels for the row categories. However, it doesn’t repeat on rows, the sparkline could be used to display multiple measures arranged either horizontally or vertically as the screenshot shows. Another limitation related to the previous I’ve just discussed is that it supports only a single field in the Category area and a single field in the Value area. In other words, the sparkline is limited to one measure and one group. Continuing on the list of limitations, the tooltip displays the same information irrespective where you hover on the sparkline viewport as opposed to showing just the value of the data point behind the cursor.

I hope you’ll find my sparkline useful not only for your real-life projects but also for learning how to implement custom visuals.

New Tabular Schema in SQL Server 2016

Those of you who had to generate programmatically Tabular models or make changes to the schema would agree that it was more complicated that it should have been. That’s because Tabular borrowed the schema from Multidimensional so objects had to be defined in different places and in the SSAS MD terminology (dimensions, DSVs, etc). SQL Server 2016 CTP3 brings a new and much more simplified Json-based schema for describing SSAS Tabular models. To see it, you need to configure your workspace database in SQL Server 2016 (1200) compatibility mode. SSMS doesn’t yet support the new schema but you can take a look at Model.bim once you create your project. When you open the source of the Model.bim file you’ll see something completely different than the XML schema you were used to:


“name”: “SemanticModel”,

“id”: “SemanticModel”,

“compatibilityLevel”: 1200,

“readWriteMode”: “readWrite”,

“model”: {

“culture”: “en-US”,

“dataSources”: [


“name”: “DW”,

“connectionString”: “Provider=SQLNCLI11;Data Source=.;Initial Catalog=AdventureworksDW2016CTP3;Integrated Security=SSPI;Persist Security Info=false”,

“impersonationMode”: “impersonateServiceAccount”,

“annotations”: [


“name”: “ConnectionEditUISource”,

“value”: “SqlServer”



“name”: “PasswordRemoved”





“tables”: …

So faster Tabular schema operations in SQL 2016 and much easier model auto-generation. Now all we need is a programmatic way to work with the new schema and this should probably come soon.

Introducing Office Online Server

Continuing the line of thought from my previous blog “Microsoft Unveils BI Roadmap”, one nagging question still remains. What to do with all these Excel files now that SSRS came with a vengeance and has been promoted to THE on-premises BI platform? It looks like Microsoft is thinking along these lines. Interestingly, Excel Services will be removed from SharePoint Server 2016 and replaced with Office Online Server, as explained here. And this page gives more details about Office Online Server which is currently in preview. In a nutshell, Excel rendering would work pretty much in the same way as in SharePoint Server 2013. The most common scenario that Microsoft plans now is to have the Excel workbook in SharePoint. When you open the workbook, SharePoint will redirect you to the Office Online Server using a WOPI frame (some sort of an Iframe), with some special tokens that enable Office Online Server to retrieve the workbook from SharePoint, and then render it as usual. This will work for Power Pivot models as well. Office Online Server (OOS) also supports loading Excel files from OneDrive which is how Power BI Excel rendering works today.

But wouldn’t be nice to extend SSRS 2016 and integrate with OOS so that you can deploy Excel reports to a report server installed in native mode? This would allow SSRS to handle all of the important Microsoft report formats (SSRS, Datazen, Power BI Desktop, and Excel). If OOS doesn’t require a SharePoint license, this will be a great value proposition as well as you will save you a SharePoint Server Enterprise Edition license. This is precisely the wish I communicated to Microsoft and posted it on the Excel wish list. Please vote if you like the idea.

You know that there is an Excel wish list site, right?