looking for input about our BI initiative

I'm relatively new at BI, and I'm looking for input or suggestions anyone may have.

My company is in the business of manufacturing and sales. I was hired on, at first as a contractor, to write reports, but we had no reporting database or data warehouse to query.

When I started here, I was given a large textfile, extracted from the OLTP production system, to normalize into a reporting database. It worked well, and we were able to create reports that ran with good performance.

During the course of the year, our parent company bought more similar companies, and it became necessary to create what we called a global sales data warehouse. Some of the companies involved used the same OLTP system, but those that didn't were required to give us a similar flat textfile, which was not always easy for them. This was to be done as quickly as possible, but it was about 6 months of design, loading, and corrections before the users were reasonably confident in the data we provided for them.

I wanted to create a truly dimensional DW, but I was pressed into getting it done ASAP, so what we have now is another relational (3NF) data mart or data warehouse.

Now, it's been in place for about 18 months. I really would like to do another iteration as a dimensional DW, but I'm so busy creating reports (and cleaning up and maintaining reports my boss wrote) that we have no time budgeted for this. I also have spent quite a bit of time dealing with funky tables, views and procedures my boss has written - often cleaning up the structure, renaming columns and tables to align them with our naming convention, and changing the load scripts.

My boss has gotten really quite good at SQL, although he uses the visual designer for report queries and views, and I often end up cleaning up and maintaining these, which is often harder than just rewriting them! There have been a number of Mondays during which I find that he's added various questionably-designed tables and views during the course of the weekend.

My responsibilities here:

- maintain a handful of SQL Servers

- create/design databases (I'd like to do more of this!)

- write and maintain ETL code (we just use SQL scripts for this)

- fix load issues

- write reports (SQL Server Reporting Services)

- maintain reports

- clean up my boss' questionably-written-but-quite-complex-and-needed-right-now reports, and help him when he gets stuck

- connect with my counterparts at the other companies when there are data issues

So basically I feel like I'll never get caught up.

Comments or suggestions?

Hi there,

Interesting post and it sounded like a position I was in a few years back. Here are some initial thoughts...

1. Your boss as well meaning as he is should not be releasing code (i.e. tables, views etc) to a production environment. Ideally you need a development environment for this which he can play in. Once he's happy with his code he should be passing it to you so take a look at and if it's not up to scratch I'd send it back with some suggested changes before it goes live. That teaches him as well as giving you a bit of time. The stuff that's needed right now sounds like reports unless you need the extra tables / views to supply data to those reports.

2. Wanting to build a fully dimensional data warehouse is no small thing and I can see by your responsibilities you've already got your hands full. Something like this should be driven as a company initiative (if you don't get the business to buy into the concept it's never going to work) as changing all your reports etc to read from the dimensional data warehouse I suspect would be no small task. Do some research on dimensional data warehousing and come up with a case on how it can improve things for the company. Once you've got that go to your boss and explain your proposal, then that's something he can take to the CIO / CEO. Perhaps you need another staff member (contractor) to take care of the day to day stuff while the data warehouse is built, a well written business plan highlighting the benefits / ROI is the best thing you can do.

We use SQL 2005 with a product called WhereScape RED. Essentially RED is an ETL tool which makes developing dimensional data warehouses quite quick. It does not have a steep learning curve and the first time I used it I thought they had designed it while reading Kimballs dimensional data warehousing book.

From there RED can create the Analysis Services cubes and we just access that through Excel. Simple solution, perhaps you can prototype something for the boss and throw it into a cube if writing business cases are not your thing. Once you've got your boss acknowdgeling the benefits of creating the dimensional datawarehouse perhaps he can write the business case.

A bit of a brain dump I'm sorry but if you want clarification on a point I'll be happy to help.

Cheers,

Chris

Chris is right; you're stuck in a never-ending cycle. Unfortunately if the business is not on board with a BI initiative (at the C-level) then these projects tend to never get off the ground. While at another company I was working on a warehouse project sponsored by the CEO. He was fired and the project went into limbo. It would be pulled out, dusted off, and worked on for a couple weeks and then it was back on to the next fire. Six weeks would pass and we'd do the whole thing over again.

It sounds like you're in a wild west environment. I've found it's nearly impossible to build a consolidated data warehouse under those conditions. A good warehouse really requires a certain level of maturity in your shop. If you can implement some kind of change control and peer review process with your boss that can save you a lot of time and headache. After that you two can start trying to sell the warehouse to someone higher in the organization who can sponsor the project and procure funding.

High-level sponsorship is the lifeblood of your project. If it doesn't get funding the warehouse as a project will almost always fail.

Having said that, you might be able to get approval from your boss to build something off to the side. It would be slow going given all your responsibilities but it would be something you could learn from and hopefully release in the future. Ultimately though you'll only be able to learn and grow so much from that and eventually you'll need more. It might then be necessary to augment your skillset by going to work for a different company if your current company hasn't matured enough by that point. I had to make a few jumps between employers because I wasn't getting what I needed where I was.

Just some thoughts. Hopefully this helps a bit.

Chris,

Thanks for the response.

Regarding #1 below, really one of the main issues is that my boss is a bit out of control. He and I have discussed the change migration situation, and he's getting better about it, but he still can't stay out of production - plus, he feels like he needs to be "in there" when I'm busy with another report, or out of the office. Plus, he has set the bar of expectation too high.. many reports that he does are reports that use fields we don't have in the DW, so since he knows the OLTP system pretty well, he just dumps out the data and starts working on them.

We also have a cube that he created one weekend with the assistance of a book; the load job doesn't run, though. I have to admit, I wasn't exactly happy about this - I'm busy trying to keep my head above water.

Chris Rutherford wrote:Hi there,

Interesting post and it sounded like a position I was in a few years back. Here are some initial thoughts...

1. Your boss as well meaning as he is should not be releasing code (i.e. tables, views etc) to a production environment. Ideally you need a development environment for this which he can play in. Once he's happy with his code he should be passing it to you so take a look at and if it's not up to scratch I'd send it back with some suggested changes before it goes live. That teaches him as well as giving you a bit of time. The stuff that's needed right now sounds like reports unless you need the extra tables / views to supply data to those reports.

2. Wanting to build a fully dimensional data warehouse is no small thing and I can see by your responsibilities you've already got your hands full. Something like this should be driven as a company initiative (if you don't get the business to buy into the concept it's never going to work) as changing all your reports etc to read from the dimensional data warehouse I suspect would be no small task. Do some research on dimensional data warehousing and come up with a case on how it can improve things for the company. Once you've got that go to your boss and explain your proposal, then that's something he can take to the CIO / CEO. Perhaps you need another staff member (contractor) to take care of the day to day stuff while the data warehouse is built, a well written business plan highlighting the benefits / ROI is the best thing you can do.

We use SQL 2005 with a product called WhereScape RED. Essentially RED is an ETL tool which makes developing dimensional data warehouses quite quick. It does not have a steep learning curve and the first time I used it I thought they had designed it while reading Kimballs dimensional data warehousing book.

From there RED can create the Analysis Services cubes and we just access that through Excel. Simple solution, perhaps you can prototype something for the boss and throw it into a cube if writing business cases are not your thing. Once you've got your boss acknowdgeling the benefits of creating the dimensional datawarehouse perhaps he can write the business case.

A bit of a brain dump I'm sorry but if you want clarification on a point I'll be happy to help.

Cheers,

Chris

Brian,

Thanks for your response; it's always good to see you online, and I must disclose to the other readers that we've also worked together, albeit too briefly!

What you mention below ("build something off to the side") is quite probably what I'll have to do. I've certainly got a better handle on how the business uses the sales data at this point, so that should make it a bit easier to implement.

Looks like I need to find a way to manage my boss as well.

BrianJarrett wrote:Chris is right; you're stuck in a never-ending cycle. Unfortunately if the business is not on board with a BI initiative (at the C-level) then these projects tend to never get off the ground. While at another company I was working on a warehouse project sponsored by the CEO. He was fired and the project went into limbo. It would be pulled out, dusted off, and worked on for a couple weeks and then it was back on to the next fire. Six weeks would pass and we'd do the whole thing over again.

It sounds like you're in a wild west environment. I've found it's nearly impossible to build a consolidated data warehouse under those conditions. A good warehouse really requires a certain level of maturity in your shop. If you can implement some kind of change control and peer review process with your boss that can save you a lot of time and headache. After that you two can start trying to sell the warehouse to someone higher in the organization who can sponsor the project and procure funding.

High-level sponsorship is the lifeblood of your project. If it doesn't get funding the warehouse as a project will almost always fail.

Having said that, you might be able to get approval from your boss to build something off to the side. It would be slow going given all your responsibilities but it would be something you could learn from and hopefully release in the future. Ultimately though you'll only be able to learn and grow so much from that and eventually you'll need more. It might then be necessary to augment your skillset by going to work for a different company if your current company hasn't matured enough by that point. I had to make a few jumps between employers because I wasn't getting what I needed where I was.

Just some thoughts. Hopefully this helps a bit.

tung wrote:Thanks for your response; it's always good to see you online, and I must disclose to the other readers that we've also worked together, albeit too briefly!

St. Louis IT is a small world, isn't it?

It's almost impossible to affect change from the bottom up. I've seen "grassroots" efforts fail time and time again. Personally I'd do what I could to learn as much as possible, however I could, for as long as I could and then look for a new job if things haven't changed for the better. At some point I think you have to cut your losses and make the move that's best for your career and your family. Building a warehouse project off to the side could provide you with a great learning opportunity and could provide your company with a beneficial reporting tool in the end. You can also augment your resume once you've built something.

I made several jumps along the way (as you know from our short duration together at VH) and I've finally ended up in a really, really good shop. It took years of paying dues and hanging on during a bumpy ride but all the hard work has finally paid off. It takes time, effort, and determination. We're building a full-blown, fully-funded data warehouse using Kimball methodologies and I'm serving as data warehouse architect on the project.

I hope things work out for you. Keep us posted on how it goes.

I don't think a side project is the right answer. You should try to convince your boss that a new solution is needed. Point out the risks associated with changes on the fly and complex hand coded sql. Think about the audit exposure if the business is using this database to publish financials. Unfortunately, this is usually not enough. In addition to this you should try to identify several new capabilities that would be possible with a new solution. Possibly a better audit trail, improved data quality, or increased dimensional history. The key is to find one that has the business value to cover the re-development cost. Since you have already been down the requirements road, you should be able to re-engineer to a dimensional model in a few months.

I also agree that side project isn't the right answer but it might be the only way to make it happen. If it never sees the light of day, and you don't let your deadlines or performance suffer as a result, then you've at least given yourself some experience that you can leverage to make your next job move.

All the ideas cbusch mentions are great, provided you can get management on board. Unfortunately the bottom-up approach for initiatives of a large magnitude is rarely successful, in my experience at least. I'd definitely try to convince management why it make sense to do things the right way and what the right way is. But if all else fails you have only yourself to fall back on and if you chase your tail for the next two years instead of gaining BI experience you could find yourself stuck where you are without any options.

Even though not being from St.Louis and not being from the US at all ;-) i'll second Brians suggestions.
I would look for trying to convince the management to do a proper project. In the meantime start and try to build something for "yourself". If it works out and is a solution for your company show it to them. If it's going wrong take the learnings and make it better next time.

In the end you have tried everything to help your current company and yourself and maybe then it's time to step on the next level in another shop.

Cheers
Mario