the amb data warehouse: a case study

The AMB Data Warehouse - A Case Study

The AMB Data WarehouseA Case Study

Mark GschwindVP, Business Information ArchitectureAMB Property Corporation

[email protected]

10/8/2010


Agenda

• Background on AMB’s business, its data and users BI requirements

• How we solved for it-- Data Architecture-- CDC-- Master Data Management-- Data Quality-- Reporting-- Analysis

• Generalizing, about what you can learn from us• Q&A10/8/2010

The AMB Data Warehouse - A Case Study10/8/2010

AMB’s Business


AMB’s Data

Data ObjectsBuildings…which are (usually) separated into units which……which have tenants with leases that usually span 10 years We make money when…Tenants pay rent, or we develop a building to spec for a tenant. Changing over time:• Tenants come and go• Units change as walls get torn down and changed• Buildings transfer ownership from the REIT to funds• People managing the property change over time• Buildings change status from “Development” to “Operating” to

“Sold”

10/8/2010


AMB’s Data

Source Systems• Four ERP systems: CTI+MRI+Yardi+FAS and one forecasting sys• Coding conventions are not uniform• About 400K g/l transactions/month, peaking at qtr end• Leasing Activity much smaller, but no less important (4,100

units, about 3,000 tenants)• ERP’s do not have ESB messages/queues/brokers. In fact there

is no accepted standard for an XML Namespace for Real Estate. CDC impact, no MDM “hubs” possible

• Limited reporting capabilities in ERP’s to the point that they cannot support reporting to US and Int’l regulatory agencies such as the SEC and IRS SOX impact

• They allow bad data to get entered Data quality impact

10/8/2010


AMB’s Users

Sr. Executives (3) and Managing Directors (10)0 of 10 Managing Directors use the cubesNeed hi-level KPI’s that clearly show alerts, w drill-down to an actionable levelNeed everything in one place – do not want to go to LMS+OLAP+BPC+BOXIData is heavily massaged to allocate costs, split operational variance from FX variance, etc.Want to do “what if” analysis on interest rates, macro-economic variables, organizational changes impacting G&AThey are under-served - still learning what these people want Regional Managers (30)Only 12 of 30 Operations RM’s use the cubes. Probably only 6 activelyThese people are trying to keep our buildings leased up, and actively finding new dealRequire external data on market rents, market absorption, etcNeed to see what is going to happen on the leasing side, less interested in reporting history Power Analysts (5)These people provide the Sr. Executives and Managing Directors their reports and analysisDo a lot of massaging in Excel for what-ifs, etc.Intimately familiar w the dataPower Excel users building elaborate models for Sr. Executives, love OLAP Accounting and Operations Analysts (12)19 of 20 Operations Analysts use the cube12 of 12 Accounting Managers use the cubeMostly concerned with reporting history, and the mechanics of the forecast. But they deal w these things at a consolidated level.Need to see results no more than 30 minutes after data entry – as part of their processThey are controlling the Master Data, and need these changes reflected w low latency as wellPower Excel users Accounting and Operations Staff (170 cube users 250+ BOXI users)Includes first-line accounting which has been outsourced.Mostly concerned with reporting actual results at the most granular level of detailNeed to see results no more than 30 minutes after data entry – as part of their processBig Excel users

10/8/2010


BI Requirements

OperationalIntegrate all the source systems, conforming the coding conventionsMust support Regulatory Reporting (SOX Compliant)

– Systematic processes ensuring data quality– Source control (VSS)– Change control: Formal testing process w signoffs – Ability to re-create history the way it was reported

High-availability / Low Latency / 24-6 support for global usage AnalyticAllow fast, easy access to aggregate data, returning it to ExcelShow trends going all the way back in history+30 yrs out in forecast OverallConsistent metadata, single version of the truth, cleanse data to enforce data quality

10/8/2010


-- Identify MRI INSERTsSELECT * FROM [dbo].tbl_MRI_nowEXCEPTSELECT * FROM [dbo].tbl_MRI_before

UNION ALL

-- Identify MRI DELETEsSELECT * FROM [dbo].tbl_MRI_beforeEXCEPTSELECT * FROM [dbo].tbl_MRI_now-- If there is an INSERT and DELETE,-- remove the DELETE and-- make the INSERT an UPDATE

CDC via Table Compare


CDC via Triggers

-- Code snippet of trigger CREATE TRIGGER [dbo].[trg] ON [dbo].[FinancialsAccountDetail] FOR INSERT, UPDATE, DELETE AS DECLARE @ichange_type CHAR(1) IF EXISTS(SELECT * FROM inserted) IF EXISTS(SELECT * FROM deleted) -- --update = inserted and deleted tables both contain data -- BEGIN SET @ichange_type = 'U' SELECT * FROM inserted END -- U ELSE -- --insert = inserted contains data, deleted does not -- BEGIN SET @ichange_type = 'I' ELSE -- --delete = deleted contains data, inserted does not -- BEGIN SET @ichange_type = 'D'


CDC based on update_datetime


Alerting

• G/L Totals Checked Nightly

• “Listener” processes check for inactivity hourly

• If totals do not tie, or inactivity is detected, emails go out.


Hardware

10/8/2010

DW ETL and SQL ServersSQL Server 2005 SP2Compaq ProLiant BL460c G1OS: Windows Server 2003 Enterprise x64CPU: 8 Xeons @3.00GHzRAM: 32 GBStorage: EMC SAN, about 100 GB for .mdf’s, 100

for .ldf’s


Hardware

10/8/2010

Our failover solution uses PolyServe (HP)


Hardware

10/8/2010

Analysis Services ServerAS 2008 (SP1)Compaq ProLiant BL460c G1OS: Windows Server 2008 Enterprise x64 SP2CPU: 8 Xeons @3.00GHzRAM: 24 GBStorage: EMC SAN, 2 cubes taking up 10 GB


Master Data Management

10/8/2010

What Is Master Data?

Lists of data that are shared and used by several of the applications that make up the system. For example, a typical ERP system as a minimum will have a Customer Master, an Item Master, and an Account Master.

AMB uses EDM, (predecessor to SQL 2008 R2 MDS) for its G/L account hierarchies.

Good demo of MDS:http://www.youtube.com/watch?v=g1fZez5AJHY&feature=related



10/8/2010


Ass

ign

acco

unt

(Mov

er)

App

rove

assi

gnm

ent

(App

rove

r)

Roles for each hierarchy in the master accounts list

Cre

ate

new

acc

ount

gr

oup

& p

erfo

rm

reas

sig

nmen

ts(A

ccou

nt G

roup

Cre

ator

)

This is the user that will move NEW accounts that come from the source accounting systems (CTI & MRI) into an account group. They will receive an email when new accounts arrive into the +EDM master account list. They will also receive an email every morning with all the accounts not classified. If an account is not used in the hierarchy this user must move the account into the ‘Not Used’ account group.

This is the user that will approve ALL account moves performed by the Mover & Account Group Creator. They will receive an email when new accounts arrive into the +EDM master account list. They will also receive an email every morning with all the accounts not classified. They will receive as email when the Mover & Account Group Creator assign an account to an account group.

This is the user that will create new account groups and move accounts that already exist in the hierarchy to different account groups. i.e. The Mover moves new accounts into the hierarchy and the Account Group Creator performs any subsequent account moves.

10/8/2010



10/8/2010

EDM (MDS) Allows us to• Get a single version of the truth for our Master Data• Satisfy our SOX-compliance requirements…• Improve data quality by enforcing standards and workflows (used

to use an .xls for this)• Improve operational, reporting consistency• Enable Users to Securely Manage Master Data with Web-based

access• Provide an audit trail of changes for regulatory compliance• Flexibly add new data, hierarchiesMDS Wish List• Versioning by hierarchy, not model


Data Quality

Demo of MyData

10/8/2010


Reporting and Analysis - BOXI

10/8/2010


Data Quality

Demo of Cubes

10/8/2010


Analysis – Reasons for OLAP Success

• Updated incrementally, so it can become part of a business process• Active steering committees adjusting it as business processes change• Same data as BOXI (not re-ETL’ing)• Easy to use, especially with Excel users• Nice stream of upgrades, particularly w 2005 and 2008.• OLAP better suited to these calc’s:

Any-any currency, Budget vs Actual FX rates, Operating vs Ttl Variance Occupancy %, Projection, External Aggregate-level data Traffic-lighted KPI’s

• It’s a good sand box

10/8/2010


Generalizing

• Keep users in a sandbox to avoid bad queries, but make it a good sandbox

• Hard part: finding the sweet spot for analytics in your organization. But after you address it, move on the next one

• Try to do statutory reporting out of the source system – or you are getting into a big headache that will take you away from analytics

• Question requests that data be real-time10/8/2010


Q&A

10/8/2010

the amb data warehouse: a case study

Technology

amb data warehouse

data architecture

boxi data

data entry

external data

cleanse data

bad data

aggregate data