Have you ever used a pivot table in Excel?  It's a great tool which allows you to slice data with different criteria:

Date Region Product Total Sum of Total   Product    
1/10/2001 Europe bag 3 Region Date bag coat Grand Total
2/10/2001 Asia coat 4 America 3/10/2001   5 5
3/10/2001 America coat 5   10/13/2001 8 8
5/11/2001 Europe bag 33 America Total 8 5 13
8/12/2001 Europe coat 7 Asia 2/10/2001   4 4
10/13/2001 America bag 8 Asia Total     4 4
Europe 1/10/2001 3   3
  5/11/2001 33 33
  8/12/2001   7 7
Europe Total   36 7 43
Grand Total   44 16 60

Above you see that we have a spreadsheet that contains some orders and right beside it we have a pivot table that breaks down what sold in a region.

As data grows more and more calculations are needed to produce the breakdowns.  That is where SQL Server (Analysis Services) comes to the rescue.  It allows people to specify what they want to measure and how they want to slice the data.  Cube definitions in Analysis Services allow people to specify what they watn to measure and how they are plannign to slice the data.  That is I can specify I want to measure all the Totals from my orders and I want to slice my Totals by Region and Date.

This following link describes Cubes is more detail:  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_da_whatrcubes.asp

One of the deliverables that we have for the Reporting & Anaytics team is to produce good cubes. 

  • identification of data that you want to analyze
  • allowing you to choose what you want to measure
  • coming up with suggested ways of slicing this data

To start a user would make Entities in MBF.  These entities are objects that programs can interact with.  The user would specify properties on these objects that the application are able to see.  These properties will get their values from the database. For example:

  • Product entity (ProductName, UnitCost)
  • Order entity (OrderDate)
  • OrderLine entity (Quantity)
  • Customer entity (FirstName, LastName)
  • Address entity (Street, Region, Country)

Next the user would setup Associations & Compositions (relationships) to allow navigation between entities.  This will enable one to get from OrderLine to the Product that that OrderLine refers to.  It will allow one to get from the Order to the Customer etc.

Once the entities are designed the entities will be mapped to the backing SQL store.  This is where the Entities will get all their data.  Also set operations on the Entities will translate to SQL updates.

NOW Analytics how comes in.  Once these entities and relationships are specified Reporting&Analytics tools are ready to make cubes.

  • look at the object graphs & mappings and make useful cubes