November 25, 2010 at 4:34 am
Hi,
I am looking for some opinions 😎 I am working at a client who currently does not have any true "BI" in place (but are hoping to) but has several EXCEL spreadsheet models, most of which get data from a number of sources (most of which are not centrally stored/or even uploaded in a database) and require several other spreadsheets as inputs.
In effect, spreadsheets are being used manually to do all sorts of data manipulations and transformations ie repivoting data, formulas, V-lookups.
I have been asked to write a proposal on a specific model (comprising many different data sources and spreadsheets) as the business has become aware that trying to create reports from multiple spreadsheet inputs with lots of embedded logic and data sources all over the place (in a database, stored on someone's laptop) is time-consuming and not delivering any transparency. :w00t:
Overall I am convinced that really all is required is a data organisation excercise. Their is no need for real-time reporting and a lot of the issues can be solved by:
a) Making information available in 1 place
b) Automating the transformations and calculations in SQL
My main line of expertise is in datawarehousing and I think in this case, a simple data mart would solve their issues. However I am writing a proposal where I have been asked to outline several solutions and I am wondering if a "data mart" might be overkill... :w00t:
They are not really looking for a flexible reporting engine but really a replication of a specific model. They also may be short on time and it is not clear a data mart would be cost effective.
Any ideas on other solutions that could be proposed? I am thinking that there will need to be an ETL solution of some kind.
Note that the Client uses Lotus Notes but is otherwise heavily reliance on Microsoft ie EXCEL (used heavily), SSIS, SSAS and SSRS and doesn't have much else in terms of BI tools that could be considered. They do have Crystal Reports but they are very against it and in any case their primary issue I feel is they way data is integrated ie not at all. In cases where they have data uploaded, the answer to reporting has been to create loads of different SQL Server Views (that are just written for very specific ad-hoc needs) but I personally see this as part of the issue.
November 26, 2010 at 1:11 am
Well, they certainly need a BI solution 🙂
They have already made a good step by recognizing they are in Excel-Hell.
If I were you, I'd build a data warehouse. Data warehouses don't need to be complex, just a couple of fact tables and dimensions can suffice. I'd also suggest the Kimball method. Start small, build one success, and expand on that one using the bus architecture (aka conformed dimensions). Even if this solution is "overkill" for the moment, you are at least building a solution that can go on for a couple of years. Who knows how the company will change?
So, I'd use SSIS for the ETL, it is supreme in combining data from various sources. Build your data warehouse with this tool. Then you can build a SSAS cube on top of it, it kind of depends on the data and on the user requirements. You can build SSRS reports on relational or OLAP reports, so do some interviews to find out what they want. But I'd suggest to build one small sample SSAS cube, just to show its capabilities. Often the business doesn't know this tool good enough, so they don't mention it in their requirements because they don't know what it can do for them.
You can link SSAS with Excel, so they will certainly love that 🙂
Good luck and let us know how it goes!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 26, 2010 at 10:32 am
Thanks for this. I probably will recommend a data mart for this.
They definitely need to integrate the data sources and I could implement ETL to build the report they want (without needing a data mart). However for the extra design effort involved may as well create something that will provide them with a reporting engine/ foundation for cubes/ foundation for data warehouse..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply