spaghetti spreadsheets untangled - wordpress.com€¦ · afbeeldingsresultaat voor linkedin logo....
TRANSCRIPT
![Page 1: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/1.jpg)
Spaghetti
Spreadsheets
Untangled
Benefits of decision modeling
when uncovering complex
business logic hidden in
spreadsheets
Charlotte Bouvy MSc
DecisionCAMP 2019 Bolzano
![Page 2: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/2.jpg)
Intro speaker
• MSc Cognitive Artificial Intelligence (Utrecht University, The Netherlands)
• 20+ years experience in bridging the gap between business and IT (knowledge engineering, business rule management, business architecture)
https://www.linkedin.com/in/charlottebouvy/
![Page 3: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/3.jpg)
Sociale Verzekeringsbank (SVB)
• implements national (social) insurance schemes in the Netherlands• child benefit, AOW pension, Anw survivor benefit
• personal budget scheme under Social Support act
• mission• excellent service
• stable flexible organization
• close cooperation with chain partners
![Page 4: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/4.jpg)
BRM at SVB
• Business Rule Management key in their business innovation
• ‘wizards’ support the 1500 case workers with knowledge intensive decisions in the primary process
• decision modeling manages complex rules
• a multidisciplinary team speeds up development
• cost-effective business case speeds up change process
![Page 5: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/5.jpg)
Our case
• Operational control and audit departments of SVB
• Account periodically towards ministries• To comply with the law that structures the execution of work and income
regulations
• Lawfullness• Were the assessments done according to the laws and regulations
• Did every citizen receive the amount he/she was entitled to
• Reliability• Is the financial statement a faithful representation of the actual payments
who
why
what
![Page 6: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/6.jpg)
Problem #1
• Knowledge scarce… and about to walk away
![Page 7: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/7.jpg)
Problem #2
• Spreadsheets applied everywhere..
… but error prone
DILBERT © Scott Adams. Used By permission of ANDREWS MCMEEL SYNDICATION. All rights reserved.
![Page 8: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/8.jpg)
• What We Know About Spreadsheet Errors
Ray Panko, professor IT managementauthority on bad spreadsheet practices“the issue is how many errors there are, not whether an error exists”
• European Spreadsheet Risk Interest Group collects horror stories
• How a spreadsheet fail lead to questionable global fiscal policy
Spreadsheet errors
http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htmhttp://www.eusprig.org/horror-stories.htmhttps://www.nytimes.com/2013/04/19/opinion/krugman-the-excel-depression.html
![Page 9: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/9.jpg)
Problem #3
• Dealing with the domain specific business logic details
![Page 10: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/10.jpg)
Our solution: SARA
• Statistical Application for Analysis of Lawfulness (‘Rechtmatigheid’)
• Replaces different spreadsheets
• Modeled in Oracle Policy Automation
![Page 11: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/11.jpg)
3 main benefits of SARA
1. Faithful representation of calculation and decision knowledge
2. Data separated from calculation and decision model
3. Model is both ‘single source of truth’ and versatile implementation
![Page 12: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/12.jpg)
Benefit #1 faithful representation of knowledge
• Semantics semantics semantics
• Concept model
• Transparency (less assumptions)
• Explicit unambiguous names
• Levels of abstraction
• Logical decomposition
![Page 13: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/13.jpg)
Before SARA
![Page 14: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/14.jpg)
Our solution: SARA
![Page 15: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/15.jpg)
SARA high levelDomain
Law
Law
Stratum Stratum Stratum
Sample Sample
Stratum
Sample
Stratum Stratum
Sample Sample Sample
• Stratified sampling
• Estimation on level of strata
• Aggregation on law and domain level
• ‘base level’ rule:
error = SOLL - IST
• Calculation modes
• ‘absolute’ for lawfulness
• ‘netted’ for reliability
estimation
aggregation
![Page 16: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/16.jpg)
Modeling calculations rules vs spreadsheet
the percentage of lawfullness for the stratum =
100 - the percentage of unlawfullness for the stratum
the percentage of unlawfullness for the stratum =
the estimated error amount for the stratum / the IST amount for the stratum
the estimated error amount for the stratum =the estimated SOLL amount for the stratum – the IST amount for the stratum
![Page 17: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/17.jpg)
the inaccuracy for the stratum = Sqrt(1 – the square of the correlation between IST and SOLL for the stratum) * the t-value for the stratum * the standard deviation of the SOLL for the stratum * (the number of cases in the stratum / Sqrt(the number of cases in the sample in the stratum)) * Sqrt((the number of cases in the stratum - the number of cases in the sample in the stratum) / (the number of cases in the stratum - 1))
less straightforward
![Page 18: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/18.jpg)
Modeling calculations and decisions
![Page 19: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/19.jpg)
Modeling calculations and decisions
![Page 20: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/20.jpg)
Benefit #2 separation of data and model
• Manual data copy paste actions cause spreadsheet errors
• Extraction, transformation, loading of data (from different sources) are different responsibilities
• Important: data sets with enough coverage to test the model• Business scenario’s as acceptance criteria
![Page 21: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/21.jpg)
Benefit #3 executable model
• Model is both ‘single source of truth’ and versatile implementation
• Mature, scalable, secure, stable, repeatable
• Service oriented
• Version control, change management
![Page 22: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/22.jpg)
Integrated with case management solution Octopus –SOAP webservices to load input
and save results
Deployed as web interview -data input by RPA
Input screens
Result screen
Modeling and testing business scenarios in OPM
Different ways to execute SARA
![Page 23: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/23.jpg)
Lessons learned
• Spend time with business and SME’s to discover together the value todeliver ∞
• Make a business vocabulary
• Without business scenario’s for acceptance testing a user story is not ‘ready for sprint’
∞“Discover to Deliver” by Ellen Gottersdiener & Mary Gorman
![Page 24: Spaghetti Spreadsheets Untangled - WordPress.com€¦ · Afbeeldingsresultaat voor linkedin logo. Sociale Verzekeringsbank (SVB) •implements national (social) insurance schemes](https://reader036.vdocuments.us/reader036/viewer/2022062415/5fc7354414a68707ba230f64/html5/thumbnails/24.jpg)