Access to data, what is it like at other places?

sfca · Post by **sfca** » Wed Dec 05, 2012 12:21 am

I have questions about database administration, responsibility, and what could be considered normal at other financial institutions. Hopefully someone has some insight. I apologize in advance for how long this is, but if it were easy, I would not be asking. First, the background:

Assume there exists a fixed income investment portfolio. A relational database exists that represent the holdings of corporate bonds, munis, and various derivatives. The analysts and PM's responsible for managing these holdings use a GUI written by the IT group to get access to the holdings. They can click on drop down boxes or enter text in combo boxes to get slices of the data. The GUI does not provide bulk data download. There is a download capability to send the GUI screen data to Excel. If the analyst/PM wants a different slice of the data, they need to submit a service request to IT, have it approved and wait a month or two for an enhancement.

In my view, doing portfolio level analytics and even much industry/single security analysis should require direct access to the database. For example, the GUI will not produce time series data. I believe analysts should be able to query the data using SQL, download it to Excel, and do whatever analytics without IT involvement. However, the IT group has refused to provide front office access to this data except through the GUI.  A request to set up a copy of the data for use (call it a developemnt database) was passive/aggressively refused by providing a very low quality product.

I think the IT manager is controlling the supply of data in order to artificially increase his group's importance. His staff count has increased to handle the requests, and as one knows, increased staff count in a bank can increase the manager's income. So, my question is whether analysts and PM's at other firms are confined to using GUIs to look at their data.  Or, do they have direct access to the data. I have noticed in various jobs postings that SQL is commonly mentioned at money management firms, and I suspect it is because they have direct access to the data.  Is this as messed up as it looks? And, if this is as bad as it looks, is there some kind of reference or something on paper I could point to that shows this is not normal or acceptable at other places?

Jim · Post by **Jim** » Wed Dec 05, 2012 1:17 am

I've seen this conflict between flexibility on one hand and control on the other hand play out differently at various places. Big banks tend to emphasize the control side of thing; smaller firm (like hedge funds) are often like the Wild West where anything goes.

The point of view from the IT side of things you appear to missing is that when there are numerous SQL queries running wild outside of the IT group you get two big problems: 1) If you need to change database structure (for a variety of reasons), you have no idea of how many queries you are going to break. Some queries are not used for months but when the are used the data needs to be there right away (say for some client or upper management presentation). Queries under the control of the IT group can be tested and fixed. It becomes hit or miss whether or not any other ad hoc queries remain valid. And 2) Outside users often don't understand the performance characteristics of the queries they run. This can bring an entire server to its knees or cause it to crash, thereby affecting a lot of other users.

I would suggest discussing the problem with the IT manager to see if there is a compromise solution. I would think that they ought to be able to develop an API which delivers data in bulk (in say a .csv file), but that otherwise controls the access. One example to bring to the IT manager is the customer API from 1010data.com.

YukaRedux · Post by **YukaRedux** » Wed Dec 05, 2012 1:56 am

Sounds like an opportunity for your IT manager to further increase his headcount with a Front Office Data Liaison hire...

sfca · Post by **sfca** » Wed Dec 05, 2012 2:36 am

Thank you both very much. It helps to know that it is not just this place where the conflict exists. That all by itself is signficant. The IT manager does not have any experience doing this sort of thing anywhere else so I got the impression he was just making it up as he went along. Pushing for a bulk data download application from IT would get me closer to what I want than where I am now.

tristanreid · Post by **tristanreid** » Wed Dec 05, 2012 3:19 am

From a regulatory standpoint IT is often made to not allow 'production' access, it's more than a practical/political consideration. That said, some people just aren't motivated to help. Try asking for a 'reporting database', and explain that it will be used only by your group, with no downstream applications.

As Jim mentioned, you don't want to have a bunch of work pointing at something, and then they do some backoffice upgrade and all your stuff gets hosed. The way to avoid that is to have your own database that you can use, that will have your procs/views/other code, but will also have your tables. If you're in an Oracle environment, see if you they know about materialized views, which basically means you get a view to the production database that will pull automagically into your db. In SQL you can do this with more of a standard replication or ETL approach.

If you're going with SQL, you set up a database, call it "STAGING", and then you can do your own stuff in "REPORTING", and you copy stuff regularly (and under your control) from STAGING->REPORTING. You'll be able to write/scrub/reorganize the data in REPORTING, but if you put something in STAGING it will be overwritten the next time the information gets brought over. Your ETL procedures will still fail if the table structure gets changed, but you'll have all of that in one place, your code will still operate on its last successful copy.

Looks like this:

PROD db (under control of IT, you have no direct access)
ETL from PROD to STAGING (controlled by IT)
STAGING (you have read-only access)
ETL from STAGING to REPORTING (controlled by you)
REPORING (under control of you, but ask IT to perform backups)

PM me if you have issues/pushback with this. I speak IT.

-t.

london · Post by **london** » Wed Dec 05, 2012 3:23 am

My group has been down a similar path.

Initially we had OUR SQL databases, and there were the main production databases. The two were completely separate. On ours we could do what we wanted, but we didnt have access to the IT ones.

There was also very little contact between my group and IT except when something broke.

The steps (that have taken about two years):

1. We requested a regular daily extract, then stored it daily (so built our own time series)

2. We then got access to a replicated version of the their data, so we could run any queries without risking slowing up some production server. The replication happened hourly, which was good enough for most things.

3. We now have a shared development server where changes are made, and we can check our downstream dependencies don't break.

4. Finally, they write stored procedures on the production server for us if we need real time data. Since they control the stored procedure they are offically part of the code amd considered as changes happen.

All of this could have been achieved alot quicker i know.i guess it was a case of people being the limiting factor rather than technology.

Things that helped: I got a number of them interested in execution and trading and sent books to their home address. It was sneaky, but it worked as intended and they wanted to help me. Compliance always asks about gifts to clients, they don't mention colleagues ${L_SMILIES_WINK}$

We had several lunches and gossiped about the company history (from them) and what was going on in the front office (from me).

Conclusion: now the teams work much closer and progress is faster. Originally I viewed them as an impediment to be navigated around, now they are definite allies and genuinely want to help.

[Edit: cross post with TR, and he says it far better!]

Praetorian · Post by **Praetorian** » Wed Dec 05, 2012 12:08 pm

> I got a number of them interested in execution and trading and sent books to their home address

Social engineering at it's best Big Smile

signalseeker · Post by **signalseeker** » Wed Dec 05, 2012 3:36 pm

> Things that helped: I got a number of them interested in execution and trading and sent books to their > home address. It was sneaky, but it worked as intended and they wanted to help me. Compliance

> always asks about gifts to clients, they don't mention colleagues ${L_SMILIES_WINK}$

> We had several lunches and gossiped about the company history (from them) and what was going on in the front office (from me).

That is genius!

tristanreid · Post by **tristanreid** » Wed Dec 05, 2012 7:52 pm

LOL - in fact london, you said it far better than me.

[edit: In fact, my career in relation to phynance is largely the beneficiary of people like you]

-t.

sfca · Post by **sfca** » Wed Dec 05, 2012 8:33 pm

Thank you very very much to all. This exactly hits the spot. This place is great. The office politics part of this is also exactly correct and a lesson I just don't seem to ever learn. With 20/20 hindsight it was not a good idea to estimate in millions how much money this inefficiency is costing us and share it with management.