Tsql Analytic functions in SqlServer2012

Tsql in sql server 2012 has  number of  new analytic functions which can be used for various reporting needs importantly  for different window frames and offsets

Some of the functions which are introduced  are

• Lag
• Lead
• First_Value
• Last_Value

Lag:  Lag  enables  to find the data from previous row without using the selfjoin  

An example of lag function using the AdventureWorksDW2012  database  ,i have created a CTE  with  the aggregate at sales territory  productcategory and CaleanderYear   and i'm using the following query  to demonstrate the use of lag as shown below

Lag function accepts two arguements ,the first arguement is the offset,  in this case i have used 1 for previous year and a default arguement to assign a default value for the row prior to the start of our window

Basically  the above query   uses  lag  function to find the previous year sales along with each calendar year   Sales and it is partitioned by sales territory and ordered by calendar year ,

The result of the query looks like below

Lead is a similar function which avoids a self join to give a leading value for the period of interest

Implementing Dashboard with Performance Point Services, SSAS and SSRS

Organizations  always make Strategies  to monitor  goals  and to implement those  Strategies  they need actionable metrics which is often delivered  by decision support systems such as datawarehouse or data marts along with olap  provides the  necessary  data   with required  analytic  attributes and measures  at appropriate granularity to use in  dashboards and reports .These dashboards and reports  empower business users with important visualizations to get a glance of the performance of the business  and help them in detecting patterns of problems or opportunities

SharePoint Business Intelligence with Performance Point Services and SSRS  enables to build Dash Boards ,Score Cards and  Structured reporting for business performance management  .

The key features of PPS  are

• Enables to  build the score cards to analyse the goals and trends from multiple processes
• Provides intelligent interactivity  with multiple individual parts in a  dashbaords by using filters to enable contextual selection of the data .
• Decomposition tree guides   self analysis across dimensions with flexibility for drilling into the details of metrics in question.

Implementation of the DashBoard .



I have SharePoint site from previous demo with Enterprise  BI template  which provides with necessary  document libraries for Business intelligence.




The first step is to create is to  go to the  business intelligence  center  site  ,create dash boards section   and click start  using performance point services  as shown below

In the next step click the run dashboards designer as shown below

In the dashboard designer i have created a ssas connection to use the KPI's defined in the ssas,the connection settings is shown below

In the next step  , set the time intelligence settings as shown below

This setting consists of mapping to the time  Date dimension of the Cube  and sets the appropriate associates of the time hierarchies .


In the next step  right click the performance point document library in the dashboard designer and select kpi and blank kpi as shown below

On the New KPI page select data  and than  fixed  value

Than click change source

Than select the ssas data connection

Than for the KPI i will use an MDX expression as shown below to select the KPI Value from SSAS Net Income KPI as shown below

Repeat the same step by KPI Goal and  use the MDX expression KPIGoal("Net Income") .

In the calculation section of the main kpi page select  click default  and select data value  as shown below

Repeat the same steps for bringing other KPI's



In the next step right click the performance point service content library and select new score card
and import the previously created Kpi as shown below

In the step i will create reports using PPS and SSRS.To create  analytic  chart  in PPS right click the Performance Point Services  Document Library  and select new analytic report  as  shown below

In the analytic  chart report page select the necessary fields as shown below ,for this report i have selected Country as Bottom Axis and Product Category for Series and Sales Amount and Calendar Year as Background ,I'm using Calendar Year as i will be using it to filter the report from the dashboard filter.

To add the  prebuilt SSRS report right click the Document Library and select the ssrs report and set the  server and reports Properties as shown below

The SSRS Report is consists of  a bar chart which shows the Product Category sales for each sales Channel and consists of two parameters for Calendar Year and Product Category,make sure to give a default value and delete the hidden datasets created   as shown below

In the next step right click the document library to create a filter  and follow the steps below

                                                                                                    

                                                                                                          

In the next step right click the document library and create a new dash boards  as shown below

          

The dash boards has three rows ,in the first row add the filter ,in the second row add the score  card and the analytic chart and in the third row add the ssrs report .

Configuring the Filters and contextual selection ,i have a dashboard filter for Calendar year which need to be set to ssrs  for the slect the SSRS row and go to edit  and click creaet connection as shown below

In the Items selection select the source  of filter in this case its our dashboard filter Year   as shown below

In the values  selection select the members unique property  for the Calendar Year  of  the report

In a similar way we want to filter the report based on the selection of the score card Product Category ,for this we create another contextual connection  and set the properties of the connection as shown below

Than we deploy the dashboard  by going to office ribbon and click deploy  as shown below

 and the deployed dashboard looks like below 

Developing Dashboard in SSRS from multiple Stars

Developing  dashboards and reports across multiple process   can be tricky in some cases,in this post i will develop a dashboard in SSRS which uses the data from multiple stars to show the sales performance against the goals.

The  key to the solution is the concept of dimensional conformance  which allows for drilling across different processes  .In order to drill across the basic requirements are the structure and the content  of the dimensions should be same .

For this demo i'm using Adventureworksdw2012  database and the dimensions i will be using are DimDate,DimSalesTerritory and a fact FactResellersales. I have created  another star for Sales Goals to show the actual sales against goals.This star contains the same dimensions  but the granularity of the salesgoals facts for Date Dimension is year and for Sales Territory is Country which doesn't affect the dimensional conformance as i will be aggregating the both stars at Year and Country level .Also for fact Reseller sales when you aggregate the dataset at the country and Year some of the data for sales will be missing as there are no sales in that particular regions for that period so i have densified the data so that the sales value will be 0 for  countries, which haven't got sales for that period.

The  data set  for Actual Sales  aggregated at the country and Year looks like below

The Data  for the SalesGoal looks Like below

Now I have all the datasets for my demo,In the next few steps i will be showing how to implement this solution in SSRS.

The Key to the solution in SSRS is using Lookup to join  on multiple fields across two different datasets  in the reporting region.As we know lookup only takes two fields for joining ,you need to add some more logic  to join on multiple columns .There is a great article of Devin Knight   on his Blog for the same concept .


I have created to shared DataSources and  Datasets for the two Stars  as shown below

Than  i have used those data sets for the Report  ,in those individual data sets i have created a calculated field which the look  to join two different datasets .

The first step is to create a calculated field,for that right click the dataset , go to the fields section and add calculated field  as shown below

In the Calculated field i have created an expression which allows for multi join on year and country fields as shown below

I have created a similar calculated  expression on the other dataset as shown below

Now i have the pre requirements ready ,In the next steps i have  added a new report and tablix  on to the data region and  added   fields for  Country, Actual Sales  from dataset1  for ADW as explained previously, and added  an expression for Sales Goal for which i have given the description how to build it  in the next section  and  also  added an  indicator  for SalesTrend as shown below

In the Sales Goal field , i have used an expression to lookup the sales goal  which is on the second dataset2 based on the calculated  fields, which  i have created earlier .

The final report  looks like below

Data Visualization in SSRS

 Data is becoming an important asset  for organizations  and data visualization is gaining immense prominence in inferring knowledge  from that data   in an easy way.Imagine you have gathered tera or peta bytes of data and the  business users cant explore the information for the  patterns and connections  in an easy timely manner,  than the effort you  make in creating  those  data stores will be of less useful to the business.

Using  Data Visualization   effectively with dash Boards and business score cards are an important aspect in gaining the strategic and and analytic  insights  of the business  at a glance.Therefore effective use of the space ,colors and the visuals play an important role in conveying the message to the users.

The key factors  when building the reports  or dashboards  are

• Use the space effectively in dashboards to include key insights   
• Use the right visualization  for observing different trends
• Make navigation easy to users and should be effective in helping the users in reaching to the data of interest
• Use the parameters as and when needed and don't make it mandatory to include parameters in every report or dashboards
• Keep  the reports simple without borders or lines  and emphasize data effectively
• Use light colors for the report  column headers

SqlServer Reporting Services  has included many new capabilities for building reports and introduced various data visualization futures starting  from sqlserver2008r2   Reporting Services.

I have built a report using Adventureworks DW 2012 

The key things in the report is the effective use of space colors and visuals,as you can see the sales trend over time is shown using  a sparkline which  shows the trend over calendar years in a single column which is a very good use of space  and i have included a indicator to show the status of sales and a pie chart to show the sales contribution to the total sales and also the report doesn't include any lines and borders in between the rows and columns.

Unit Testing using SqlServer Data Tools (SSDT)

Sql server Data Tools (SSDT) is one of  the great new product from Microsoft    for database developers  which  brings the power of visual studio's  project based development    to  database development .

Some Key Features of the SqlServer Data  Tools are

• Model Based declarative  Development   and validation 
• Offline development using  lighter weight  Localdb  which comes with SSDT and doesn't require a separate instance  of database to be installed
• Uses  Power Buffer for  Declarative  Development ,Power buffers    hold a model of the database by   which you  can make changes to the  table structures and alter the programming constructs without affecting the actual database until we  are ready to commit the changes 
• Refactoring support  for  database objects which enables  to rename ,move objects  and preview all the effected  dependent changes 
• Snapshots allow to hold the point in time copy of the database project 
• SchemaCompare gives the ability to compare the local  database project with the target  database for schema changes
• Easy to create sandbox dev,test  environments  by cloning the  production database for schema and data 
• Server Explorer in SSDT resembles object explorer of   SSMS 
• Drift detection in SSDT  enables to keep update with  any new changes to the database after we create the database project in visual studio
• SSDT can  be used for database unit testing  when you have visual studio2010/2012 professinal or higher edition
• Can be used  to target sql server 2005/2008 and SqlAzure

Unit Testing using SqlServer Data Tools SSDT Step by Step.

For this demo i'm creating a schema with one table and a stored procedure ,the table name is called Product and the stored procedure NewProducts inserts records into the table   as shown below

In the next step  i  will create a new database project in SSDT  by slecting the file menu and project as shown below

In the next step i have selected the SqlServer Database Project template as shown below

Than i  have imported the schema by going to  Project Menu  than to  Import and selected  the SQLScipt

On the import Sql wizard  click next

In the next step  you need to select the location of the sqlfile

Click next and finish .After importing the sql script go to Projects and check the newly created tables and  stored procedure in the database of the projects node.

Press F5 which deploys the database and creates a Localdb   under the databases section of localdb\projects as shown below

In the next step  i  will create  a unit test for the stored procedure NewProduct

Right click the StoredProcedure  and select create  unitests as shown below

I have selected the necessary stored procedures for unit testing in this case i have only one called  NewProducts  as shown below a

I need to configure the  the test configuration  wizard which follows after the previous step or you can  right click the testproject created  and select the SQLServer test configuration

 Under database connections give the connection details for your localdb  as shown below

In the next step under the sqlserver test configuration wizard  select the check box under deployment under the database project section select the project which we created earlier  and under deployment configuration select the debug which is shown on the earlier wizard

In the next  step i have   amended  the  test logic which was created by earlier step    by giving the variable for prodcut name as testproduct  as shown below

Than under test condition delete the  inconclusive condition

After deleting the test condition than ,select row count from the dropdown list

 The new test condition as shown below

Open the properties for the test condition and  change the  row count property to 1 as shown below

Than go to the  test menu  windows and  select test explorer as shown below

                                                                                                      

                                                                                                              

Than run the tests as shown below

As you can see the test runs successfully and that concludes the demo.