Self-Service BI – Will it fit our need?

I left the big-time (or so Raleigh seems compared to Goldsboro NC) and took a job with a super-regional distribution company. I was the first “specialist” to be hired into a four man IT shop. We had a server administrator, a help desk, a Progress developer and the boss man. As you can imagine in this environment it didn’t take long for my time to be pulled away from Business Intelligence development and over to other projects – new software implementations, troubleshooting, planning, etc.

So much need that it has been 9 months now since I have had time to develop any business intelligence projects. Enter a new job title, Systems Architect, while still trying to keep the old one, BI Architect, and you understand why the question popped into my mind – can we use Self-Service BI to more rapidly fill the needs? The needs are great – we are a sales driven company and our ERP database doesn’t allow much slicing or dicing of data. In every meeting I see needs for mining data, for showing better reporting, for BI. Yet week after week, I put in too many hours, move forward projects but none of them my BI projects.

This is why, much to my dismay I started considering Self-Service BI. Abandoning my precious multidimensional cube and looking to a new approach. My only exposure to Self-Service BI had been a few presentations during SQL Server User Group meetings and the new features of SSAS 2012 presentations I attended at SQL PASS 2011.

It occurred to me, 2 weeks ago, during a planning meeting that unless I changed how we do things I could never change how we do things. I need to get the data out of the incredibly ill-designed database our company runs on and expose it to the analysts. Most of my development time has been wasted trying to understand the relations between tables, the fields and their meaning and what would be relevant for exposure. Fairly normal BI tasks – but this database, designed by a non-IT individual is about the worst I had ever seen. Through the years so much clean-up and data handling was implemented in code that I stumble for weeks finding the right data for dimensions and measures. We do have business analysts assigned to each functional area. While they wait for the BI to reach their area they have been grabbing data, often by running reports and keying the data into Excel sheets to share.

Wait – the analysts are already manipulating the data. What if I exposed the tables to them in a tabular model? I wouldn’t need to fight with them writing SQL Queries, they wouldn’t have to wait for me to build the cube and even better – after playing with the data they could share the workbooks back with me. This way I would know the fields, the formulas, the lookups and the cleaning they did. I could be doing my other projects and then be productive with my time available to develop BI. The more I considered this, the more I realized that leveraging the business analysts’ time to help me was the only way I could move forward.

True maverick style this Tuesday I decided to test my theory and expose some tables. I opened my development server and tried to create a tabular model project. Of course I failed! But a little time searching the web and I was able to start the Analysis Services engine, and ultimately to put it into tabular mode. The next step was easy – import a table, then another and another. My goodness but was I on a roll. I thought, “This is it”!

Then the bubble burst. My poorly designed database always has a minimum of two column joins between tables. Many joins are 4 or 5 column. It didn’t take me long to realize that my technique wasn’t flawed, my knowledge was just lacking. Not one to give into pessimism I haven’t given up. I joined some tables and brought them in. This means that for my situation I will have to use surrogate keys and heavy ETL manipulation before bringing in the tables. I will basically need views or integration packages to create a data warehouse. I am still thinking this part through but I do not see how I can join the tables without surrogate keys. A customer is unique to the company ID column and the customer number column. The order is unique to the company ID column and the order number column. This can’t work in tabular. Can I use concatenated fields to represent a join column? Can I place each company’s information in a unique model table? I honestly don’t know.

Which brings me to this – I don’t know. I don’t know how this is going to turn out. I don’t know if I will move back to multidimensional development. I don’t know if Self-Service BI is right for our company.

I do know that once again I need to hit the books, set up a lab and re-educate myself. That is the thing about this profession we love – it is always changing. Time for me to make some changes, so that I can make some changes.

I thought a blog series might be interesting as I document my thought processes, disappointments, discoveries and ultimate solution. It also will help me get into the habit of blogging and get me back into sharing with my awesome SQL community! Wish me luck because tabular models – I’m coming for you!