Recently I was helping someone debug an Analysis Services Multidimensional project, and didn’t come up with much to help the performance. Why? The underlying data model was completely unwieldy and the fix, which no one wanted to do, was to redo it completely. Having worked recently with a number of business analysts to migrate there Excel spreadsheets to Power BI to support the growing trend to Self-Service Business Analysis, has made me think a lot about what makes a project a success.  Self-Service BI has been hyped as the way that analysis can better do their job and not involve technical resources.  While I support the move to the Analysts being more involved in with the data to make good decisions using the data, these kind of projects still need experienced data professions help them make a the project a success. There isn’t a tool which can fix a project with a bad data model. The problems the analyst have are not so much with learning the tool, as Power BI was designed to be easy to use. The problem is with data modeling.

Reporting Views; Modeling for the Moment

A lot of business reporting is developed by using the following process, which you may find where you work. The database team can’t keep up with the report requests, so they create a number of views and provide business analysis with some tool, be it Report Builder, Excel or Access to gather the data to do reports. This method provides the ability for analysts who don’t know much about data modeling to create reports based on the information is provided. This process works for a while. As long as the data people need to do their jobs is provided, reports are created and the Database team doesn’t have to be involved. This whole methodology starts blowing up over time. Why? The reporting time starts to increase.

The Reporting Time Explosion

Once I was working at a company where the person in charge of doing the performance reporting went on an extended medical leave, and trained someone else on what was required to get the data and create the reports. She gathered data from this system and that system, added in some information on a spread sheet, ran some macros did some queries, updated some Excel spreadsheets and after that the reports were generated. This process required three hours every day to do this complicated series of task and a full week for monthly reporting. It took all of about two days for her replacement to be overwhelmed, and the task of doing the reports came to me. After a week, I had gathered all of the data together for the daily reporting and automated it, which took the daily reporting process from three hours a day to seconds of computer time. It took a couple of stored procedures, some SSRS reports and a new process for storing the data not in an Excel Spreadsheet, but in the application where it was supposed to be entered. By the end of the second week, the monthly reporting was completed as well. A task which took the majority the time person spent her day, was automated to button clicks in less than two weeks. Why? The task of gathering the data was given to someone who understood databases and data modeling. That’s the knowledge that is needed to set up a successful BI Project.

Business Knowledge needs to be combined with Technical Knowledge

To be an expert at something takes time and focus. There are only so many hours in a day, and if you are focused on spending those hours on creating technical solutions, you are bound to get really good at applying technical knowledge gained to solving problems. Likewise, if you spend all of your day looking at the data trying to solve business problems and answer questions about how the decisions made impact the data, you are going to get really good at analyzing business data. Tools help provide the ability to answer questions, which can be answered because the data model supports the type of analysis needed. To figure that out, someone who knows about data modeling needs to be involved to ensure the Self Service business intelligence project has a good foundational data model.  If that’s not there, it doesn’t matter what the tool is, the project won’t be successful.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur