Problem:
Large amount of data in spreadsheets: Every month data from SQL Server created 40 spreadsheets with 30+ pivot tables (each) and up to 100,000 rows.
Why it mattered:
Spreadsheets were very difficult to create, maintain and share, no time dimension, only monthly snapshots.
Characters:
- IT Manager
- Pricing Manager
- District Managers in North America
Attempts at Resolution:
Keep information in SQL Server and generate reports using SSRS.
Solution:
Create views and formulas in SQL Server and use ETL to export data to IBM Cognos TM1 cubes.
Summary:
The information can easily be distributed via browser, Excel add-on and TM1 directly. Users can create their own views and look at the information by changing filters, rows and columns. The monthly export into one container (TM1) also added a time dimension so information can be viewed over a period of months or years, not just one spreadsheet at a time.