Friday, July 27, 2012

CONCAT() IN SQL SERVER 2012

SQL Server 2012 introduces a new string function CONCAT() which allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values. Good thing about the function is that it takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This function would help eliminate the need of explicit data conversions when concatenating two values.

 Note: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned.


SYNTAX:

SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN])
-- Between 2 and 254 values can be passed.

EXAMPLE:

DECLARE @FirstName NVARCHAR(10) = N'हरि'
       ,@LastNameVARCHAR(10) = 'Sharma'
       ,@Cell VARCHAR(20) = '+1 (425) XXX-YYYY'
       ,@Age INT = 30
       ,@Delimiter VARCHAR(2) = SPACE(2)
SELECT CONCAT(@FirstName, @Delimiter, @LastName, @Delimiter, @Cell, @Delimiter, @Age)


OUTPUT:
 


You might have observed that there’s no data conversion being performed in the above example.

The data returned by CONCAT function depends on the type of valued passed. Below are some of the mapping input and output types:
 


Input Value / Type
Output Value / Type
SQL-CLR (TYPES & UDT)
NVARCHAR(MAX)
NVARCHAR(MAX)
NVARCHAR(MAX)
NVARCHAR(<=4000)
NVARCHAR(<=4000)
VARBINARY(MAX)
NVARCHAR(MAX)
ALL OTHER INPUT TYPES
VARCHAR(<=8000) *Unless one of the parameters is an NVARCHAR of any length and in that case, the output value would be in NVARCHAR(MAX)

NOTE: CONCAT function only works with SQL Server 2012 and later versions.


Saturday, December 10, 2011

Creating Degenerated dimension

Degenerate Dimensions
In a data warehouse, a degenerate dimension is a dimension which is derived from the fact table and doesn't have its own dimension table. Degenerate dimensions are often used when a fact table's grain represents transactional level data and one wishes to maintain system specific identifiers such as order numbers, invoice numbers and the like without forcing their inclusion in their own dimension. The decision to use degenerate dimensions is often based on the desire to provide a direct reference back to a transactional system without the overhead of maintaining a separate dimension table.

Once a cube is created, we can add degenerate dimension to the cube.
Below are the steps to create Degenerate Dimension:

1. Click on Add Cube Dimension icon in Dimension Usage tab as shown below. It will open Add Cube Dimension dialogue box.
2. Click on New dimension… button in Add Cube Dimension dialogue box.















3. It will open Dimension Wizard. Click on Next to continue…
4. Select required data source view and click on Next to continue…

















5. Select Standard dimension option and click on Next to continue…
6. Select Main Dimension table and Key columns. Click on Next to continue…  
7. Select Dimension Attributes. Click on Next to continue…  
















8. Click on Next to continue…  
















9. Now we are at Completing the Wizard. Give proper dimension name click Finish to complete.

 















10. Newly created dimension is now available in Dimension Usage tab. At the intersection of Fact Sales measure group and the Sales Order Number dimension, click the ellipsis button (…) in the Item Description cell to review the fact relationship properties.   












The Define Relationship dialog box opens. Notice that you cannot configure any of the properties. The Selected relationship type should be Fact.

The following image shows the fact relationship properties in the Define Relationship dialog box




Tuesday, November 1, 2011

Key Performance Indicators (KPIs) using Analysis Services

In business terminology, a KPI is a quantifiable measurement for gauging business success. In Analysis Services, a KPI is a collection of calculations that are associated with a measure group in a cube that are used to evaluate business success. Typically, these calculations are a combination of Multidimensional Expressions (MDX) expressions or calculated members. KPIs also have additional metadata that provides information about how client applications should display the results of the KPI's calculations

Common KPI Terms:
  • Goal - An MDX numeric expression or a calculation that returns the target value of the KPI.
  • Value - An MDX numeric expression that returns the actual value of the KPI.
  • Status - An MDX expression that represents the state of the KPI at a specified point in time. The status MDX expression should return a normalized value between -1 and 1. Values equal to or less than -1 will be interpreted as "bad" or "low." A value of zero (0) is interpreted as "acceptable" or "medium." Values equal to or greater than 1 will be interpreted as "good" or "high."
  • Trend - An MDX expression that evaluates the value of the KPI over time. The trend can be any time-based criterion that is useful in a specific business context. The trend MDX expression enables a business user to determine whether the KPI is improving over time or degrading over time.
  • Status indicator - A visual element that provides a quick indication of the status for a KPI. 
  • Trend indicator - A visual element that provides a quick indication of the trend for a KPI. 
  • Display folder - The folder in which the KPI will appear when a user is browsing the cube.
  • Parent KPI - A reference to an existing KPI that uses the value of the child KPI as part of computation of the parent KPI. Sometimes, a single KPI will be a computation that consists of the values for other KPIs.
  • Weight - An MDX numeric expression that assigns a relative importance to a KPI. If the KPI is assigned to a parent KPI, the weight is used to proportionally adjust the results of the child KPI value when calculating the value of the parent KPI.
Creating KPIs using Business Intelligence Development Studio (BIDS)
  1. Click on the KPIs tab
  2. We shall first add a KPI named ProfitPercentage to the cube:
    • Click on the New KPI button or right-click on the KPI Organizer window and select New KPI.
    • In the KPI Designer which opens up, type in the name of the KPI as ProfitPercentage.
  3. Add the measure to the Sales Fact measure group by selecting from the Associated measure group drop down.
  4. Now let's add an expression to evaluate the value property of the KPI:
    • Type the following expression in the Value Expression box. You could also drag the measures from the Metadata tab in the Calculation tools window.
      [Measures].[Profit]/[Measures].[Product Cost]
      OR
      ([Measures].[Sales Amount]/[Measures].[Product Cost]-1
      We have built the expression above using the logic that ProfitPercentage is calculated as the difference in Sales and Cost, divided by the Cost.
      Here [Measures].[Profit] is Calculated Member using below calculation:
      CREATE MEMBER CURRENTCUBE.[MEASURES].[Profit]

      AS SUM
      (
      [Date].[Calendar Year - Qtr - Month].currentmember,
      [Measures].[Sales Amount]-[Measures].[Product Cost]
      ),
      FORMAT_STRING = "#,#",
      VISIBLE = 1;
  5. Now, let us set the goal of the KPI to a constant. Enter 0.25 in the Goal Expression box, which means the goal expects 25% more in sales than the cost.
  6. Next we are on to the all important status. Here we shall write an expression to compare the value with the goal: 
    • Before examining the actual expression let us take a look at a pseudo-expression:
      CASE
         WHEN value/goal >= 0.9 THEN 1
         WHEN value/goal < 0.8 THEN -1
         ELSE 0
      END

      Here, a ratio of .9 would be considered good in this instance. This means that if the value makes up at least 90% of the goal then it would be considered as successful, hence the value 1 to indicate it. If value/goal equals to less than 0.8 then the status will be considered as a failure (-1). And all those values do not fall into the above ranges will be considered neutral (0).

      Now, enter the following MDX expression into the Status expression box:
      CASE
         WHEN
      KPIValue("ProfitPercentage")/KPIGoal("ProfitPercentage")>=.9 THEN 1
         WHEN KPIValue("ProfitPercentage")/KPIGoal("ProfitPercentage")< .8 THEN -1
         ELSE
      0
      END


      Being the realization of the previous "pseudo-expression", the above expression replaces value with KPIValue("ProfitPercentage") and goal with KPIGoal("ProfitPercentage"). The KPIValue function returns the value of the KPI's value property, while the KPIGoal function returns the value of the KPI's goal property. Both functions take the name of the KPI through a character string parameter.
  7. Select an appropriate image type from the Status indicator drop down to provide for the visuals of the KPI status.
  8. The next property to be configured is the trend. Once again, let us first look at a "pseudo-expression":
    CASE
       WHEN IsEmpty(x) THEN 0
       WHEN v - vX >= 0.25 THEN 1
       WHEN v - vX < -0.1 THEN -1
       ELSE 0
    END

    Here, x is the current time period of the previous year. For instance if the current selected time period is 20-May-2008, x will be 20-May-2007. v is the value of the KPI, which by default would return the value for the current time period. vx is the value of the KPI for the same time period of the previous year. The expression first checks if x is empty, thereby resulting in 0 (or no change in trend). Then, it checks if the current period's value is an increase of at least 25%, thereby indicating 1 or a positive trend. If there's a decrease of more than 10% from last year it indicates a negative trend or -1. All other results will indicate no change in the trend (or 0).

    Enter the following MDX expression in the Trend expression textbox:
    CASE
    WHEN
    IsEmpty(ParallelPeriod([Date].[Calendar Year - Qtr – Month].[Calendar Year], 1,[Date].[Calendar Year - Qtr – Month].CurrentMember)) THEN 0
    WHEN KPIValue("ProfitPercentage")-(KPIValue("ProfitPercentage"), ParallelPeriod([Date].[Calendar Year - Qtr - Month].[Calendar Year],1,[Date].[Calendar Year - Qtr - Month].CurrentMember)) >= 0.25
    THEN 1
    WHEN KPIValue("ProfitPercentage") - KPIValue("ProfitPercentage"),
    ParallelPeriod([Date].[Calendar Year - Qtr - Month].[Calendar
    Year],1,[Date].[Calendar Year - Qtr - Month].CurrentMember)) < -0.1 THEN -1
    ELSE 0
    END

    In the MDX expression above, x is replaced by ParallelPeriod([Date].[Calendar Year - Qtr - Month].[Calendar Year], 1,[Date].[Calendar Year - Qtr - Month].CurrentMember) ParallelPeriod is a function which returns a member from a prior period in the same relative position as a specified member. The variable v is replaced by KPIValue("ProfitPercentage") and vx is replaced by (KPIValue("ProfitPercentage"), ParallelPeriod([Date].[Calendar Year - Qtr - Month].[Calendar Year], 1,[Date].[Calendar Year - Qtr - Month].CurrentMember)).
  9. Select an appropriate image type from the Trend indicator drop down to provide for the visual of the KPI trend. Here's a sample of what the KPI designer should look like:


  10. The KPIs is done! Next, process the cube. You will be able to view the KPIs using the built-in KPI Browser under the KPIs tab in BIDS. You could also use Office 2007 Excel.