Trellis Charts

Creating Trellis Charts to Make SSRS Reports More Readable

Problem

Factual data is generally analyzed from a lot of different dimensions. Because of this Grid based reporting is not an ideal reporting format to support analytical needs. Visual data representation like charts, graphs, and gauges are one of the best ways to report data that require comparison analysis. The challenge with this form of data representation is that every visualization has a modest limit to contain data points, after which the visual representation becomes uninterruptable to the human eye. Therefore the need is to have a scalable information visualization design that can report quantitative multi-dimensional data while still preserving its analytical value.

Solution

The more attributes you try to contain within a chart/graph, the more data points there are in a single chart which distorts the scale as well as size of the data points in the graph. So the solution is to separate different attributes / groups on a different axis and add only required data points into the visualization. This might sound completely confusing without an example, so let's try to understand the problem as well as the solution with an example.

Step 1:

Create a dataset as shown in the below screenshot. This dataset is of Sales, having Year, Continent, Country and City as its attributes. There are 18 records in this dataset.

Step 2:

Suppose we want to contain this data in a bar chart. Without grouping the data there would be 18 bars on the graph, and if you have the most granular level attribute to report in the chart i.e. City in this dataset, you would always end up with 18 bars on the chart. Generally a dataset like this in real life would be much larger. Try to analyze the data from the below representation.

Step 3:

Reporting data using small graphical representation in pivotal form is known at lattice charts and a trellis chart is a form of this. We will report from our dataset in this form. To report this data and make it analyzable, with all the related attributes, we need to separate the attribute being reported on the chart and limit the chart to represent only actual data i.e. Sales and most granular attribute i.e. City.

Create a new SSRS report. Add a matrix control to the report, then add grouping by Year on the rows axis and grouping by Continent -> County on the columns axis. Add a chart on the Data area and configure it to contain Sales and City fields on Values and Category area respectively. After this is done, your configuration should look exactly like the below screenshot.

Step 4:

Execute and preview the report and you should find the output as shown in the below screenshot. If you analyze carefully, the data is analyzable using any attribute with every possible combination. This technique of information visualization design is very scalable. Even if more attributes get added like Product Category, Orders etc, the same can be put across the desired axis and the report would still remain analyzable. If that same volume of data was represented on a single bar chart, the reported data would become completely unintelligible.

How to Show the Vertical Axis in terms of Lakhs in BAR CHART,SSRS Report

 I have the created the bar chart report using SSRS. Here i can able to display the each columns in terms of lakhs and expression is shown below.

="Rs "& (round(Fields!value.Value/100000,2))&" Lakh".

Put the expression =round(Fields!value.Value/100000,2)) in series properties under value filed.

The other way is create a calculated filed let’s say Calc_Value in your dataset and use this expression=round(Fields!value.Value/100000,2))

Now use this calculated field Calc_Value everywhere as per your requirement.

How to use SSRS reports as Data Source in Excel

How to use SSRS reports as Data Source in Excel

As we all know SSRS is a server-based reporting platform that allows to create and manage a wide variety of reports, and deliver them in a range of formats, but many of them are still addicted to the excel for reporting as it is more convenient and easy to use.

In this post, let us learn how to use SSRS report as Data Source for Excel Reporting.
First of all, we need to enable Data Analysis in Excel and then continue importing data from SSRS into Excel.

Go to "Data" tab and Click "manage Data Model".

On the pop-up click "Enable" to enable the data analysis add-ins.

In the Power Pivot for excel pop-up, click on "Get External Data" and choose "From Other Sources".

Choose "Report" in the "Connect to a Data Source" pop up.

Next, Choose the Report Path which you want to use as the source.

In the Name box, type in the ReportServer URL and choose "Open".

Then Navigate to the rdl file.

Choose the rdl file and click "Open".

Once the rdl file is chosen, you can preview the report and click next.

Then choose the data table/view under the report and click "Finish".

Click Close on the successful completion of the import.

This is now import the data into the excel for further reporting.

Now go back to the excel and choose "Pivot Table" from the "Insert" tab.

And choose the Report connection that we previously imported.

Then choose where to create the new Pivot table.

Now you can create your own reports out of this data using the excel which is more convenient for you.

How to: Display Data Point Labels Outside a Pie Chart

In Reporting Services, pie chart labeling is optimized to display labels on only several slices of data. Labels may overlap if the pie chart contains too many points. One solution is to display the labels outside the pie chart, which may create more room for longer data labels. If you find that your labels still overlap, you can create more space for them by enabling 3D. This reduces the diameter of the pie chart, creating more space around the chart.

To display data point labels inside a pie chart

1. Add a pie chart to your report. For more information, see How to: Add a Chart to a Report.
2. On the design surface, right-click on the chart and select Show Data Labels.

To display data point labels outside a pie chart

1. Create a pie chart and display the data labels.
2. Open the Properties pane.
3. On the design surface, click on the pie itself to display the Category properties in the Properties pane.
4. Expand the CustomAttributes node. A list of attributes for the pie chart is displayed.
5. Set the PieLabelStyle property to Outside.
6. Set the PieLineColor property to Black. The PieLineColor property defines callout lines for each data point label.

To prevent overlapping labels displayed outside a pie chart

1. Create a pie chart with external labels.
2. On the design surface, right-click outside the pie chart but inside the chart borders and select Chart Area Properties. The Chart AreaProperties dialog box appears.
3. On the 3D Options tab, select Enable 3D.
4. If you want the chart to have more room for labels but still appear two-dimensional, set the Rotation and Inclination properties to0.

Custom view

creating custom view using Power Query for a report

1.Copy n paste Dr-Ageing Excel n Rename it as Interest Calculation -- 
to get the custom view option in the report.
2.Copy this excel name in Template Field in the settings(Report).
Now Open the Excel -- Power Query -- Workbook Queries -- Data -- Edit -- View -- Advanced Editor.
3. Write the below Query n Save -- Run
  let
   SpParameter = Excel.CurrentWorkbook(){[Name = "Parameter"]}[Content],
   Source = Sql.Database("192.9.200.195", "ICSoftReportTemp", [Query="SELECT  C.CustName + ' | ' + C.CustCode as Customer,[ASOn] ,Ageslab AS Ageing,amount,AgeId AS ID ,IntAmount
FROM [DR-Ageing_ForIntCalc] (NOLOCK)
inner JOIN IcSoft.dbo.Customer C (NOLOCK) on C.CustId = [DR-Ageing_ForIntCalc].custid
where LoginId = " & Number.ToText(SpParameter[LoginID]{0})])
in
   Source

Create First OLAP Cube in SQL Server Analysis Services

Introduction

This article is created to help those technical newbies who want to learn creation of OLAP Cube in SSAS (SQL Server Analysis Services) using Microsoft BIDS (Business Intelligence Development Studio) Environment and Data Warehouse. We will first take a glimpse at the basic introduction to requirement of OLAP Cube, and then create OLAP Cube in SQL Server Analysis Service by following 10 easy steps.
Before preparing OLAP Cube, we need to create and populate our data warehouse. If you are totally new to Data Warehouse concepts, please take a quick look at my previous article “Create First Data Warehouse” which can give you a brief idea on data warehouse concepts which are used in this article.

Brief Introduction to OLAP Cube

What is OLAP Cube & Why do we need it?

• An OLAP cube is a technology that stores data in an optimized way to provide a quick response to various types of complex queries by using dimensions and measures.
  Most cubes store pre-aggregates of the measures with its special storage structure to provide quick response to queries.
• SSRS Reports and Excel Power Pivot is used as front end for Reporting and data analysis with SSAS (SQL Server Analysis Services) OLAP Cube.
• SSAS (SQL Server Analysis Services) is Microsoft BI Tool for creating Online Analytical Processing and data mining functionality.
• BIDS (Business Intelligence Development Studio) provides environment for developing your OLAP Cube and Deploy on SQL Server.
• BIDS (Business Intelligence Development Studio) comes with Microsoft SQL Server 2005, 2008 (e.g. Developer, Enterprise Edition) .
• We have to choose OLAP Cube when performance is a key factor, the key decision makers of the company can ask for statistics from the data anytime from your huge database.
• We can perform various types of analysis on data stored in Cube, it is also possible to create data mining structure on this data which can be helpful in forecasting, prediction.

What is the difference between OLAP and OLTP?

Online Transaction Processing (OLTP)	Online Analytical Processing (OLAP)
Designed to support Daily DML Operations of your application	Designed to hold historical data for analyses and forecast business needs
Holds daily Latest Transactional Data related to your application	Data is consistent up to the last update that occurred in your Cube
Data stored in normalized format	Data stored in denormalized format
Databases size is usually around 100 MB to 100 GB	Databases size is usually around 100 GB to a few TB
Used by normal users	Used by users who are associated with the decision making process, e.g., Managers, CEO.
CPU, RAM, HDD space requirement is less.	CPU, RAM, HDD space requirement is higher.
Query response may be slower if the amount of data is very large, it can impact the reporting performance.	Query Response is quicker, management can do Trend analysis on their data easily and generate quicker reports.
T-SQL language used for query	MDX is used for querying on OLAP Cube

Basic Architecture

In our case, data warehouse is used as a source of data to Cube in BIDS. Once Cube gets ready with data, users can run queries on Cube created in SSAS. SSRS Reports and Excel Pivoting/Power Pivot can use OLAP Cube as source of data instead of OLTP database to get performance for resolving Complex Queries.
SSRS Reports, Excel Power Pivot can be used for visualization/analysis of data from cube.

Scenario

X-Mart is having different malls in our city, where daily sales take place for various products. Higher management is facing an issue while decision making due to non availability of integrated data they can’t do study on their data as per their requirement. So they asked us to design a system which can help them quickly in decision making and provide Return on Investment (ROI).
So as a part of the design, we had completed designing of First Data Warehouse in my previous article. Now we have to Design / Create OLAP Cube in SSAS, on which our reports can do a quick query and we can also provide self service BI capability to users later on.

Creating Data Warehouse

Let us execute our T-SQL Script to create data warehouse with fact tables, dimensions and populate them with appropriate test values.
Download T-SQL script attached with this article for creation of Sales Data Warehouse or download from this article “Create First Data Warehouse” and run it in your SQL Server.
Follow the given steps to run the query in SSMS (SQL Server Management Studio).

1. Open SQL Server Management Studio 2008
2. Connect Database Engine
3. Open New Query editor
4. Copy paste Scripts given below in various steps in new query editor window one by one
5. To run the given SQL Script, press F5
6. It will create and populate “Sales_DW” database on your SQL Server

Developing an OLAP Cube

For creation of OLAP Cube in Microsoft BIDS Environment, follow the 10 easy steps given below.

Step 1: Start BIDS Environment

Click on Start Menu -> Microsoft SQL Server 2008 R2 -> Click SQL Server Business Intelligence Development Studio.
 

Step 2: Start Analysis Services Project

Click File -> New -> Project ->Business Intelligence Projects ->select Analysis Services Project-> Assign Project Name -> Click OK

Step 3: Creating New Data Source

3.1 In Solution Explorer, Right click on Data Source -> Click New Data Source

3.2 Click on Next

3.3 Click on New Button

3.4 Creating New connection

1. Specify Your SQL Server Name where your Data Warehouse was created
2. Select Radio Button according to your SQL Server Authentication mode
3. Specify your Credentials using which you can connect to your SQL Server
4. Select database Sales_DW.
5. Click on Test Connection and verify for its success
6. Click OK.

3.5 Select Connection created in Data Connections-> Click Next

3.6 Select Option Inherit

3.7 Assign Data Source Name -> Click Finish

Step 4: Creating New Data Source View
4.1 In the Solution Explorer, Right Click on Data Source View -> Click on New Data Source View

4.2 Click Next

4.3 Select Relational Data Source we have created previously (Sales_DW)-> Click Next

4.4 First move your Fact Table to the right side to include in object list.

Select FactProductSales Table -> Click on Arrow Button to move the selected object to Right Pane.
4.5 Now to add dimensions which are related to your Fact Table, follow the given steps:
Select Fact Table in Right Pane (Fact product Sales) -> Click On Add Related Tables

4.6 It will add all associated dimensions to your Fact table as per relationship specified in your SQL DW (Sales_DW).
Click Next.

4.7 Assign Name (SalesDW DSV)-> Click Finish

4.8 Now Data Source View is ready to use.

Step 5: Creating New Cube

5.1 In Solution Explorer -> Right Click on Cube-> Click New Cube

5.2 Click Next

5.3 Select Option Use existing Tables -> Click Next

5.4 Select Fact Table Name from Measure Group Tables (FactProductSales) -> Click Next

5.5 Choose Measures from the List which you want to place in your Cube --> Click Next

5.6 Select All Dimensions here which are associated with your Fact Table-> Click Next

5.7 Assign Cube Name (SalesAnalyticalCube) -> Click Finish

5.8 Now your Cube is ready, you can see the newly created cube and dimensions added in your solution explorer.

Step 6: Dimension Modification

In Solution Explorer, double click on dimension Dim Product -> Drag and Drop Product Name from Table in Data Source View and Add in Attribute Pane at left side.

Step 7: Creating Attribute Hierarchy In Date Dimension

Double click On Dim Date dimension -> Drag and Drop Fields from Table shown in Data Source View to Attributes-> Drag and Drop attributes from leftmost pane of attributes to middle pane of Hierarchy.
Drag fields in sequence from Attributes to Hierarchy window (Year, Quarter Name, Month Name, Week of the Month, Full Date UK),

Step 8: Deploy the Cube

8.1 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Properties

8.2 Set Deployment Properties First
In Configuration Properties, Select Deployment-> Assign Your SQL Server Instance Name Where Analysis Services Is Installed (mubin-pc\fairy) (Machine Name\Instance Name) -> Choose Deployment Mode Deploy Allas of now ->Select Processing Option Do Not Process -> Click OK

8.3 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Deploy

8.4 Once Deployment will finish, you can see the message Deployment Completed in deployment Properties.

Step 9: Process the Cube

9.1 In Solution Explorer, right click on Project Name (SalesDataAnalysis) -- > Click Process

9.2 Click on Run button to process the Cube

9.3 Once processing is complete, you can see Status as Process Succeeded -->Click Close to close both the open windows for processing one after the other.

Step 10: Browse the Cube for Analysis

10.1 In Solution Explorer, right click on Cube Name (SalesDataAnalysisCube) -- > Click Browse

10.2 Drag and drop measures in to Detail fields, & Drag and Drop Dimension Attributes in Row Field or Column fields.
Now to Browse Our Cube

1. Product Name Drag & Drop into Column
2. Full Date UK Drag & Drop into Row Field
3. FactProductSalesCount Drop this measure in Detail area