Washington DC is opening up its budgets, will you?

Sonny Hashmi, Deputy Chief Information Officer, OCFO at DC Government, sent me a note highlighting the new CFOInfo DC Dashboard, which is in Beta. This dashboard is exposing details of the District of Columbia’s budget to a degree I have never seen before, and making it easy for your average user to dig in and see how money is being spent. Amazing!

I sent Sonny a few questions, questions he was happy to answer. Give a read, let me know if you’d like to know more.

Q. Did you bring in external expertise to design and architect the data warehouse?
A. Our data warehouse was built over the past 3-4 years in an incremental fashion. We have been using it for internal budget analysis and it drives all financial reporting including the yearly audits. Implementing CFOInfo on top of the data warehouse involved designing and implementing a data mart. The infrastructure and data model was primarily done in-house. We relied on some external Cognos expertise during the development of the dashboard.

Q. What technologies are you using (SQL, Oracle,etc), for the warehouse?
A. Our data warehouse is a very large multi-dimensional database that includes data from many sources including financial, tax, procurement, HR and budget data. The database resides on a multi-node Oracle Cluster. We re-architected our entire BI layer to be an internal center of excellence for all financial reporting. It is a multi-tiered solution that uses Cognos 8.4 as the BI engine.

Q. If another city/town wanted to replicate what you have done, what would it cost?
A. It all depends on your current technology stack. In our case, the data warehouse and BI solution was already in place. We just leveraged the existing investment. It took one calendar month to implement CFOInfo from concept to go-live with some external cognos expertise.

Depending on the complexity and format of the underlying data source (Excel spreadsheets? ERP? Home Grown systems? Mainframe?), the most challenging aspect of any BI project including CFOInfo is the data modeling. Data modeling for BI solutions is somewhat different from a traditional operational database. The schema and normalization is key to system performance. That is when Data Marks become very useful.

Depending on the state of current systems, data and existing data warehouses, cost would be determined by if the jurisdiction has internal data modelers or external resources are needed. I would estimate a total of 4-8 weeks of database development and modeling effort would be about average.

The other component of the solution is the BI solution. An advanced BI solution (such as Business objects, Cognos, OBIEE) provides a flexible foundation to develop and deploy interactive reports and dashboards quickly without a lot of development needed. These systems are typically expensive, but if business intelligence and enterprise performance management is part of the strategic plan, its a worthwhile investment, especially to enable transparency, gov20 and open data initiatives. High end solutions can cost anywhere between $150,000 – $500,000 in licensing costs alone.

The good news is that there are a variety of mid-market and lower tier solutions available in the market place. Gartner has extensive industry research in this area. Although these solutions do not have the same level of analytics and presentation options (no converged meta data, etc.), for specific targeted projects such as CFOInfo, they may fit the bill for smaller jurisdictions.

There is also an insurgence of open source and cloud based solutions entering the market such as google analytics, yahoo mashups and variety of other more specialized solutions. these solutions are great for very targeted presentation solutions, but if the jurisdiction wants real-time analytics, or tight control over presentation and flexibility, these may not fit the bill.

An alternative approach is to implement dashboards using in-house coding using open standards (such as J2EE or .Net with Flash/Google charts API. It is a low cost solution but requires high level of internal skill. The downside of this approach is if business requirements change frequently, and if solutions are needed to be agile, this approach requires development and redevelopment of solutions to keep pace with demand. Moreover, there is a high risk of data integrity issues, bugs and security risks.

Each jurisdiction needs to make these decisions based on budget availability, internal skill set, and strategic direction.

Q. How many different data sources is this data being pulled in from?
A. Our internal data ware house pulls data from HR, procurement, financials, tax, budget and grants management systems. However, CFOInfo currently only displays data from the budget and financial systems.

Q. Is the data updated in real-time (or near real-time)?
A. CFOInfo currently only contains budget data (as well as past year actual expenditures). By nature of the business process, this data does not change day to day. proposed budgets only change at defined milestones in the yearly budget process. Past year actual expenditures and approved budgets don’t change.

However, the technical architecture allows for daily data refreshes for data that does change constantly. As we roll in new data sources, this would allow for the dashboard to be updated daily.

Q. I love the current drill-down reports, is the data available in XML, CSV, or some other developer-friendly format?
A. Currently, data is available to be exported in a variety of formats including CSV. Since the current data set does not update very often, it should prove sufficient for people to create their own analyses, especially in the “tabular” or detailed view.

There are several policy and legal risks involved in enabling real time financial data reporting that are often overlooked. Financial data must be accurate and consistent to drive informed policy and decision making. The risk of raw data misrepresentation, misinterpretation or misuse can carry great consequences such as misinformed policy decision making. This risk must be carefully analyzed, understood and mitigated when exposing raw financial data.

Q. How detailed will the data be? For example, in my town people love discussing what the chief of police or the school superintendent makes, will you be able to get to the individual salary level, to the individual purchase level?
A. We have financial and HR data at this level available in our internal data warehouse and is used internally all the time. We also provide individual’s salary information in PDF format online at http://grc.dc.gov/grc/lib/grc/foia/publicbodyinformation.pdf . If budget and business priorities demand, this information can be presented in an interactive fashion.

As I mentioned earlier, there are legal and policy implications that needs to be carefully worked out when embarking on any such initiative. Questions such as “When do the numbers become official? Which system is the system or record? How does one reconcile the numbers reported to the public and privately recorded elsewhere that may not always agree?”. These issues must be resolved. The technology is the easy part.