Callum

Callum Green's Blog

Sunburst Charts in SQL Server Reporting Services 2016

The Sunburst is the second of the new chart types in Reporting Services 2016.  It was made available by Microsoft in CTP 2.3.  The aim of this blog is to describe what a Sunburst chart does and provide a guide on how to create one.

What are Sunburst Charts?

Like Tree Maps, Sunburst charts are an ideal candidate to display hierarchical data.  They work particularly well with ragged hierarchies and clearly indicates where relationships exist.  A ragged hierarchy is when some levels do not automatically roll up to its parent, e.g. an organizational hierarchy.  The CEO may have two managers – one who has a subordinate under them (COO)and the other who is not responsible for anyone (Secretary).

clip_image002

This is how a ragged hierarchy looks in a Sunburst Chart:

clip_image004

Source: Koen Verbaeck (www.mssqltips.com)

The Sunburst is just like a Donut chart but with multiple layers.  You can see there is an extra ‘ray’ coming out from Feb, as this is the only month that contains weekly data.  The hierarchy may in fact contain a weekly leaf member throughout but because Feb is the only month with values at the week level, none of the other members are visible.

 The whole purpose of this chart type is to help a user visualise how an outer ring (child member) contributes to an inner ring (parent member).  If you have a hierarchy that does not aggregate naturally, I would advise using other SSRS visualisations. 

Creating a Sunburst Chart

I will now show you how to create a simple Sunburst chart in SSRS 2016.  The functionality is available through bothReport Builder and Visual Studio.  For this demo, I will use Report Builder.

1.      Navigate to Report Builder through your configured SSRS web service and choose Blank Report.

2.      Set up your own relevant Data Source, dimension Datasets and report Parameters.  I suggest using a simple fact table and dimension from AdventureWorksDW2016.  A copy of this database can be found here.

3.      Select Insert > Chart > Insert Chart from the Menu Pane.

 

 

clip_image005 

4.      Select the Sunburst chart and click OK. 

clip_image006

5.      Create the Sunburst Dataset, which is derived from the fact table.  The SQL for my chart is as follows:

SELECT

             DC.DateKey,

             CalendarDate,

             'Week ' + CAST(CalendarWeekNumber AS VARCHAR(2)),

             CalendarMonthName,

             CalendarQuarterName,

             CalendarYearName,

             COUNT(*) AS No_Of_Games

FROM          [Warehouse].[DimDateCalendar] DC

INNER JOIN

             [Warehouse].[FactResults] FR

             ON  FR.DateKey = DC.DateKey

INNER JOIN

             Warehouse.DimLeague DL

             ON DL.LeagueKey = FR.LeagueKey

WHERE         DL.LeagueName = 'Premier League'

GROUP BY

             DC.DateKey,

             CalendarDate,

             'Week ' + CAST(CalendarWeekNumber AS VARCHAR(2)),

             CalendarMonthName,

             CalendarQuarterName,

             CalendarYearName

 

The query is bringing back the number of Premier League football matches played on a given day.  It only contains data from 2010 to 2015.

 

image 

6.      Now let’s bring in just one level of the hierarchy and a Sum of the No_Of_Games.

clip_image008

 

7.      If we preview the chart, it still looks very incomplete. 

clip_image002[6]

8.      If we add more Category Groups, it will add additional rings to the outside of the chart.  Eventually building up a number of layers. 

clip_image012

9.      The chart now looks like a Sunburst but it is very difficult to make sense of what is going on.  Therefore, we will add some colours, labels and groupings to the data.

10.   Right Click on the ‘No_Of_Games’ Value property and ensure Show Data Labels is ticked.


clip_image013 

11.   The Sunburst can be partitioned (by colour) depending on the desired attributes.  For simplicity, we will use ‘CalendarYearName’. 

clip_image015



12.   Add any additional chart headers, logos, etc to your report and Save the report.  Click Run to view it.


clip_image016 

13.   That is it - the report is now ready.  You can clearly see a pattern in the data and each year represents a colour. In addition, it is easy to see what proportion of quarters, months and weeks make up a given year.

Conclusion

The Sunburst chart type is a very visual chart type and not to be used to interrogate numbers at a granular level.  It is far better to spot anomalies within a hierarchy or highlight any bias towards a certain segment.

One major drawback I found was that it is very difficult to view or report on accurate information, especially when the data becomes more granular.  This is the age old issues with Pie Charts, which is why the BI community frown upon this chart type.  In the example above, the most outer ring is very busy and virtually impossible to analyse.  I would definitely advise using Tree Maps for more detailed analysis. 

There are also a couple of known limitations in CTP 2.3 for Sunburst charts.  Tooltips only show in the outer ring when an expression is defined and you cannot control the different slices.  On the other hand, Pie Charts enable a user to set a PieStartAngle, as well as a variety of other property options.  I am sure a lot of the missing functionality will be brought in – either in newer CTP releases or when the SQL Server 2016 is fully available.

For more interactive and aesthetically pleasing Sunburst charts, I would advise using either Power BI or D3 components (for web developers).  Not only is it more configurable but you can offer some really cool features like removing ‘ray’s, drill through and chart zoom.  A pre-configured D3 example can be found here.  I had a quick look through the SunburstChart Properties in SSRS 2016 and cannot see such options.

References/Future Reading

For more information on Tree Maps, I recommend the below resources/blogs:

·        Adventure Works DW 2016 CTP 3.0 database download - https://www.microsoft.com/en-us/download/details.aspx?id=49502

·        Koen Verbaeck - https://www.mssqltips.com/sqlservertip/4030/how-to-create-a-sunburst-graph-in-sql-server-reporting-services-2016/

·        Sorna Kumars Muthuraj (MVP) Web Blog  - https://www.youtube.com/watch?v=LKSecJ4_lzE

·        D3 Chart Type Gallery - https://github.com/mbostock/d3/wiki/Gallery

New Features in SQL Server 2016 – Part 2: Analysis Services

This blog solely focuses on the new Analysis Services features of SQL Server 2016 CTP2.  For anyone who may missed it – click here to view my opening blog on the Database Engine.

Although there have not been any major Analysis Services (SSAS) enhancements in CTP2, it is pleasing Microsoft are still looking to improve this part of the BI Stack.  The majority of them seem to be geared towards Tabular and DAX, although there are plans to release further Multidimensional functionality in CTP2 and beyond.

There are five key enhancements for SSAS:

1.      Tabular Model Partitions (Tabular).

2.      Many 2 Many (Tabular).

3.      Analysis Services PowerPivot mode.

4.      New DAX Functions (Tabular).

5.      Enable/disable attribute hierarchies.

Tabular Model Partitions
SQL Server SSAS 2016 CTP2 includes new parallel processing functionality for tables with two or more partitions, increasing processing performance. There are no configuration settings for this.  More information on this feature is displayed below:

1.      IN SSAS Tabular, partitioning big data marts will help drastically when processing new data.  Instead of having to process all data, you can just partition the relevant ones.

2.      In order to create, manage, and process partitions in SQL Server Management Studio, you must have the appropriate Analysis Services permissions defined in a security role.

3.      Parallel processing is also available in SSAS.  This happens automatically, when processing a table with more than one partitions.  Although you can choose to independently process a partition if required.

Many to Many Dimensions (Tabular)

There isn’t too much to show or say here yet apart from the fact that this feature is FINALLY HERE!  Gone are the complications of workarounds (thanks to Marco Russo and Alberto Ferrari), we can now just use the Many to Many functionality in Tabular.  Multidimensional already has this feature, which is one of the reasons why developers have yet to buy in to Tabular. 

Unfortunately, Microsoft have yet to reveal much detail on to how this feature will work.  I just hope it performs as well as the current workarounds.

Analysis Services PowerPivot mode

To install PowerPivot for SharePoint is now a lot easier.  If you already know how to configure SharePoint 2013, this will help you greatly. 

Taken from the MSDN Microsoft website, you simply use the Install Wizard and do the following:

1.      Select ‘Analysis Services’ from the standard Instance Feature.

2.      Choose ‘Analysis Services server mode’ and configure administrators (screenshot below).

clip_image002[4]

 

New DAX Functions

There are many new DAX functions, which are all detailed on MSDN website – click here for further detail.

I have picked out 5 functions that will be highly beneficial when creating an SSAS Tabular cube.  I am looking at this form a very BI focused background so I would recommend looking at all of the new functions to see if they benefit your needs.

1.      TOPn

a.      This is not actually a new function but has been updated.

b.     Now allows TRUE/FALSE/ASC/DESC to specify sorting direction.

2.      CALENDAR

a.      Returns a table with a single column named “Date” that contains a common set of dates. The range of dates is from the specified start date to the specified end date.

b.      See Similar – CALENDARAUTO

3.      MEDIAN

a.      Very simple – returns the median numbers in a column.

b.      See Similar – MEDIANX (uses an expression).

4.      NATURALINNERJOIN

a.      Inner join of a table with another table. The tables are joined on common columns in the two tables. If the two tables have no common column names, an error is returned.

b.      See similar – NATURALLEFTOUTERJOIN

5.      SUMMARIZECOLUMNS

a.      Enables you to group data together and return a summary table.

Enable/Disable Attribute Hierarchies

Again, this functionality is tailored towards SSAS Tabular.  There is a new setting that ties back to the Tabular mode metadata

1.      ColumnUsage property

a.      DAXUsage

                                                    i.     Attribute hierarchies can only be used in measures.

b.     UnrestrictedUsage

                                                    i.     Can be set in the XMLA (in SSDT) or by using an ALTER statement in the Tabular model.

                                                   ii.     An example taken from MSDN:

       <Alter>

  <ObjectDefinition...>

    <Database>

      <Dimensions>

        <Dimension>

          <Attributes>

            <Attribute>

               <ddl500:ColumnUsage value=”ddl500_500”>

               DAXUsage | UnrestrictedUsage

               </ddl500:ColumnUsage>

 

Multidimensional Features

The full SQL Server 2016 release will include some key enhancement to Multidimensional SSAS.  These include:

1.      Netezza as a Data Source (Netezza Data Warehouse | IBM - NDM Technologies).

2.      General performance improvements.

3.      Unnatural hierarchies.

4.      Distinct counts.

5.      DBCC support. Checks the logical and physical integrity of objects in the specified database.

6.      Expose on-premises multidimensional cubes in the cloud with Power BI.

I will provide further information on the improvements, as and when Microsoft announce them.

References

For more information on all of the new SSAS SQL Server 2016 features, the below resources/blogs are highly recommended.

·        Official Microsoft Page -
https://msdn.microsoft.com/en-us/library/bb522628(v=sql.130).aspx

·        Brew your own Business Intelligence Blog –

http://byobi.com/blog/2015/05/ssas-related-enhancements-in-sql-server-2016/

·        Jorg Klein SQL Server Blog -
http://sqlblog.com/blogs/jorg_klein/archive/2015/05/22/bi-on-your-terms-with-sql-server-2016.aspx