Category Archives: BI

What is Power BI

Posted on by

Last week I delivered an introductory presentation about Power BI covering all the features of it in the SQL Server Universe user group meeting. I got a fair amount of time to showcase the features of Power BI to the audience.

I concluded my presentation giving the following definition to Power BI from Microsoft’s point of view or what Microsoft has been trying to do.

 

Productizing self service BI tools and services coupled with the company’s cloud based organizational strategy

Personally I think Microsoft is determined to have a product in the self service BI market, they’ve been trying to bring all the tools and services under one umbrella named Power BI.

It works perfectly well, it’s a very impressive product to deliver a showcase. I have delivered product introductory presentations for Azure and Windows 8 in other user group meetings. Compared to them Power BI is a small product which draws the interest of few people, but it didn’t fail to impress the audience, so thought of writing about Power BI.

Power BI has components categorized under 3 topics.

Excel Features

  • Power Query
  • Power Pivot
  • Power View
  • Power Map

Power BI for Office 365

  • Power BI Sites
  • Power BI Q&A
  • Query and Data Management
  • Power BI Windows App

IT Infrastructure service for Power BI

  • Provision Power BI for Office 365
  • Power BI Admin Center
  • Data Management Gateway

Excel features are available for download for free. You can download them from this URL

Detailed blog posts about each component will follow in the coming days.

Advertisement

Charting with DotNet HighCharts

Posted on by

This post explains how to use the DotNet HighCharts wrapper for the HighCharts Javascript library. You can download the wrapper from Codeplex.

First create an ASP,NET file and add the following Javascript references.

   1: <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
   1:  




   2: <script src="http://code.highcharts.com/highcharts.js">




   1: </script>




   2: <script src="http://code.highcharts.com/modules/exporting.js">


</script>

Download the wrapper and add the single .dll reference to the project and add the following using statements to the code.

   1: using DotNet.Highcharts;

   2: using DotNet.Highcharts.Options;

   3: using DotNet.Highcharts.Helpers;

   4: using DotNet.Highcharts.Enums;

The wrapper provides pure C# methods for generating the charts and has a method ToHtmlString() which returns the JavaScript version of the chart. This can be set to a ASP.NET Literal control for rendering.

Then here’s a sample for creating a column chart with two series.

   1: DotNet.Highcharts.Highcharts chart = new DotNet.Highcharts.Highcharts("chart").SetXAxis(new XAxis

   2:             {

   3:                 Categories = sales.GetSales().Select(list => list.ProductName).ToArray<string>()

   4:             })

   5:             .SetSeries(new Series []

   6:             {

   7:                 new Series()

   8:                 {

   9:                     Type = ChartTypes.Column,

  10:                     Data = new Data(new object [] {125,824,122}),

  11:                     Name = "Sales"

  12:                 },

  13:                 new Series()

  14:                 {

  15:                     Type = ChartTypes.Column,

  16:                     Data = new Data(new object [] { 57,234,67 }),

  17:                     Name = "Freight"

  18:                 }

  19:             });

‘sales’ is the object data source, and we get the product name from it and the values are hard coded. All charts has a name, here the column chart’s name is ‘chart’ itself. Names should be unique for the page. If you have another chart with same in the same page only the last chart will be displayed.

Then chart has SetXAxis method where set the X-Axis properties.  And the chart has number of series. We feed the data to the series through its Data property which takes an object array.

Finally we serialize the chart as HTML content to the Literal control (ltrChart)

Example of a pie chart generation.

   1: DotNet.Highcharts.Highcharts piechart = new DotNet.Highcharts.Highcharts("pie").SetXAxis(new XAxis

   2:             {

   3:                 Categories = sales.GetSales().Select(list => list.ProductName).ToArray<string>()

   4:             })

   5:             .SetSeries(new Series[]

   6:             {

   7:                 new Series()

   8:                 {

   9:                     Type = ChartTypes.Pie,

  10:                     Data = new Data(new object [] {125,824,122}),

  11:                     Name = "Sales"

  12:                 }

  13:             });

  14:  

  15:             piechartLiteral.Text = piechart.ToHtmlString();

MDX (Children and Members)

Posted on by

MDX queries are complex compared to the SQL or TSQL queries. It is perceived as complex because the underlying data source is not two dimensional. But simple MDX expressions are not very complex, 😀 as that is why they are simple.

 

Members and the Children properties of the MDX provide different set of outputs for the hierarchies.

 

SELECT [Measures].[Internet Sales Amount] ON columns,
[Product].[Category].Children on rows
from [Adventure Works]
where [Date].[Calendar Year].&[2003]

 

As you see below, when you specify the Children the it excludes the ‘All’ member of the hierarchy.

image

 

select [Measures].[Internet Sales Amount] on columns,
[Product].[Category].Members on rows
from [Adventure Works]
where [Date].[Calendar Year].&[2003]

‘Members’ brings you the results with the ‘All’.

 

image