P is for Practical | |
PerlMonks |
Re: Microsoft Analysis Server - Data Cubesby richardX (Pilgrim) |
on Dec 01, 2002 at 20:04 UTC ( [id://216799]=note: print w/replies, xml ) | Need Help?? |
I have built a system similar to the one that is being discussed. The base system that drove the entire application was the Data Warehouse (DW). Creating a DW properly, with efficient reporting in mind, is a huge thread in itself. The next major piece of the puzzle was the Extraction Translate and Load application (ETL). We wrote our own ETL tool because we needed a data driven application that non-technical people could use to map data from external applications to the DW. There are many ETL tools on the market if you need a commercial one. The final piece of the puzzle is the reporting. We pre-built OLAP cubes that would answer the most common questions that users would ask of the DW including the drill downs. It would be a huge challenge to create dynamic OLAP cubes that could be constructed on the fly of any fields that the user desires. Their are so many requirements that you have to concern yourself with when building an OLAP cube, including what your facts and dimensions are, the size of the tables that you are extracting from, are the fields indexed, and many other concerns. We had to manage the expectations of our users, to let them know that we could build addition cubes but that allowing them to create anything they wanted was not acceptable. Runaway processes on the database server were the biggest concern. The end result was that we did not allow users to create cubes, only execute them. The users accessed the cubes from a web page that asked for date ranges and other parameters. The application then ran the cube, and exported the results to Excel, HTML report, or email. We provided each user with training on how to manipulate the Excel pivot tables and graphs once they had the data they needed. Power users then took the Excel data and created tons of their own reports, graphs, and analysis. So it was impossible to anticipate all the requirements for Power users, so we let them run with the extracted data without further supervision. For more mainstream users, we created a Digital Dashboard using Microsoft’s Digital Dashboard toolkit. Users could then access the cubes from the Dashboard after logging in and authenticating themselves. We used Role Based Access Control (RBAC) to control access to reports, with each role giving access to specific cubes. Our analysis also identified the most common reports that multiple users and or departments would use on a daily and weekly basis. On a scheduled basis, those cubes were run at night and the reports exported to HTML and stored into folders for canned reports. Some RBAC users only could see these canned reports and never access cubes directly. This prevented the servers from being overwhelmed by cube processing that was redundant. We purposely did not constrain which programming tools we used, to give us the maximum flexibility. We used Perl, batch files, Excel macros, Digital Dashboard calls, HTML, SQL, stored procedures, multidimensional expressions (MDX) scripts, and any applicable Perl modules like DBD/DBI etc. Most MS programmers do not know about the hidden power of MDX. I would recommend learning every thing that you can about MDX, since it talks to the services directly and most efficiently. Maybe you could write a Perl module that controls MDX scripts :} Richard
In Section
Seekers of Perl Wisdom
|
|