Well, it's only been a month since my last posting. That's not great (I'm hoping to get it down to a couple weeks or so), but it's better than it was before. And honestly, I have an excuse; I got married! Crazy, I know, but I can't help it. My wife (weird) is the coolest person in the world. I'm a lucky guy. Anyway, onto business.
Presumably, you've purchased Argos and are preparing to migrate legacy reports from your legacy system for use in your new reporting tool. This is excellent news! The first opportunity presented to us as a result of this process is a chance to take an inventory of your legacy reports and to DEPRECATE (yes, I said it) reports that are no longer used or will become obsolete with your new system. As you can imagine, if not handled well, this can be a very difficult process for some people. So, before beating down the doors of your office mates and telling them their reports are going away in 3 months, take a deep breath and simply make a list of everything that exists in your legacy system, when it was last run, how often it's run, and whether it needs current data or can be run against a data warehouse (if available). Organize them by area of ownership/responsibility (such as Records, Admissions, Financial Aid, Finance, etc), then organize a meeting with the decision makers in each of those areas to prioritize their list. That meeting will be your first opportunity to broach the subject of deprecation. By the way, I highly recommend NOT using the word deprecate (or any form of it). Gently encourage each of your clients to recognize the fact that they have not executed or requested report x for 6 months and that they say they're supposed to be running it once a week. Ask them if they still need it and if the answer is "Yes", ask them why (again, gently...try not to sound like you're accusing them of anything). Finally, make sure that they understand that "that's the way we've always done it" is not an acceptable answer.
After you've eliminated as many unnecessary reports as possible, it's time to prioritize. Luckily, this doesn't really fall on you (generally speaking). Work with your clients again to determine which reports are most important and, where possible, set reasonable deadlines. Now comes the fun part, and the second most important aspect of your conversion: consolidation. One of Argos' key strengths is it's ability to combine similar requests into a single object, called a DataBlock. Imagine a situation in which your first four reports relate to student information. If you were to encapsulate the data required for said reports into a single DataBlock, then set your report writers onto that DataBlock, you've just reduced your overall workload by 75%! You're probably grumbling to yourself "well...how often does that actually happen."
I was recently working with a client whose anticipate conversion load was around 450 legacy reports. After elimination, prioritization, and consolidation, I reduced the expected number of DataBlocks to 78. And this is generally how things go! Seeing a reduction in expected output of 50% or more is pretty typical! So, you might be asking yourself "how" one can consolidate to such a degree. Firstly, it has to do with technology. Generally speaking, older reporting tools were designed to facilitate one report per "designable object" (what we refer to as a DataBlock). As such, it meant expansive menus and large amounts of development resources. BI tools continue to evolve and are now smarter than ever. That said, when I work to consolidate like reports, I stick with a concept commonly referred to as "level of granularity". By this, I simply mean the level at which you do not want duplicate records to appear. Some common examples include: student, course, financial transaction, financial account, financial aid award, and prospect. Each of these items has certain attributes that can be reported and often are reported in situations in which you only want one record per item in your report. That is your "level of granularity". Here's an example. Let's say one of your reports has to do with students majoring in Psychology. The report needs the students name, id, and email address. Obviously, you don't want more than one row per student, so this is a case where the "student" is your level of granularity. Now, all we have to do is figure out how to achieve that level and then "bolt on" the appropriate attributes (in this case, ID, name, and email). It's that simple!
Alright, I think that's enough for now. This post should have given you some ideas on what I consider the most important aspects of report conversion. This, in conjunction with my post about development best practices, should put you well on your way to solving all the worlds problems. Happy Hunting!
Tuesday, June 1, 2010
Wednesday, April 28, 2010
Create a Function Library - Decrease Intellectual Angst and Increase Efficiency
Just like your typical code monkey, I'm constantly looking for ways to reduce my required intellectual output while increasing my overall efficiency. For example, right now, I'm walking on a treadmill, listening to my wedding rehearsal dinner play list to make sure it's all the same feel/volume (aka sans cuss words:)), and updating a blog that I haven't written on in 4 months (I'm very sorry).
As far as coding goes, I have been doing a lot of work with schools who are moving with increasing speed and fervor toward data-based decision making. This requires a much more analytical approach to reporting than your typical "students by major" list, which, in turn requires more database resources and involves a great deal more "logic" with regard to your institution's business rules (often the hardest part). To help aid me in this transition, I have started to compile a library of functions. For the most part, the functions are relatively simple, I just add one whenever I need to answer a new question. For example, I was recently asked by a client what percentage of their new freshmen this fall enrolled full time. My old "hack" self would have sat down at a computer for a couple of hours and written the necessary query to gather that information. BUT WAIT! This time, I wrote a database function that took as parameters term, student type, and time status. Now, to get my pct, this is the query:
select f_enrl_count('201010','N','FT') FT_NewFreshmen,
f_enrl_count('201010','N','%') Total_NewFreshmen,
f_enrl_count('201010','N','FT')/
f_enrl_count('201010','N','%')*100 Pct_New_FT
from dual;
It's now that simple! Not only is the query simplified, the overall results come back quicker because I am using pre-compiled code and I can use it from any query tool that connects to this database! Functions can be combined too. For example, if you're asked the percentage question a lot and you're tired of writing the query above, you could encapsulate that code in a function called f_enrl_pct that would look like this:
create or replace function
f_enrl_pct(in_term varchar2, in_styp varchar2, in_time_status varchar2)
return number as
v_output number;
begin
select round(f_enrl_count(in_term,in_styp,in_time_status)/
f_enrl_count(in_term,in_styp,'%')*100,2) into v_output
from dual;
return v_output;
end;
Now when you want that percentage, all you have to do is write the following:
select f_enrl_pct('201010','N','FT') from dual
and WALAH, you're done! How sweet is that. Now, you're probably chomping at the bit for the code behind the enrollment function. Unfortunately, because of the variety of architectures (Baseline, ODS, EDW, home grown data marts), I would rather not post the code here. However, if you're really interested post a comment and I'll think about sharing:). Also, if you have developed some neat functions and would like to share, I welcome your comments there as well! Anyway, the moral of today's story is two fold:
1. Writing a blog entry while on the treadmill takes way longer than it's worth
2. Creating a function library may be the smartest thing you ever do as a sql programmer
As a side note, functions can be used for non analytical reports as well! More on that later...:)
Happy Hunting!!!
As far as coding goes, I have been doing a lot of work with schools who are moving with increasing speed and fervor toward data-based decision making. This requires a much more analytical approach to reporting than your typical "students by major" list, which, in turn requires more database resources and involves a great deal more "logic" with regard to your institution's business rules (often the hardest part). To help aid me in this transition, I have started to compile a library of functions. For the most part, the functions are relatively simple, I just add one whenever I need to answer a new question. For example, I was recently asked by a client what percentage of their new freshmen this fall enrolled full time. My old "hack" self would have sat down at a computer for a couple of hours and written the necessary query to gather that information. BUT WAIT! This time, I wrote a database function that took as parameters term, student type, and time status. Now, to get my pct, this is the query:
select f_enrl_count('201010','N','FT') FT_NewFreshmen,
f_enrl_count('201010','N','%') Total_NewFreshmen,
f_enrl_count('201010','N','FT')/
f_enrl_count('201010','N','%')*100 Pct_New_FT
from dual;
It's now that simple! Not only is the query simplified, the overall results come back quicker because I am using pre-compiled code and I can use it from any query tool that connects to this database! Functions can be combined too. For example, if you're asked the percentage question a lot and you're tired of writing the query above, you could encapsulate that code in a function called f_enrl_pct that would look like this:
create or replace function
f_enrl_pct(in_term varchar2, in_styp varchar2, in_time_status varchar2)
return number as
v_output number;
begin
select round(f_enrl_count(in_term,in_styp,in_time_status)/
f_enrl_count(in_term,in_styp,'%')*100,2) into v_output
from dual;
return v_output;
end;
Now when you want that percentage, all you have to do is write the following:
select f_enrl_pct('201010','N','FT') from dual
and WALAH, you're done! How sweet is that. Now, you're probably chomping at the bit for the code behind the enrollment function. Unfortunately, because of the variety of architectures (Baseline, ODS, EDW, home grown data marts), I would rather not post the code here. However, if you're really interested post a comment and I'll think about sharing:). Also, if you have developed some neat functions and would like to share, I welcome your comments there as well! Anyway, the moral of today's story is two fold:
1. Writing a blog entry while on the treadmill takes way longer than it's worth
2. Creating a function library may be the smartest thing you ever do as a sql programmer
As a side note, functions can be used for non analytical reports as well! More on that later...:)
Happy Hunting!!!
Subscribe to:
Posts (Atom)
Posts
