Excel Invoice Template Hours Worked - DOC by ner17598

VIEWS: 1,437 PAGES: 40

More Info
									             SSAS Project – Week 6



                  Ron Villmow



SetFocus Business Intelligence Masters Program



       SQL Server Analysis Services 2005



AllWorks, Inc. – a Fictitious Construction Company



     Darrel Wire, Kevin Goff, Shane Chubbs



                  July 11, 2008
                            Business Intelligence Masters Program -- SSAS Week 6 Project




TABLE OF CONTENTS
1     PROJECT TASK #1: BUILD PROJECT CUBES ................................................................... 3

        1.1      Cube 1 Allworks – Project Master Cube ...................................................................... 3

        1.2      Cube 2 Allworks – Project Overhead Cube ................................................................. 3

        1.3      Cube 3 Allworks – Project Materials Cube................................................................... 4

        1.4      Cube 4 Allworks – Project Labor Cube ........................................................................ 4

2     PROJECT TASK #2: BUILD SSAS QUERIES ....................................................................... 5

        2.1      MDX Collection of Queries in SSAS ............................................................................ 5

        2.2      MDX Queries #1 Based on the Allworks JobMaster Cube .......................................... 6

        2.3      MDX Queries #2 Based on the Allworks Overhead Cube ......................................... 13

        2.4      MDX Queries #3 Based on the Allworks Material Cube ............................................ 14

        2.5      MDX Queries #4 Based on the Allworks Labor Cube ................................................ 16

3     PROJECT TASK #3: KEY PERFORMANCE INDICATORS ................................................ 19

        3.1      KPI Llogic – Calculation and Status Expressions ...................................................... 19

        3.2      KPI 1 Open Receivables As a % of Invoice Amount ................................................. 20

        3.3      KPI 2 Increase in Number of Jobs ............................................................................. 22

        3.4      KPI 3 Overhead As a % of Total Cost........................................................................ 23

        3.5      KPI 4 Profit Percent.................................................................................................... 28

        3.6      KPI 5 Determine Percent Increase in Overhead Category ........................................ 29

4     PROJECT REQUIREMENTS ................................................................................................ 34

REFERENCES .............................................................................................................................. 40




                                                                 Page 2                                                   July 11, 2008
                    Business Intelligence Masters Program -- SSAS Week 6 Project




1 PROJECT TASK #1: Build Project Cubes
Four views were created using SSAS (SQL Server 2005 Analytical Services) in BIDS (Business
Intelligence Development Studio) which is a component of Microsoft Visual Studio 2005. These
four views were used to build four cubes with the structures illustrated in the following diagrams.
The solution was stored in the local hard drive folder C:\AllWorksAnalysis_RWV.

1.1   Cube 1 Allworks – Project Master Cube




1.2   Cube 2 Allworks – Project Overhead Cube




                                               Page 3                                   July 11, 2008
                Business Intelligence Masters Program -- SSAS Week 6 Project




1.3   Cube 3 Allworks – Project Materials Cube




1.4   Cube 4 Allworks – Project Labor Cube




                                          Page 4                               July 11, 2008
                   Business Intelligence Masters Program -- SSAS Week 6 Project




2 PROJECT TASK #2: Build SSAS Queries
2.1   MDX Collection of Queries in SSAS
Four sets on MDX (Multi-Dimensional eXpressions) queries were created using the Analytical
Services portion of SQL Server 2005 One set of MDX queries was created for each of the four
views and cubes. These were stored in the folder C:\AllWorksMDXQueries_RWV. Each query
was stored individually as well in a Master query set for each cube.




                                             Page 5                               July 11, 2008
               Business Intelligence Masters Program -- SSAS Week 6 Project


2.2   MDX Queries #1 Based on the Allworks JobMaster Cube
/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     MDX Queries for DataSource View 1:
|           Allworks_JobMaster
|===================================================
*/

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     1.1 Retrieve total labor costs by Client
|===================================================
*/
SELECT [Measures].[Total Labor Cost]
      ON Columns,
([Clients 1].[Clients].[Clients]
,[Clients 1].[Client Name].[Client Name])
      ON Rows
FROM [Allworks_JobMaster]

 go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     1.2 Retrieve total labor costs by Client
|           Filter out nulls
|===================================================
*/
With Member [TotalLaborCost] As
[Measures].[Total Labor Cost]
      ,format_string='currency'

SELECT  [TotalLaborCost]
      ON Columns,
Non Empty([Clients 1].[Clients].[Clients]
,[Clients 1].[Client Name].[Client Name])
      ON Rows
FROM [Allworks_JobMaster]
 go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     1.3 Retrieve total labor costs by County
|           Keep Nulls
|===================================================|
*/
With Member [TotalLaborCost] As


                                         Page 6                               July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project


[Measures].[Total Labor Cost]
      ,format_string='currency'

SELECT  [TotalLaborCost]
      ON Columns,
[County 1].[County Name].[County Name]
      ON Rows
FROM [Allworks_JobMaster]

go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     1.4 Retrieve total labor costs by Division
|           Keep Nulls
|===================================================
*/
With Member [TotalLaborCost] As
[Measures].[Total Labor Cost]
      ,format_string='currency'

SELECT  [TotalLaborCost]
      ON Columns,
([Division 1].[Division].[Division]
,[Division 1].[Division Name].[Division Name])
      ON Rows
FROM [Allworks_JobMaster]

go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     1.5 Retrieve total labor costs by
|           Client Account Grouping
|           Keep Nulls
|===================================================
*/
With Member [TotalLaborCost] As
[Measures].[Total Labor Cost]
      ,format_string='currency'

SELECT  [TotalLaborCost]
      ON Columns,
([Client Groupings 1].[Client Groupings].[Client Groupings]
,[Client Groupings 1].[Grouping Name].[Grouping Name])
      ON Rows
FROM [Allworks_JobMaster]

go

/*
|===================================================

                                        Page 7                               July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project


|     Ron Villmow
|     July 9, 2008
|     1.6 Retrieve total labor cost
|           ,total material cost and
|           ,total overhead cost
|           by Client
|           Keep Nulls
|===================================================
*/
With Member [TotalLaborCost] As
[Measures].[Total Labor Cost]
      ,format_string='currency'

Member [TotalMaterialCost] As
[Measures].[Total Material Cost]
      ,format_string='currency'

Member [TotalOverheadCost] As
[Measures].[Total Overhead]
      ,format_string='currency'

SELECT  {[TotalLaborCost],[TotalMaterialCost]
      ,[TotalOverheadCost]}
      ON Columns,
[Clients 1].[Client Name].[Client Name]
      ON Rows
FROM [Allworks_JobMaster]

go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     1.7 Retrieve total labor cost
|           ,total material cost
|           ,total overhead cost and
|           Calculate total of all costs
|           by Client
|     Keep Nulls
|===================================================
*/
With Member [TotalLaborCost] As
[Measures].[Total Labor Cost]
      ,format_string='currency'

Member [TotalMaterialCost] As
[Measures].[Total Material Cost]
      ,format_string='currency'

Member [TotalOverheadCost] As
[Measures].[Total Overhead]
      ,format_string='currency'

Member [TotalAllCosts] As
([TotalLaborCost]+[TotalMaterialCost]

                                        Page 8                               July 11, 2008
                 Business Intelligence Masters Program -- SSAS Week 6 Project


         +[TotalOverheadCost])
         ,format_string='currency'


SELECT  {[TotalLaborCost],[TotalMaterialCost]
      ,[TotalOverheadCost],[TotalAllCosts]}
      ON Columns,
[Clients 1].[Client Name].[Client Name]
      ON Rows
FROM [Allworks_JobMaster]

go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
      1.8 Calculate total of all costs
      , the total of all profits and
|            , the total profit %
      by Job
|            Eliminate division by zero errors and nulls
|===================================================
*/
With Member [TotalLaborCost] As
[Measures].[Total Labor Cost]
      ,format_string='currency'

Member [TotalMaterialCost] As
[Measures].[Total Material Cost]
      ,format_string='currency'

Member [TotalOverheadCost] As
[Measures].[Total Overhead]
      ,format_string='currency'

Member [TotalAllCosts] As
([TotalLaborCost]+[TotalMaterialCost]
      +[TotalOverheadCost])
      ,format_string='currency'

Member [TotalProfits] As
([Measures].[Additional Labor Profit]
+[Measures].[Total Labor Profit]
+[Measures].[Total Material Profit])
      ,format_string='currency'

Member [PCT_TotalProfit] As
iif(([TotalProfits]+[TotalAllCosts])>0
      ,[TotalProfits]/([TotalProfits]+[TotalAllCosts]),null)
      -- The null eliminates / by 0 and nulls because of the
      -- non empty function in the rows
      -- Also the percent format string does the *100
      ,format_string='percent'

SELECT    {[TotalAllCosts],[TotalProfits],[PCT_TotalProfit]}

                                           Page 9                               July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project


      ON Columns,
Non Empty ([Job Master 3].[Client Job].[Client Job])
      ON Rows
FROM [Allworks_JobMaster]

go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     1.9 Calculate total of all costs
|           , the total of all profits and
      , the total profit %
|           by Client
|     Eliminate division by zero errors and nulls

|===================================================
*/
With Member [TotalLaborCost] As
[Measures].[Total Labor Cost]
      ,format_string='currency'

Member [TotalMaterialCost] As
[Measures].[Total Material Cost]
      ,format_string='currency'

Member [TotalOverheadCost] As
[Measures].[Total Overhead]
      ,format_string='currency'

Member [TotalAllCosts] As
([TotalLaborCost]+[TotalMaterialCost]
      +[TotalOverheadCost])
      ,format_string='currency'

Member [TotalProfits] As
([Measures].[Additional Labor Profit]
+[Measures].[Total Labor Profit]
+[Measures].[Total Material Profit])
      ,format_string='currency'

Member [PCT_TotalProfit] As
iif(([TotalProfits]+[TotalAllCosts])>0
                  ,[TotalProfits]/([TotalProfits]+[TotalAllCosts])
                  ,null
      )
      ,format_string='percent'
      -- Division by 0 is indicating by a null %
      -- Null total items are eliminated
SELECT {[TotalAllCosts],[TotalProfits],[PCT_TotalProfit]}
      ON Columns,
Non Empty ([Clients 1].[Client Name].[Client Name])
      ON Rows
FROM [Allworks_JobMaster]


                                        Page 10                              July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project


 go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
      1.10 Display a count of Jobs
|           by Client
|           Treat nulls as a 0 count

|===================================================
*/
WITH Member [JobCount] AS
iif([Measures].[Job Summary Facts Count]
      ,[Measures].[Job Summary Facts Count] -- Not null, use count
      ,0)                                   -- Null = 0

SELECT [JobCount]
      ON Columns,
([Clients 1].[Client Name].[Client Name])
      ON Rows
FROM [Allworks_JobMaster]

go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
      1.11 Retrieve all clients with
|           a total labor cost > 5000
|           and 'INC' in the name
|===================================================
*/
With Set [INC_Clients] As
filter(
[Clients 1].[Client Name].[Client Name]
,instr([Clients 1].[Client Name].currentmember.membervalue,'INC')
and [TotalLaborCost]>5000 )

Member [TotalLaborCost] As
      [Measures].[Total Labor Cost]
      ,format_string='currency'

select [TotalLaborCost]
      on columns,
[INC_Clients]
on rows
from [AllWorks_JobMaster]

go

/*
|===================================================
|     Ron Villmow


                                        Page 11                              July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project


|     July 9, 2008
      1.12 List the jobs that make up
|           the top 30% of total invoice amount
|
|===================================================
*/
With Member [InvoiceAmount] As
      [Measures].[Invoice Amount]
      ,format_string='currency'

select [InvoiceAmount]
      on columns,
TopPercent([Job Master 3].[Description].[Description]
      ,30
      ,[InvoiceAmount]
      )
      on rows
from [AllWorks_JobMaster]

go

/*
|===================================================
|     Ron Villmow
|     July 9, 2008
|     1.12a List the jobs that make up
      the top 30% of total invoice amount
|===================================================
*/
With Member [InvoiceAmount] As
      [Measures].[Invoice Amount]
      ,format_string='currency'

select [InvoiceAmount]
      on columns,

Non Empty
{
-- Top jobs based on 30% of the total invoice amount
TopPercent([Job Master 3].[Description].[Description]
      ,30
      ,[InvoiceAmount]
      )
-- Ordered jobs to compare all the jobs and invoice amounts
,Order([Job Master 3].[Description].[Description]
      ,[InvoiceAmount]
      ,DESC)
}
      on rows

from [AllWorks_JobMaster]




                                        Page 12                              July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project




2.3   MDX Queries #2 Based on the Allworks Overhead Cube
/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     MDX Queries for DataSource View 2:
|           Allworks_Overhead
|===================================================
*/

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     2.1 Show Overhead by Overhead Category
|           for Q3 and Q4 2005
|     Keep nulls to show all categories
|===================================================
*/
SELECT {
      ([Measures].[CurrentOverhead],[Project Overhead View].[FY
Qtr].&[2005 Q3]),
      ([Measures].[CurrentOverhead],[Project Overhead View].[FY
Qtr].&[2005 Q4])
      }
      ON Columns,
[Overhead].[Description].children
      ON Rows
FROM [Allworks_Overhead]

go

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     2.2 Show Overhead by Overhead Category
|           for Q3 and Q4 2005
|           Also show the % change between the two quarters
|     Omit nulls
|===================================================
*/
With Member [QtrOverhead] As
      ([Measures].[Weekly Over Head],[Project Overhead View].[FY
Qtr].currentMember)
      ,format_string='currency'
Member [PreviousQtrOverhead] As
      ([Measures].[Weekly Over Head],[Project Overhead View].[FY
Qtr].prevMember)
      ,format_string='currency'
Member [PCT_QuarterIncrease] AS
      iif([PreviousQtrOverhead]>0



                                        Page 13                              July 11, 2008
               Business Intelligence Masters Program -- SSAS Week 6 Project


            ,([QtrOverhead]-
[PreviousQtrOverhead])/[PreviousQtrOverhead]
            ,NULL)
      ,format_string='percent'
SELECT {[PreviousQtrOverhead]
      ,[QtrOverhead]
      ,[PCT_QuarterIncrease]
      }
      ON Columns,
Non Empty [Overhead].[Description].children
      ON Rows
FROM [Allworks_Overhead]
WHERE [Project Overhead View].[FY Qtr].&[2005 Q4]

go

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     2.3 Show Overhead by Overhead Category
|           for 2005 ordered by Overhead $$ descending
|     Omit nulls
|===================================================
*/

SELECT [Measures].[Weekly Over Head]
      ON Columns,
Non Empty
Order([Overhead].[Description].children
      ,[Measures].[Weekly Over Head]
      ,DESC)
      ON Rows
FROM [Allworks_Overhead]
WHERE [Project Overhead View].[FY Year].&[2005]



2.4   MDX Queries #3 Based on the Allworks Material Cube
/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     MDX Queries for DataSource View 3:
|           Allworks_Materials
|===================================================
*/

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     3.1 Show Material Purchase amounts
|           by Material types for 2005
|           Included are purchase amounts


                                         Page 14                              July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project


|           for fuel, materials and petty cash
|===================================================
*/
SELECT [Measures].[Purchase Amount]
      ON Rows,
([Material Types].[Description].children,[Project Master View].[FY
Year].&[2005])
ON Columns
FROM [Allworks_Materials]

go

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     3.2 Show Material Purchase amounts
|           by Material types for 2005
|           Included are purchase amounts
|           for fuel, materials and petty cash
|     Break down purchase by Client
|     Eliminate NULLS
|===================================================
*/
SELECT [Measures].[Purchase Amount]
      ON Columns,
Non Empty([Material Types].[Description].children,[Job Master
1].[Client Name].[Client Name])
ON Rows
FROM [Allworks_Materials]
WHERE [Project Master View].[FY Year].&[2005]

go

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     3.3 Show Material Purchase amounts
|            for 2005 for each client
|            Ordered by amount for each client
|     Eliminate NULLS
|===================================================
*/
SELECT [Measures].[Purchase Amount]
      ON Columns,
Non Empty
      Order([Job Master 1].[Client Name].children
      ,[Measures].[Purchase Amount]
      ,DESC)
ON Rows
FROM [Allworks_Materials]
WHERE [Project Master View].[FY Year].&[2005]

go


                                        Page 15                              July 11, 2008
               Business Intelligence Masters Program -- SSAS Week 6 Project



/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     3.4 Show Material Purchase amounts
            and material types
|           for each job
|           Ordered by total amount for each job
|     Show NULLS as 0 to see all jobs
|===================================================
*/
With Member [PurchaseAmount] As
      iif([Measures].[Purchase Amount]>0
            ,[Measures].[Purchase Amount]
            ,0)
            -- 0 to show all jobs,
            -- Null to eliminate jobs with 0 amounts
      ,format_string='currency'

SELECT ([PurchaseAmount]
            ,[Material Types].[Description].members)
            -- members includes the all as well as the childrem
      ON Columns,
Non Empty
      Order([Job Master 1].[Client Job].children
      ,[PurchaseAmount]
      ,bDESC)
      ON Rows
FROM [Allworks_Materials]



2.5   MDX Queries #4 Based on the Allworks Labor Cube
/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     MDX Queries for DataSource View 4:
|           Allworks_Materials
|===================================================
*/

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     4.1 List hours worked, total labor
|           and labor rate
|           for each 2005 employee
|     Eliminate nulls
|===================================================
*/
With Member [LaborRate] As
      [Measures].[Total Labor]/[Measures].[Hoursworked]


                                         Page 16                              July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project


     ,format_string='currency'

SELECT
{[Measures].[Hoursworked],[Measures].[Total Labor],[LaborRate]}
       ON Columns,
Non Empty [Employees].[Full Name].children
       ON Rows
FROM [Allworks_Labor]
WHERE [Project Labor View].[FY Year].&[2005]

go

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     4.2 List hours worked, total labor
|           and labor rate
|           for each 2005 employee
|     Order by labor rate
|     Eliminate nulls
|===================================================
*/
With Member [LaborRate] As
      [Measures].[Total Labor]/[Measures].[Hoursworked]
      ,format_string='currency'

SELECT
{[Measures].[Hoursworked],[Measures].[Total Labor],[LaborRate]}
       ON Columns,
Non Empty
Order([Employees].[Full Name].children
       ,[LaborRate]
       ,DESC)
       ON Rows
FROM [Allworks_Labor]
WHERE [Project Labor View].[FY Year].&[2005]

go

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     4.3 List totals for hours worked,
|           total labor and labor rate
|           for the 2005 contractors
|           and the 2005 employee
|===================================================
*/
/*
With Member [Contractors] As
      ([Employees],[Employees].[Employee Flag].&[True])
Member [Employees] As
      ([Employees].Members,[Employees].[Employee Flag].&[False])
      */

                                        Page 17                              July 11, 2008
              Business Intelligence Masters Program -- SSAS Week 6 Project


With Member [LaborRate] As
      [Measures].[Total Labor]/[Measures].[Hoursworked]
      ,format_string='currency'
Member [TotalLabor] As
      [Measures].[Total Labor]
      ,format_string='currency'

SELECT
{[Measures].[Hoursworked],[TotalLabor],[LaborRate]}
       ON Columns,
{[Employees].[Employee Flag].children}
       -- False are contractors
       -- True are employees
       ON Rows
FROM [Allworks_Labor]
WHERE [Project Labor View].[FY Year].&[2005]

go

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     4.4 Show all the jobs and the hours worked
|           by the top 3 employees for 2005
|           Order by Job, and hours worked in each job
|===================================================
*/
SELECT [Measures].[Hoursworked]
      ON Columns,
Non Empty Generate([Job Master 2].[Description].children
            ,Order(TopCount(([Job Master 2].[Description].currentmember
                  ,[Employees].[Full Name].children),3
                  ,[Measures].[Hoursworked])
                  ,[Measures].[Hoursworked]
                  ,Desc
                  )
            )
      ON Rows
FROM [Allworks_Labor]
WHERE [Project Labor View].[FY Year].&[2005]


go

/*
|===================================================
|     Ron Villmow
|     July 10, 2008
|     4.5 Show all employees and the hours worked
|           for quaters Q4 2005
|           and 4 quaters previous
|===================================================
*/
With Member [Hours_Q4_2005] As
      ([Measures].[Hoursworked]

                                        Page 18                              July 11, 2008
                   Business Intelligence Masters Program -- SSAS Week 6 Project


      ,[Project Labor View].[FY Qtr].&[2005 Q4])
      ,format_string='#,#.00'
Member [Hours_4QrtsPrev] As
      ([Measures].[Hoursworked]
      ,[Project Labor View].[FY Qtr].&[2005 Q4].lag(4))
      ,format_string='#,#.00'

SELECT {[Hours_Q4_2005],[Hours_4QrtsPrev]}
      ON Columns,
Non Empty [Employees].[Full Name].children
      ON Rows
FROM [Allworks_Labor]




3 PROJECT TASK #3: Key Performance Indicators
Calculation members and KPI’s (Key Performance Indicators) were added to the Job Master and
Overhead cubes in BIDS. The logic to create the KPI’s as well as Excel screen shots of the
results are included in the following pages.

3.1   KPI Llogic – Calculation and Status Expressions

-- KPI 1 Open Receivables
([Measures].[Invoice Amount] - [Measures].[Amount Received])

iff([Measures].[Invoice Amount]>0,
    (OpenReceivables/[Measures].[Invoice Amount]),0)

Case
  When KPIValue("KPI_PCT_OpenReceivables")
    <=KPIGOAL("KPI_PCT_OpenReceivables")THEN 1
  When KPIValue("KPI_PCT_OpenReceivables") <= .20 THEN 0
  ELSE -1
END

-- KPI 2 Job Count
 ([Measures].[Job Summary Facts Count])

([Measures].[NumberOfJobs],[All Works Calendar].[FY Qtr].prevMember)
([Measures].[NumberOfJobs],[All Works Calendar].[FY Qtr].&[2006 Q1])

((NumberOfJobs)-(PrevNumberOfJobs))

Case
  When KPIValue("KPI_JobCountIncrease")
    >=KPIGOAL("KPI_JobCountIncrease")THEN 1
  ELSE -1
END

-- KPI 3 Percent Overhead Cost
([Measures].[Total Labor Cost]+[Measures].[Total Material Cost]+[Measures].[Total Overhead])

([Measures].[Total Labor Cost]+[Measures].[Total Material Cost]+[Measures].[Total Overhead])


                                             Page 19                               July 11, 2008
                    Business Intelligence Masters Program -- SSAS Week 6 Project


Case
  When KPIValue("KPI_PCT_OverheadCost")
    <=KPIGOAL("KPI_PCT_OverheadCost")THEN 1
  When KPIValue("KPI_PCT_OverheadCost")<=.15 THEN 0
  ELSE -1
END

-- KPI 4 Percent Profit
([Measures].[Total Labor Profit]+[Measures].[Total Material Profit]+[Measures].[Additional Labor
Profit])

([Measures].[Total Labor Profit]+[Measures].[Total Material Profit]+[Measures].[Additional Labor
Profit])

Case
  When KPIValue("KPI_PCT_Profit")
    >KPIGOAL("KPI_PCT_Profit")THEN 1
  When KPIValue("KPI_PCT_Profit")>.05 THEN 0
  ELSE -1
END

-- KPI 5 Percent Overhead Increase Per Quarter
([Measures].[Weekly Over Head],[Project Overhead View].[FY Qtr].currentMember)

([Measures].[Weekly Over Head],[Project Overhead View].[FY Qtr].prevMember)

(iif(PreviousOverhead>0,(CurrentOverhead-PreviousOverhead)/PreviousOverhead,0))


Case
  When KPIValue("KPI_PCT_OverheadIncrease")
    <KPIGOAL("KPI_PCT_OverheadIncrease")THEN 1
  When KPIValue("KPI_PCT_OverheadIncrease")<=.15 THEN 0
  ELSE -1
END


3.2   KPI 1 Open Receivables As a % of Invoice Amount
The original KPI was created without regard to nulls and errors. The KPI was later created again
and corrections were made for division by 0 errors.




                                              Page 20                                 July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 21                              July 11, 2008
                Business Intelligence Masters Program -- SSAS Week 6 Project


3.3   KPI 2 Increase in Number of Jobs




                                          Page 22                              July 11, 2008
                Business Intelligence Masters Program -- SSAS Week 6 Project


3.4   KPI 3 Overhead As a % of Total Cost




                                          Page 23                              July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 24                              July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 25                              July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 26                              July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 27                              July 11, 2008
                Business Intelligence Masters Program -- SSAS Week 6 Project


3.5   KPI 4 Profit Percent




                                          Page 28                              July 11, 2008
                   Business Intelligence Masters Program -- SSAS Week 6 Project


3.6   KPI 5 Determine Percent Increase in Overhead Category
The percent of increase from each quarter to the next was created and saved to Excel. All
Overhead Categories were shown with a break for each quarter. This makes analysis and growth
trends readily available. This could also have been done with slices to show the growth for just
one quarter if desired.




                                             Page 29                                July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 30                              July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 31                              July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 32                              July 11, 2008
Business Intelligence Masters Program -- SSAS Week 6 Project




                          Page 33                              July 11, 2008
                  Business Intelligence Masters Program -- SSAS Week 6 Project


4     PROJECT REQUIREMENTS

                        Part 4: SSAS Project Overview

There are three phases to the SSAS Project:
   1. Creating an SSAS solution in BIDS, and building four views/cubes for Project
      (“Job”) Master, Project Overhead, Project Materials, and Project Labor.

    2. A workshop on SSAS queries

    3. Building KPIs (covered in this document)

    4. Deliverables for this:
           a. The SSAS solution from BIDS
                  i. Create in a folder called AllWorksAnalysis_KSG (initials)
           b. The MDX queries that you create for each of the four views
                  i. When you create the project, call the Name and the Location the
                      same thing (e.g. C:\AllWorksMDXQueries_KSG)
           c. A screenshot of the results from each Excel KPI test (5 screen shots)
                  i. Kpi1 through KPI5 (jpg, bmp)

DataSource View 1: Project (“Job”) Master Cube - Construction Job Summarization (1
row per job)
           o   Job Number
           o   Client
           o   Description
           o   ClientPK, CountyPK, DivisionPK, ClientGroupingPK
           o   Total Labor Cost (Hours worked from timesheets * worker effective rate for the
               day)
           o   Total Overhead Cost (Hours worked from timesheets * overhead effective rate
               for the day, taking into account if the hours worked is for a contractor or
               employee)
           o   Total Material Cost (sum of materials purchased)
           o   Total Labor Profit (sum of labor hours * job additional overhead percentage)
           o   Total Material Profit (sum of material purchases * job material markup %)
           o   Total Other Profit (sum of Additional labor from Invoice / Job Xref table)
           o   Total Labor Hours
           o   Total Dollars Billed
           o   Total Dollars Received




                                            Page 34                              July 11, 2008
                Business Intelligence Masters Program -- SSAS Week 6 Project


   For View 1, when creating the data source view (and ultimately the cube), add
    dimensions for JobMaster, Clients, County, Division, and ClientGroupings. This is so that
    we can query the Job View by multiple dimensions
   Deploy the project to the server
   Create MDX queries in SQL Server to determine the following:
        o Retrieve total labor costs by Client
        o Retrieve total labor costs by Client, and filter out any NULL values
        o Retrieve total labor costs by County
        o Retrieve total labor costs by Division
        o Retrieve total labor costs by Client Account grouping
        o Retrieve 3 meatures…total labor cost, total material cost, and total overhead by
            client
        o Do the same (retrieve 3 measures) and add a 4th measure, a calculated measure,
            that adds all three costs
        o Retrieve and calculate the total costs, the total profit, and total profit %, for
            each individual job. The three are calculated as follows:
                 Total costs = total labor cost + total material cost + total overhead cost
                 Total profit = labor profit + material profit + additional labor overhead
                    profit
                 Total profit % = (total profit / (total cost + total profit)) * 100
        o Do the same thing as above, but group it by client
        o Display a count of Jobs by client
        o Retrieve all Clients with a Total Labor cost to date greater than 5,000, and the
            word 'INC' appears in the client name
        o List the jobs that make up the top 30% of total invoice amount

   View 2: Project (“Job”) Overhead Cube - Overhead summary by Week
         o Job Number
         o Labor overhead Category
         o Week-ending Date (Saturday date)
         o The Fiscal year (2005, 2006, etc)
         o The Fiscal Quarter (Q4 2005, Q1 2006, etc.)
         o Total overhead amount (hours worked from timesheets * overhead effective
            rate for the day, taking into account if the hours worked is for a contractor or
            employee)
   For View 2, when creating the data source view (and ultimately the cube), do the
    following:
         o create the 2nd view, selecting Overhead as the only table, and using the script
            to create the summarized table for View 2. Call the view
            “ProjectOverheadView”.


                                          Page 35                                 July 11, 2008
                   Business Intelligence Masters Program -- SSAS Week 6 Project


           o   in the DSV designer, create a link on overheadPK between Overhead and the
               result of the new SQL query
          o Shift-click on overheadpk, weekending, and JobmasterPK (for the new table)
               and create a multi-column primary key
          o Create the cube, using Overhead as the dimension, and the new
               ProjectOverheadView table as both a fact table and a dimension table
      Deploy the project to the server
      Create MDX queries in SQL Server to determine the following:
          o Show Overhead by Overhead Category for Q3 and Q4 2005 (hint, use the FY Qtr
               as a dimension)
          o Show Overhead by Overhead Category for Q3 and Q4 2005, and also show the %
               of change between the two
          o Show Overhead by Overhead Category for all of 2005, order by Overhead $$
               amount descending

View 3: Project (“Job”)Materials Cube - Material purchases by Week
            o Job Number
            o Material Purchase Type (material, fuel, petty cash)
            o Week-ending Date (Saturday Date)
            o Total Purchase Amount for the week
            o The Fiscal year (2005, 2006, etc)
            o The Fiscal Quarter (Q4 2005, Q1 2006, etc.)
      For View3, when creating the data source view (and ultimately the cube), do the
       following:
            o create the3rd view, selecting Material Type, Job Master, and client as the tables,
               and using the script to create the summarized table for View 3. Call the view
               “ProjectMasterView”.
            o in the DSV designer, create a link on Material Type PK between the Material
               type table and the new SQL query. Also create a link on the JobMasterPK
               between the Job master table and the new SQL query.
            o Shift-click on Material type PK, weekending, and Job MasterPK (for the new
               table) and create a multi-column primary key
            o Create the cube, using Client and Job Master as the dimensions, and the new
               ProjectMasterView table as both a fact table and a dimension table
      Create MDX queries in SQL Server to determine the following:
            o Show Material Purchase amounts by Material Type for 2005. The result set
               should have 1 column for the purchase amounts for Fuel, Materials, and petty
               Cash
            o Show Material purchase amounts for 2005, broken out by Material Purchase
               type and client. (for instance, Fuel for client A, B, C…Petty Cash for client A, B,
               C, etc.)


                                             Page 36                                 July 11, 2008
               Business Intelligence Masters Program -- SSAS Week 6 Project


        o   Show a list of total client material purchases for 2005, in descending purchase
            amount order. The result set should show at the top which client required the
            most materials.
        o   Show jobs in order of purchase amount and then show the breakdown in each
            job of material type (for instance, Job A, total purchase amount, amount for
            fuel, amount for materials, amount for petty cash, etc.) The general order
            should be by total purchase amount, so that the top of the result set shows the
            jobs that required the highest purchase amounts


   View 4: Project (“Job”) Labor Cube - Labor by Employee/Week
         o Job Number
         o Employee ID
         o Week-Ending Date (Saturday Date)
         o Total Hours for the week
         o Total Labor Cost for the week (hours worked from timesheets * effective rate
            for that day)
         o The Fiscal year (2005, 2006, etc)
         o The Fiscal Quarter (Q4 2005, Q1 2006, etc.)
         o Total overhead amount (hours worked from timesheets * overhead effective
            rate for the day, taking into account if the hours worked is for a contractor or
            employee)
   For View 4, when creating the data source view (and ultimately the cube), do the
    following:
         o create the 4th view, selecting Employees and Job Master as the tables, and
            using the script to create the summarized table for View 4. Call the view
            “ProjectLaborView”.
         o in the DSV designer, create a link on EmployeePK between employee table and
            the result of the new SQL query. Also create a link on JobmasterPK between the
            Job master table and the result of the new SQL query.
         o Shift-click on JobMasterPK, weekending, and EmployeePK (for the new table)
            and create a multi-column primary key
         o Create the cube, using Job Master and Employee as the dimensions, and the
            new ProjectLaborView table as both a fact table and a dimension table
   Deploy the project to the server
   Create MDX queries in SQL Server to determine the following:
         o List Hours Worked and Total Labor for each employee for 2005, along with the
            labor rate (Total labor / Hours worked).
         o Perform the same query as 4.1, except this time, sort the employees by labor
            rate descending, to see the employees with the highest labor rate at the top.



                                         Page 37                                July 11, 2008
                      Business Intelligence Masters Program -- SSAS Week 6 Project


             o     For 2005, show Total Hours worked, total labor dollars, and total labor rate --
                  for contractors (employee flag is false) and employees (employee flag is true)
             o    For 2005, show the job and the top three employees who worked the most
                  hours. Show the jobs in job order, and within the job show the employees in
                  hours worked order
             o    Show All employees for 2005 Q4, and four periods ago, for total hours worked in
                  the Quarter



                             Key Performance Indicators (KPIs)
                      Have the students create the following 5 KPIs –
       test them in Excel (using the document ViewingKPIsInExcel.PPT as a guide)
 the reason for testing in Excel is because the SSAS KPI browser does not always render
                                      results correctly

KPI1:   Project   Open Receivables as a %        0 – 10% OK                      Run for all Clients to Excel,
        (“Job”)   of Invoice Amount                                              save the spreadsheet,
        Master                                   Greater than 10%, less than     indicate in a note on the
        Cube      Open Receivables =             or equal to 20% , warning       spreadsheet any clients
                  Invoice Amount minus                                           who are either at the
                  Amount Received                Greater than 20% – bad          warning status or bad
                                                                                 status
                                                 Use Traffic Light

KPI2:   Project   Increase in number of Jobs     0 or more is good (meaning      Run for all Clients to Excel
                                                                                          nd
        (“Job”)   from the previous quarter      we’ve done at least one more    for the 2 Qtr of 2006,
        Master    to the current quarter         Job for this quarter than for   save the spreadsheet,
        Cube                                     the last quarter….a zero        indicate in a note on the
                  (hint: use PrevMember)         means we’ve done at least       spreadsheet any clients
                                                 the same # of Jobs)             who are at the bad status.


                                                 -1 is bad (we’ve done less
                                                 Jobs for the client in the
                                                 current qtr, versus the
                                                 previous quarter

                                                 Use Traffic Light

KPI3:   Project   Overhead as a % of Total       0 – 10% OK                      Run For all Jobs to Excel
        (“Job”)   Cost                                                           Save spreadsheet
        Master                                   Greater than 10%, less than
        Cube      (where total cost = Total      or equal to 15%, Warning
                  Overhead + Total material
                  Cost + Total Labor Cost)       Greater than 15% - bad

                                                 Use Traffic Light

KPI4:   Project   Profit %                       Less than or equal to 5% is     Run for all Clients, save the
        (“Job”)                                  bad                             spreadsheet to Excel
        Master    Total Profit /
        Cube      (Total Costs + Total Profit)   Greater than 5%, Less than
                                                 or equal to 15%, warning
                  Total Profit = Total Labor
                  Profit +                       Greater than 15%, good
                  Total Material Profit +

                                                  Page 38                                       July 11, 2008
                     Business Intelligence Masters Program -- SSAS Week 6 Project


                 Additional Labor Profit        Use Traffic Light

                 Total Costs = Total Labor
                 Cost +
                 Total Material Costs +
                 Total Overhead (hint: you
                 created this as a
                 calculation for KPI3, so you
                 can reuse it)
KPI5   Project   Determine % increase in        Less than 10% increase is   Run for each Overhead
       (“Job”)   Overhead category from         good                        Category, save the
       Labor     one quarter to another         Between 10% and 15%,        spreadsheet to Excel
       Cute                                     warning
                                                Greater than 15%, bad




                                                 Page 39                                July 11, 2008
               Business Intelligence Masters Program -- SSAS Week 6 Project




                                  REFERENCES


AppDev SQL Server 2005 Analysis Services.

George Spofford and Harinath, Webb, Huang, Civardi. MDX Solutions: Second
     Edition, Wiley Publishing, Inc., 2006.

SetFocus SSAS Project Overview (ssasspec.docx) Version 2 – 07/03/2008.




                                         Page 40                              July 11, 2008

								
To top