Tuesday, May 27, 2014

Explore cube using microsoft excel

The cube we have created in SSAS in previous post, can be explore, drill down, pivot using microsoft excel.
Parallel excel shows the aggregated data and it also show the related graph for the same cube.

Below are the steps to connect to excel with ssas engine.

1. Open DATA> From Other Sources > From Analysis server
and you can see the below image.
- and the server name of sqlserver so that it can connect to the database


2. Next , now you can see the next page in the wizard. We have to select the database from drop down menu.

 3. Finsh the Wizard after selecting the database from drop down menu.

4. Below popup window now you can see, I select "PivotChart and PivotTableReport" means it will show us the data as well as the graphical chart for the report.


5. If you see the below image carefully, on very right hand side top, similar window you can see in excel and you can check the columns to add it into chart. Where i check the "sales Amount" , "Tax Amt" , "Profit" columns.

From column A i.e. Row Labels is a hierarchy column based on "product color".
Column E and F reflecting the KPI Goal and KPI status again i have check the value (using check box) from right hand side top window.



We can the dimension by check or uncheck the column and can observe the charts and we can track the organization goals.




 

Friday, May 23, 2014

Some Simple MDX queries

Multidimensional Expressions (MDX) lets you query multidimensional objects, such as cubes, and return multidimensional cellsets that contain the cube's data.


1. Simple MDX Query
select [Measures].[Sales Amount] on columns
,[Dim Product].[Colorkey].[Colorkey] on rows
from [Adventure Works DW]



2. Slicing /WHERE CLAUSE - To Slice Data from cube we can use Where clause , SALES OF HELMET
    select [Measures].[Sales Amount] on columns,
         [Dim Product].[Colorkey].[Colorkey] on rows
    from [Adventure Works DW]
        where [Dim Product].[Product Subcategory Key].&[31]


    OR We can USE "member" FUNCTION - In above query we use "[Dim Product].[Colorkey].[Colorkey]" in rows, where the third attribute in string represent the member of the color key. We can also omit the third attribute from string and we can use "member" instead of it.
    select [Measures].[Sales Amount] on columns,
        [Dim Product].[Colorkey].members on rows
    from [Adventure Works DW]
    where [Dim Product].[Product Subcategory Key].&[31]


3. Example from Snow flake schema:
    select [Measures].[Sales Amount] on columns,
        non empty  [Dim Product].[Hierarchy].[Product Category Key] on rows
    from [Adventure Works DW]


4. Apply Filtering on data using Filter function
    select [Measures].[Sales Amount] on columns
        --,[Dim Product].[Colorkey].[Colorkey] on rows
        ,filter([Dim Product].[Colorkey].[Colorkey].members,
        [Measures].[Sales Amount] >4000000) on rows
    from [Adventure Works DW]

5. Drill down  the cube dimension

    SELECT       {[Measures].[Sales Amount]} ON COLUMNS,
          {[Dim Product].[Product Category Key] } on rows
    FROM       [Adventure Works DW]

          --

    SELECT       {[Measures].[Sales Amount]} ON COLUMNS,
          DRILLDOWNLEVEL({[Dim Product].[Product Category Key] }) ON ROWS
    FROM      [Adventure Works DW]
         
          --
         
    SELECT       {[Measures].[Sales Amount]} ON COLUMNS,
          DRILLDOWNLEVEL({ [Dim Product].[Product Subcategory Key]}) ON ROWS
    FROM       [Adventure Works DW]

Tuesday, May 20, 2014

Create KPI using ssas (Sqlserver analysis services)

KPI is a quantifiable measurement for gauging business success. One of the most useful features of an Analysis Service cube is the ability to define key performance indicators (KPIs) within the cube that allow for a graphical representation of the current state of your business

1. create cube as below post
http://j4info.blogspot.in/2014/05/6-hierarchy-using-dimproduct-category.html

upto this our cube using existing datawarehouse columns, here we not have any goal set column which can gave us the value on which we can set KPI.
Now we create a "CALCULATED MEMBER"


When you create a KPI, you NEED one or more members in a measure group or dimension. However, in some cases, the existing members don’t support the type of KPI you want to create, at least not in their current form. If that’s the case, you can create a calculated member, which is similar to creating a computed column in a SQL Server database.

2. To create a calculated member, open your Analysis Services project in SQL Server Business Intelligence Development Studio (BIDS), and then open the cube in which you want to create your KPI.
In Cube Designer, click the "Calculations" tab, and then click the "New Calculated Member" button. A new calculation form opens in the right pane.



- You should first name the calculated member by typing the name in the Name text box. For this example, I use the following name:
 [Profit]
- Select measure group from "Parent hierarchy" drop down column.
- In "Expression" box set expression which will give us the profit percentage. Later this calculated member used for goal calculation.
  copy and paste below code in "Expression" box.
 ( [Measures].[Sales Amount] - [Measures].[Total Product Cost]) /
 [Measures].[Sales Amount]

- Process it.
- If you want to see the script for same, "script view" button.
That’s all there is to creating a calculated member. Be sure to save the project and then process the cube so the measure is available to your KPI. After you process your cube, you can verify that the measure has been successfully added by browsing the cube data and viewing the "Profit" measure.

3. Now we create KPI
- In cubeMove to the tab "KPI"
- Click on button "New KPI"
- Now if you see the window on right hand side
- Name: GIve any name for KPI
- Value Expression: An MDX expression that returns the KPIs actual value. A value expression is a physical measure such as Sales, a calculated measure such as Profit, or a     calculation that is defined within the KPI by using a Multidimensional Expressions (MDX) expression. Add below in "Value Exression" box:

  [Measures].[profit]
 
  Also, as with the calculated member, you can drag the name from the hierarchies listed in the lower-left pane to the expression text box.

- The goal expression: A goal expression is a value, or an MDX expression that resolves to a value, that defines the target for the measure that the value expression defines.   For example, a goal expression could be the amount by which the business managers of a company want to increase sales or profit.
       
    case
    when [Dim Product].[Product Category Key]
        is [Dim Product].[Product Category Key].&[1]
            then .40
    when [Dim Product].[Product Category Key]
        is [Dim Product].[Product Category Key].&[3]
            then .20
    when [Dim Product].[Product Category Key]
        is [Dim Product].[Product Category Key].&[4]
    then .10
else .15
end

   
  where [Dim Product].[Product Category Key] = [Dim Product].[Product Category Key].&[1] is for bike, i just pick and drop product category MEMBER from left pane to GoalExpression pane box.

   
- The status expression: A status expression is uses to evaluate the current status of the value expression compared to the goal expression. A goal expression is a normalized  value in the range of -1 to +1, where -1 is very bad, and +1 is very good.
  The status expression displays a graphic to help you easily determine the status of the value expression compared to the goal expression.

  i use trafic light signal from here, You can select any other status signal type from drop down menu. Use below code in Status Expression box.
 
  case when  kpivalue("KPI") / kpigoal("KPI") >.60 then 1
when  kpivalue("KPI") / kpigoal("KPI") = .60 then 0
when kpivalue("KPI") / kpigoal("KPI") < .60 then -1
end

 
 

- The trend expression: A trend expression is uses to evaluate the current trend of the value expression compared to the goal expression. The trend expression helps the business user to quickly determine whether the value expression is becoming better or worse relative to the goal expression.

 i use face as trend indicator here, if the profit is > .60 then smile is big.
case when  kpivalue("KPI") / kpigoal("KPI") >.60 then 1
when  kpivalue("KPI") / kpigoal("KPI") = .60 then 0
when kpivalue("KPI") / kpigoal("KPI") < .60 then -1
end



Note*  - In above code kpivalue("KPI") "KPI" is the name of KPI
   
4. Now process the KPI and Browse the same from button "Browse View"

What is KPI - key performance indicator

Key Performance Indicators, also known as KPI or Key Success Indicators (KSI), help an organization define and measure progress toward organizational goals.

Once an organization has analyzed its mission (goal, achievement) ,  it needs a way to measure progress toward those goals. Key Performance Indicators are those measurements.

KPI use to indicate the progress of organization (in form sales, school students strength, time related goal) the set goal is getting or loosing.

KPI may differ from different organization:
- A business may have one of key performance indicator as percentage of income that come from customers.
- A school can have target of number of students in per session.
- In IT company it can be of number of issues resolved in a day.

As a example
In IT company has set the goal for each employee, they have to resolve two issue atleast in a day.
It can leads to three cases:
1. Employee resolve more than 2 issue
2. Employee resolve less than 2 issue
3. Employee resolve equal to 2

for first case the report can indicate the green signal or up arrow
if employee resolve < 2 issue it will reflect down arrow or other user define image.



Whatever Key Performance Indicators are selected, they must reflect the organization's goals, they must be key to its success,and they must be quantifiable (measurable).

Good Key Performance Indicators vs. Bad
Bad:

    -Title of KPI: Increase Sales
    -Defined: Change in Sales volume from month to month
    -Measured: Total of Sales By Region for all region
    -Target: Increase each month

What's missing? Does this measure increases in sales volume by dollars or units? If by dollars, does it measure list price or sales price? Are returns considered and if so do the appear as an adjustment to the KPI for the month of the sale or are they counted in the month the return happens? How do we make sure each sales office's volume numbers are counted in one region, i.e. that none are skipped or double counted? How much, by percentage or dollars or units, do we want to increase sales volumes each month?

Good:
    -Title of KPI: Employee Turnover
    -Defined: The total of the number of employees who resign for whatever reason, plus the number of employees terminated for performance reasons, and that total divided by the number of employees at the beginning of the year. Employees lost due to Reductions in Force (RIF) will not be included in this calculation.
    -Measured: The HRIS contains records of each employee. The separation section lists reason and date of separation for each employee. Monthly, or when requested by the SVP, the HRIS group will query the database and provide Department Heads with Turnover -Reports. HRIS will post graphs of each report on the Intranet.
    -Target: Reduce Employee Turnover by 5% per year.
   
   
   
What Do I Do With Key Performance Indicators?
Once you have good Key Performance Indicators defined, ones that reflect your organization's goals, one that you can measure, what do you do with them?
- You use Key Performance Indicators as a performance management tool, but also as a carrot.
- KPIs give everyone in the organization a clear picture of what is important, of what they need to make happen.
- You use that to manage performance. You make sure that everything the people in your organization do is focused on meeting or exceeding those Key Performance Indicators. You also use the KPIs as a carrot.
- KPIs everywhere: in the lunch room, on the walls of every conference room, on the company intranet, even on the company web site for some of them.

EXAMPLE   
http://j4info.blogspot.in/2014/05/create-kpi-using-ssas-sqlserver.html

source http://management.about.com/cs/generalmanagement/a/keyperfindic_2.htm

Tuesday, May 13, 2014

SOLR: Adding a new core

1. Create the required directories. Let's assume that the data directory is under /solr.
    /solr/new_core1/conf
    /solr/new_core1/data

2. Copy the conf directory from the existing core
    /solr/collection1/conf/* /solr/new_core1/conf

3. Create a solr.xml file in /solr/new_core1/conf directory, write below code in it:

<!--solr persistent="true" sharedLib="lib"-->
  <!--cores adminPath="/admin/cores"-->
    <!--core name="new_core1" instanceDir="/solr/new_core1"-->
      <!--property name="dataDir" value="/solr/new_core1/data" /-->
    <!--/core-->
<!--/solr-->


(remove comment before using the code)

4. Since the dataDir property is already defined in the solr.xml file, we need to comment out the dataDir element in /conf/solrconfig.xml. Search and replace with below line:

${solr.data.dir:./solr/core1/data}

5. Restart SOLR services (solrJetty).
Browse the solr to view the core:
http://localhost/solr/#/~cores/new_core1
web stats