You may find yourself working on an SSRS report in BIDS 2008. You may ask yourself, "Why can't I re-open this report in Design View? Why is it this ugly line of non-formatted HTML?"
You may also find yourself behind the wheel of a large automobile, or living in a beautiful house, with a beautiful wife. But that's neither here nor there...
I have had this problem multiple times, and every time I have to look up the answer on the MS SQL Server forums. But today, they were down for maintenance, so I was up the creek. Therefore, I decided that once I refound the answer, I'd blog it, and provide links and a summary explanation.
This puts it in a nutshell, and is where I eventually find the answer (usually). So credit where credit's due: http://blog.hoegaerden.be/2009/03/28/the-datatype-attribute-is-not-declared/
And here's a brief explanation:
There is a (known) issue within BIDS 2008 where it will serialize a Report Parameter's XML incorrectly. If your report has an Available or Default value, BIDS will add an attribute called "DataType" in to one or more of your parameter's Value tags. This effectively invalidates the entire RDL, because the Value tag doesn't actually have that attribute. You try to open an RDL in Design View that's been saved this way, and the deserializer can't do it. Ka-boom. You receive a long horizontal scroll of unformatted HTML. Yay.
The solution is:
Simply delete those attributes where they are found.
Do a Find on "<Value DataType=" in the HTML and delete the attribute (and its value) wherever it turns up.
Hope this is helpful. Now that I've written this out, I will expect myself to remember it....
Friday, December 2, 2011
Monday, August 29, 2011
BI Estimation - "A more appropriate metahpor"?
http://peterjamesthomas.com/2009/03/18/a-more-approprate-metaphor-for-business-intelligence-projects/
That's so obvious it kills me that I hadn't thought of it before.
So this leads me to suggest a different metaphor for BI projects. Major elements of them are much more like archaeological digs than traditional building. The extent and importance of a dig is very difficult to ascertain before work starts and both may change during the course of a project. It is not atypical that an older site is discovered underneath an initial dig, doubling the amount of work required.
That's so obvious it kills me that I hadn't thought of it before.
Monday, August 8, 2011
Let the DB Handle It
Perhaps I'm pointing out the obvious, but software developers don't always seem to experience data the same way that, say, a DBA or a BI developer does.
I'm not saying that they don't process in rows and columns, or that they all have Dyscalculia or something. I'm saying that I frequently detect a reluctance in developers to let the database handle it. I theorize that this issue can come from a number of possible sources, but is usually a symptom of childhood trauma related to set-based math...
The rationale provided is often one that I consider an artificial barrier in this day and age, which is the old saw that "business logic can't go in the database". In my decade-plus of experience maintaining, designing and building business software, most of the databases I've encountered have been fairly inextricable from their front-end line-of-business apps from the beginning. And most of these apps are not going, say, make a switch between platforms or OS's without a wholesale rewrite of the code, anyway. So using PL/SQL packages or a set of T-SQL stored procs to help encapsulate logic is not a very serious crime. As long as it's documented, of course. Also, the best iron in the whole solution always goes to the DB machine. Why not leverage it to accomplish the work of the application? Save some clock and RAM utilization in your web farm and let the database do it.
Another scenario is when the RDBMS is considered a "Mystery Hole," where strange, nigh-impossible things happen in the darkness and hum of the server closet. I find this understandable enough from developers. I don't see much evidence that good data theory is being imparted on software dev students. And SQL itself, as a language, operates in a different paradigm from any procedural or OO language, and it requires those nasty set math skills to become accomplished with it. Advanced SQL techniques do take years to learn, and even to recognize when they can be brought to bear.
That said, a Pivot operation takes a lot more Java or C# code to implement than it does SQL. And frankly, being good with SQL means bever having to write a loop.
It makes sense (to the point of being mandatory, IMHO) for software developers to learn how data works, how SQL and databases work, and when you can leverage them for processing power by letting the database handle it.
A variation on this "Mystery Hole" thing is where it's not about being unfamiliar with the DB environment, but instead it's all about control. The developer simply can't have any of the logic NOT in code that she has her hands in. Maybe I'm wrong but, on a big enough project, that way lies madness, methinks. And again, it fails to heed the wisdom of "rendering unto Caesar that which is Caesar's." For certain tasks/functions, it's often more expedient and design-time and run time to just let the DB handle it.
Now, I have encountered on many occasions scenes where DBAs and application developers are at odds. Usually over who gets to do what in whose environment. In many of these, I wouldn't be surprised to find one of the syndromes above involved. But there are many times when the obstinacy and regimentation of a DBA will make developers want to punch them dead in the face rather than get involved in DB coding. So it's certainly a two-way street, and I'm not going to applaud overly-controlling DBAs any more than overly-controlling Developers. Clearly both parties are on the same team, and should be cooperating to solve the business goals they support, yes? Rather than playing parochial games over fictitious territories?
Maybe the fact that I'm partial to such compromise (or even averse to conflict) is why I ended up in the BI world. But as an application developer I always felt it was critical to have insight into the DB layer in order to be a well-rounded architect-level software professional. And if you could get it, working knowledge of the the network/infrastructure layer, too.
But let's not get ahead of ourselves. I suppose it's enough to expect that software developers know when to -- well, you know.
I'm not saying that they don't process in rows and columns, or that they all have Dyscalculia or something. I'm saying that I frequently detect a reluctance in developers to let the database handle it. I theorize that this issue can come from a number of possible sources, but is usually a symptom of childhood trauma related to set-based math...
The rationale provided is often one that I consider an artificial barrier in this day and age, which is the old saw that "business logic can't go in the database". In my decade-plus of experience maintaining, designing and building business software, most of the databases I've encountered have been fairly inextricable from their front-end line-of-business apps from the beginning. And most of these apps are not going, say, make a switch between platforms or OS's without a wholesale rewrite of the code, anyway. So using PL/SQL packages or a set of T-SQL stored procs to help encapsulate logic is not a very serious crime. As long as it's documented, of course. Also, the best iron in the whole solution always goes to the DB machine. Why not leverage it to accomplish the work of the application? Save some clock and RAM utilization in your web farm and let the database do it.
Another scenario is when the RDBMS is considered a "Mystery Hole," where strange, nigh-impossible things happen in the darkness and hum of the server closet. I find this understandable enough from developers. I don't see much evidence that good data theory is being imparted on software dev students. And SQL itself, as a language, operates in a different paradigm from any procedural or OO language, and it requires those nasty set math skills to become accomplished with it. Advanced SQL techniques do take years to learn, and even to recognize when they can be brought to bear.
That said, a Pivot operation takes a lot more Java or C# code to implement than it does SQL. And frankly, being good with SQL means bever having to write a loop.
It makes sense (to the point of being mandatory, IMHO) for software developers to learn how data works, how SQL and databases work, and when you can leverage them for processing power by letting the database handle it.
A variation on this "Mystery Hole" thing is where it's not about being unfamiliar with the DB environment, but instead it's all about control. The developer simply can't have any of the logic NOT in code that she has her hands in. Maybe I'm wrong but, on a big enough project, that way lies madness, methinks. And again, it fails to heed the wisdom of "rendering unto Caesar that which is Caesar's." For certain tasks/functions, it's often more expedient and design-time and run time to just let the DB handle it.
Now, I have encountered on many occasions scenes where DBAs and application developers are at odds. Usually over who gets to do what in whose environment. In many of these, I wouldn't be surprised to find one of the syndromes above involved. But there are many times when the obstinacy and regimentation of a DBA will make developers want to punch them dead in the face rather than get involved in DB coding. So it's certainly a two-way street, and I'm not going to applaud overly-controlling DBAs any more than overly-controlling Developers. Clearly both parties are on the same team, and should be cooperating to solve the business goals they support, yes? Rather than playing parochial games over fictitious territories?
Maybe the fact that I'm partial to such compromise (or even averse to conflict) is why I ended up in the BI world. But as an application developer I always felt it was critical to have insight into the DB layer in order to be a well-rounded architect-level software professional. And if you could get it, working knowledge of the the network/infrastructure layer, too.
But let's not get ahead of ourselves. I suppose it's enough to expect that software developers know when to -- well, you know.
Monday, August 1, 2011
Friday, June 3, 2011
"The Tip of the Spear"
When talking about information hierarchy with clients, I often use the analogy of the "tip of the spear". What I want to express to them is the idea that reporting is most effective when it answers the most important questions first. Once that's done, it's best to make available increasing levels of detail, on-demand. In other words, the focus of reporting should start narrow, and widen to include more specific data. The POV to focus on is that of the user, and their critical questions.
Of course, this isn't the way things develop organically. If left to itself, any reporting or data operation in a company will follow an "accretion" model, where functionality and capability gradually builds up in an organization as a function of time -- not a function of efficiently doing the job. Databases with unrelated clusters of tables that enter the system as functionality arises. Reports made to meet an immediate demand, but then left behind to be rebuilt later by someone who didn't realize the original existed. The scenario accumulates junk, just like a hippie's... well, you know. And so it is that the first step to avoiding or fixing this situation is in organizing a coherent information architecture.
Aim at "first things first." Identify the top users, the top data interaction workflows, the top metrics/KPIs, and the top reports. Then, you can proceed in order of priority down into the details of who needs to see what data, and how. Essentially, you want to put together something like chart of business metrics which might overlay an Org chart.
This "information hierarchy org chart" should help you identify who needs to see what data, at what detail and business level. It should help identify drilldown/drillthrough paths that might be necessary in resulting reports and/or dashboards. It should help shape the reporting offering being made at each level of the business. And it can also help prevent redundant efforts by showing where common views of data are required and how they are connected to other views.
You can usually expect that a summary level of KPIs can be decomposed into lower level metrics, with each lower level being of importance to somebody (if not everybody). So follow this trail from the data at the bottom up as well. Because it's somewhat likely that natural "fault lines" within the data will also exert an "upward" force counter to the business rules, that might actually suggest reporting elements that are not as easily revealed from a purely business perspective.
The goal is to arrive at a catalog of reporting elements aimed at answering the user audience's most important questions first, and then facilitating answers to the "why?" questions that come next. As an added bonus, the implementation of those elements can easily be organized into several different development workpaths that allow flexibility. Would the client like to implement the entire summary level first, to satisfy executive needs? Or is it more critical to build an entire "stack" of reports around one area, in order to fully explore that particular metric from summary level down to ultimate detail? Either direction is possible -- provided the appropriate foundational knowledge of the wider system goals is in place.
Of course, this isn't the way things develop organically. If left to itself, any reporting or data operation in a company will follow an "accretion" model, where functionality and capability gradually builds up in an organization as a function of time -- not a function of efficiently doing the job. Databases with unrelated clusters of tables that enter the system as functionality arises. Reports made to meet an immediate demand, but then left behind to be rebuilt later by someone who didn't realize the original existed. The scenario accumulates junk, just like a hippie's... well, you know. And so it is that the first step to avoiding or fixing this situation is in organizing a coherent information architecture.
Aim at "first things first." Identify the top users, the top data interaction workflows, the top metrics/KPIs, and the top reports. Then, you can proceed in order of priority down into the details of who needs to see what data, and how. Essentially, you want to put together something like chart of business metrics which might overlay an Org chart.
This "information hierarchy org chart" should help you identify who needs to see what data, at what detail and business level. It should help identify drilldown/drillthrough paths that might be necessary in resulting reports and/or dashboards. It should help shape the reporting offering being made at each level of the business. And it can also help prevent redundant efforts by showing where common views of data are required and how they are connected to other views.
You can usually expect that a summary level of KPIs can be decomposed into lower level metrics, with each lower level being of importance to somebody (if not everybody). So follow this trail from the data at the bottom up as well. Because it's somewhat likely that natural "fault lines" within the data will also exert an "upward" force counter to the business rules, that might actually suggest reporting elements that are not as easily revealed from a purely business perspective.
The goal is to arrive at a catalog of reporting elements aimed at answering the user audience's most important questions first, and then facilitating answers to the "why?" questions that come next. As an added bonus, the implementation of those elements can easily be organized into several different development workpaths that allow flexibility. Would the client like to implement the entire summary level first, to satisfy executive needs? Or is it more critical to build an entire "stack" of reports around one area, in order to fully explore that particular metric from summary level down to ultimate detail? Either direction is possible -- provided the appropriate foundational knowledge of the wider system goals is in place.
Friday, April 29, 2011
Hippie's Backyard Redux: Data vs Information
What's the difference between data and information?
It's not a trick question. Data are (or "is", depending on your grammarian tendencies) unadorned facts, while information consists of facts evaluated and/or interpreted to provide a particular meaning. It's the difference between plotted points on a graph, and a trend-line drawn to connect them. The points are interesting, but the line tells a story. Data points have value, but to really get answers you want information. Therefore in order to answer business questions, a typical reporting user needs and wants plenty of information at their disposal, all based on dependable data.
So then why is it that these users typically end up with a sea of data points, but are left on their own to derive any usable information?
Tough one...
I know there are some organizations that simply don't have the processes or mandate to find and fix inefficient processes -- including reporting processes. The scope and depth of a problem process simply sits in an organizational "blind spot". Northridge has been called in to "fix" some serious manual-extract, chicken-wire-and-duct-tape Excel situations, fed through a shadowy underworld of data connections hidden to the non-adept, where if users can get any kind of realistic data at all, they're thankful. Of course, if that kind of solution is already causing pain, you're too late for duct tape. But somehow the company can't see the larger-scale syndrome at work.
Also I've seen more than one "bottom-up" reporting system, where the shape and structure of the user-facing solution is based largely on the data and the structures it occupies, with little or no recognition of business-based reporting needs. In these situations, the user frequently must sift through arbitrarily-named (from their perspective) metrics, segmented in senseless ways (again, from their perspective) in order to get answers. Or they get data access along lines skew to the business roles they fill, restricting them along axes of data volume or visibility. "Data, data everywhere, nor any conclusions to be derived." Of course, by then time this has been built, there's neither budget nor guts to actually build a bridge to the users.
And of course you can never underestimate the sheer inertial power of "We've always shown it that way" ...
But in all cases, let's face it -- the goal is to get OUT of that situation where you're giving the right stuff in the wrong format, and the right people can't find it anyway. So, the key manuever is to evaluate one's reporting offerings through the lens of business context. Context, which sums up in one word the difference between data and information, gives users the background they need to derive conclusions, make decisions, and ultimately take action when they look at a report. In practical terms, you're showing users graphs with trend lines instead of grids of numbers. Using indicator graphics to show whether costs are up for current period over previous period. It also means organizing your reporting data in a way ammenable reporting needs.
Detailed data will still have its place since certain types of analysis may require a deeper look into the data behind an informational trend. But that's why the good lord invented drill-down logic. I refer to it as a "tip of the spear" strategy. You want to start with a summary, and allow further exploration along established data relationships. Click the "up vs down over prior period" indicator and it shows a grid-style report with daily details.
Accounting for users workflow-oriented interactions with data can help establish a more coherent and useful representation of data. This structuring of information based on users' needs is called Information Architecture, which is defined by the Information Architecture Institute as "The art and science of organizing and labeling web sites, intranets, online communities, and software to support findability and usability." And it can mean the difference between efficient and effective information delivery, and a hippie's backyard.
It's not a trick question. Data are (or "is", depending on your grammarian tendencies) unadorned facts, while information consists of facts evaluated and/or interpreted to provide a particular meaning. It's the difference between plotted points on a graph, and a trend-line drawn to connect them. The points are interesting, but the line tells a story. Data points have value, but to really get answers you want information. Therefore in order to answer business questions, a typical reporting user needs and wants plenty of information at their disposal, all based on dependable data.
So then why is it that these users typically end up with a sea of data points, but are left on their own to derive any usable information?
Tough one...
I know there are some organizations that simply don't have the processes or mandate to find and fix inefficient processes -- including reporting processes. The scope and depth of a problem process simply sits in an organizational "blind spot". Northridge has been called in to "fix" some serious manual-extract, chicken-wire-and-duct-tape Excel situations, fed through a shadowy underworld of data connections hidden to the non-adept, where if users can get any kind of realistic data at all, they're thankful. Of course, if that kind of solution is already causing pain, you're too late for duct tape. But somehow the company can't see the larger-scale syndrome at work.
Also I've seen more than one "bottom-up" reporting system, where the shape and structure of the user-facing solution is based largely on the data and the structures it occupies, with little or no recognition of business-based reporting needs. In these situations, the user frequently must sift through arbitrarily-named (from their perspective) metrics, segmented in senseless ways (again, from their perspective) in order to get answers. Or they get data access along lines skew to the business roles they fill, restricting them along axes of data volume or visibility. "Data, data everywhere, nor any conclusions to be derived." Of course, by then time this has been built, there's neither budget nor guts to actually build a bridge to the users.
And of course you can never underestimate the sheer inertial power of "We've always shown it that way" ...
But in all cases, let's face it -- the goal is to get OUT of that situation where you're giving the right stuff in the wrong format, and the right people can't find it anyway. So, the key manuever is to evaluate one's reporting offerings through the lens of business context. Context, which sums up in one word the difference between data and information, gives users the background they need to derive conclusions, make decisions, and ultimately take action when they look at a report. In practical terms, you're showing users graphs with trend lines instead of grids of numbers. Using indicator graphics to show whether costs are up for current period over previous period. It also means organizing your reporting data in a way ammenable reporting needs.
Detailed data will still have its place since certain types of analysis may require a deeper look into the data behind an informational trend. But that's why the good lord invented drill-down logic. I refer to it as a "tip of the spear" strategy. You want to start with a summary, and allow further exploration along established data relationships. Click the "up vs down over prior period" indicator and it shows a grid-style report with daily details.
Accounting for users workflow-oriented interactions with data can help establish a more coherent and useful representation of data. This structuring of information based on users' needs is called Information Architecture, which is defined by the Information Architecture Institute as "The art and science of organizing and labeling web sites, intranets, online communities, and software to support findability and usability." And it can mean the difference between efficient and effective information delivery, and a hippie's backyard.
Monday, April 18, 2011
Article link: Ten Mistakes to Avoid When Estimating ROI for Business Intelligence
I like this article, smart stuff:
http://www.bi-bestpractices.com/view-articles/4781
http://www.bi-bestpractices.com/view-articles/4781
Friday, April 15, 2011
Keep Politics From Overwhelming Your Project
Have you found yourself on a project trapped in a client political struggle? With no dog in the fight except your burning, itching desire to reach code complete and deployment? Well, you aren't alone. Consultants often occupy some unique political positions with respect to their clients' organizations.
Since we are not part of the company, we frequently have outsiders' perspectives. Over the course of our engagements, we survey how relationships work in these organizations, summarily critique the entire culture, and usually end up just shaking our heads. Other times we feel just as mired in tar pits as the client stakeholders we work with. Waiting on legal signatures, approval documents, an internal IT resource to complete a precedent task, etc. And of course, sometimes we defect because our clients' have better gigs... ;-)
Now, if you are one (like me) who specifically tries to avoid the whole subject of internal corporate politics then you must eventually realize, as I did, that in real life not everyone is completely earnest or imbued with a puritan work ethic. Learning how the political game works for your clients, and knowing who the players are, is imminently valuable even if you don't want to join in.
Because the fact is that our client companies' politics are effectively a force of nature when it comes to running projects. It behooves us to look objectively at this factor EARLY on in a project -- preferably before getting contracted into an intractable situation. I have learned through a long chain of missed tells and heartaches to pay close attention to the interpersonal dynamics going on with clients. Those sorts of things can reveal dangers to be avoided or otherwise defended against throughout the project. Yell-y bosses, ineffective or power-tripping gatekeepers, impossible-to-please approvers; you can often see it in the very earliest project meetings--if you're paying attention.
That said, it's frankly quite difficult to track all the various wavelengths of information transmitted by clients in early project meetings: technical context and detail, political context, business context, interpersonal relationship context. You need at least two people to effectively document/remember/witness it all. I think it even helps to record audio where possible, too. As throw-away lines your clients utter can be the missing piece that later keeps the puzzle from being completed.
No matter how it's done, the point is that you should harvest not only the technical details, but as much of the human context as possible. And keep those tidbits of info in mind as you proceed. They can aid you in project planning and in managing the overall relationship on a number of levels:
Now, if this sounds a little Machiavellian, or perhaps a little "Art of War"-ish, well... it is. Congratulations: this is consulting. You don't necessarily have to jump in and get your Iago on. But at least knowing about the game can help you play smart when you have to, and to avoid some potential pitfalls and obstacles to doing your "real" job.
Since we are not part of the company, we frequently have outsiders' perspectives. Over the course of our engagements, we survey how relationships work in these organizations, summarily critique the entire culture, and usually end up just shaking our heads. Other times we feel just as mired in tar pits as the client stakeholders we work with. Waiting on legal signatures, approval documents, an internal IT resource to complete a precedent task, etc. And of course, sometimes we defect because our clients' have better gigs... ;-)
Now, if you are one (like me) who specifically tries to avoid the whole subject of internal corporate politics then you must eventually realize, as I did, that in real life not everyone is completely earnest or imbued with a puritan work ethic. Learning how the political game works for your clients, and knowing who the players are, is imminently valuable even if you don't want to join in.
Because the fact is that our client companies' politics are effectively a force of nature when it comes to running projects. It behooves us to look objectively at this factor EARLY on in a project -- preferably before getting contracted into an intractable situation. I have learned through a long chain of missed tells and heartaches to pay close attention to the interpersonal dynamics going on with clients. Those sorts of things can reveal dangers to be avoided or otherwise defended against throughout the project. Yell-y bosses, ineffective or power-tripping gatekeepers, impossible-to-please approvers; you can often see it in the very earliest project meetings--if you're paying attention.
That said, it's frankly quite difficult to track all the various wavelengths of information transmitted by clients in early project meetings: technical context and detail, political context, business context, interpersonal relationship context. You need at least two people to effectively document/remember/witness it all. I think it even helps to record audio where possible, too. As throw-away lines your clients utter can be the missing piece that later keeps the puzzle from being completed.
No matter how it's done, the point is that you should harvest not only the technical details, but as much of the human context as possible. And keep those tidbits of info in mind as you proceed. They can aid you in project planning and in managing the overall relationship on a number of levels:
- allowing for slow-moving companies in your timeline
- selecting which client stakeholders you want to deal with and how often
- identifying your allies and enemies within the organization,
- identifying potential problem decision makers
- finding friends where it's advantageous
- saving you from gaffes that jeopardize your project
Now, if this sounds a little Machiavellian, or perhaps a little "Art of War"-ish, well... it is. Congratulations: this is consulting. You don't necessarily have to jump in and get your Iago on. But at least knowing about the game can help you play smart when you have to, and to avoid some potential pitfalls and obstacles to doing your "real" job.
Friday, April 1, 2011
Reporting: The "Hippie's Backyard" of Software
As your Business Intelligence consultant, acting in my capacity as a reporting architect, I'm afraid I have to inform you that your current Reporting "system" is what we call a hippie's backyard.
Just think about it a minute. Yes, it's a technical term.
Not important now. The important thing is, it's not working for you or your organization. At all. In fact, I'd be inclined to describe the situation as reversed; you're working for it. After all, how many man-hours do you waste annually on shuffling data in and out of Excel from one internal silo to another? What kind of a report is a 26-tab spreadsheet with 64,000 rows of 50 columns of 8pt font in each tab? And how many differing copies of those spreadsheets are currently strewn across network and thumb drives? How many are the same? Why didn't the developers of your Inventory system include any basic reporting functionality?
I'm just asking. You don't have to get defensive.
Listen, I know how it goes: At first, there was supposed to be a set of reports included in the initial release. But then the timeline got stretched, and you ended up with no real process, no real tool to get anybody any reports, just totally ad-hoc data requests fielded by whoever can do it whenever they can. So then Ted in Accounting sets up an Excel thing that ties to the application database, people start using it, and data grows. Versions propagate. Probably 6 or so around the network, some of them like 3 years old. Two years pass, more users get on board, and the "Financial Data Mart" (as it's become known) has maxed out on file size, crashes itself at least once a session, crashes the Inventory application daily, and runs like cold molasses. But there's no time to do anything about that. Besides, Ted quit 6 months ago. Who's going to do it?
Well, isn't that really why you called me?
No, no -- you shouldn't feel bad. The same phenomenon is EVERYWHERE. Reporting is the great red-headed stepchild of the IT world. Ask any true-blue hacker how much they like building reports... But here's the way to think of this:
At a certain level of organizational size and complexity (which will be different based on industry, culture, and other factors) a simple ad-hoc reporting system is insufficient. You can't run it with a single person fielding 20-30 slightly different requests for the same data. That setup doesn't scale, and can't handle the request traffic, can't handle the data volume, can't take the heat. And truthfully, the organization will waste money trying to resolve the issue by throwing labor at it. The solution is to consider the problem holistically and strategically, and to provide a robust, organization-wide reporting solution. The end goal is to establish a solution that provides all workers the information they need to do their jobs -- on-demand.
How? Well, first things first. We need to do a broad inventory of prioritized reporting needs -- who needs to see what when, and what format works best for them. That inventory is analyzed to generate a global information architecture, which organizes the various categories of information based on user need and natural hierarchies within the organization. Next, the Data Structures at work in the organization must be analyzed to determine their usefulness vis a vis the information architecture, their accessibility, and their availability. When we bring the results of this analysis to bear on the Information Architecture, we can map specific information needs directly to data sources, we can design a truly centralized reporting solution.
And, yes, we get your company out of the hippie's backyard.
So, now let's talk a little about just how many report requests are being made, by whom, and what information is in them. And then we can talk about where all of that data comes from...
Just think about it a minute. Yes, it's a technical term.
Not important now. The important thing is, it's not working for you or your organization. At all. In fact, I'd be inclined to describe the situation as reversed; you're working for it. After all, how many man-hours do you waste annually on shuffling data in and out of Excel from one internal silo to another? What kind of a report is a 26-tab spreadsheet with 64,000 rows of 50 columns of 8pt font in each tab? And how many differing copies of those spreadsheets are currently strewn across network and thumb drives? How many are the same? Why didn't the developers of your Inventory system include any basic reporting functionality?
I'm just asking. You don't have to get defensive.
Listen, I know how it goes: At first, there was supposed to be a set of reports included in the initial release. But then the timeline got stretched, and you ended up with no real process, no real tool to get anybody any reports, just totally ad-hoc data requests fielded by whoever can do it whenever they can. So then Ted in Accounting sets up an Excel thing that ties to the application database, people start using it, and data grows. Versions propagate. Probably 6 or so around the network, some of them like 3 years old. Two years pass, more users get on board, and the "Financial Data Mart" (as it's become known) has maxed out on file size, crashes itself at least once a session, crashes the Inventory application daily, and runs like cold molasses. But there's no time to do anything about that. Besides, Ted quit 6 months ago. Who's going to do it?
Well, isn't that really why you called me?
No, no -- you shouldn't feel bad. The same phenomenon is EVERYWHERE. Reporting is the great red-headed stepchild of the IT world. Ask any true-blue hacker how much they like building reports... But here's the way to think of this:
At a certain level of organizational size and complexity (which will be different based on industry, culture, and other factors) a simple ad-hoc reporting system is insufficient. You can't run it with a single person fielding 20-30 slightly different requests for the same data. That setup doesn't scale, and can't handle the request traffic, can't handle the data volume, can't take the heat. And truthfully, the organization will waste money trying to resolve the issue by throwing labor at it. The solution is to consider the problem holistically and strategically, and to provide a robust, organization-wide reporting solution. The end goal is to establish a solution that provides all workers the information they need to do their jobs -- on-demand.
How? Well, first things first. We need to do a broad inventory of prioritized reporting needs -- who needs to see what when, and what format works best for them. That inventory is analyzed to generate a global information architecture, which organizes the various categories of information based on user need and natural hierarchies within the organization. Next, the Data Structures at work in the organization must be analyzed to determine their usefulness vis a vis the information architecture, their accessibility, and their availability. When we bring the results of this analysis to bear on the Information Architecture, we can map specific information needs directly to data sources, we can design a truly centralized reporting solution.
And, yes, we get your company out of the hippie's backyard.
So, now let's talk a little about just how many report requests are being made, by whom, and what information is in them. And then we can talk about where all of that data comes from...
Wednesday, March 30, 2011
Required Reading II: Electric Boogaloo
I wanted to follow up my previous "required reading" post with a more BI/Consulting directed set of books you should be reading/have read.
The Data Warehouse Toolkit - This is a effectively a Kimball Method primer, from the source himself. This gives a great survey of the lifecycle and methodology, not to mention a breakdown of dimensional modeling and all it entails. Coming into BI as a developer, this is where I really learned about the thinking behind ETL processes, and it gave me answers to early challenges in warehouse/datamart modeling.
The Microsoft Data Warehouse Toolkit - This is the Microsoft-flavored cousin of the previous book, written by Joy Mundy and Warren Thornthwaite (based on the existing book). The newest version is current through SQL Server 2008 R2, and therefore incorporates discussion/demonstration of some of its cooler new features (the delicious Merge statement, new SSRS goodies, etc.)
Agile Database Techniques - this is a personal favorite, just because I think the Agile methods in general can be applied with success to some types of BI/DW projects. How's that for a qualified statement, huh? Seriously, though: experience the pattern of requirements change on a few consecutive projects, and tell me BDUF isn't contrary to BI goals.... Anway, Scott Ambler is the Agile mouthpiece, so it's great that this book is his.
Getting Started in Consulting, Million Dollar Consulting - Have to recommend Alan Weiss for consulting books. His materials are a lot more utilitarian than many business books. Less cheese-moving, more concrete examples of Things You Should Do. The two titles listed above are the ones from which I got a lot.
OK, that's enough reading for now. :)
Wednesday, March 16, 2011
SQL Server File Size management
Here's a link I found that was super-helpful in the course of doing some analysis/investigation for a client:
http://www.simple-talk.com/sql/database-administration/managing-data-growth-in-sql-server/
Great DBA advice and techniques for managing file sizing and growth in SQL Server. I was able to use a bunch of this information when I evaluated the client's situation.
Monday, March 7, 2011
Required General IT Reading
The vast field of IT books has a slew of timeless classics in it, all of which should be read by anybody intending to be worth their salt as a technical professional.
But in fact, I seem to encounter less and less discussion/mention of these kinds of books as the blogosphere inflates and the Tweets zip overhead.
So, a few of those in particular I wanted to mention here, as a curmudgeony sort of move:
The Mythical Man-Month (Frederick Brooks) - This is a classic about the human aspects of developing software. You need this, critically. Buy it now.
Code Complete (Steve McConnell) - The definitive distillation of programming style and consideration. For your colleagues, for the future, for God's sake -- read this and absorb it if you ever do any coding. And don't think you know better. Because you probably don't. Sorry.
An Introduction to General Systems Thinking, The Secrets of Consulting, Becoming a Technical Leader: An Organic Problem-Solving Approach (Gerald M. Weinberg) - Gerry Weinberg is one of the great unsung heroes in the history of software development. All his books are chock full'o'wisdom for any kind of systems developer or engineer. Not to mention his consulting expertise.
The Inmates Are Running the Asylum (Alan Cooper) - A definitive treatise about bad user interface design in modern technology. Must-read. Really, this has saved me some rewrite work over the years.
Design Patterns: Elements of Reusable Object-Oriented Software (Richard Helm) - A little more down in the details than the rest of these tomes, this one is valuable not just for the specific patterns described, but for illuminating the whole idea that there ARE patterns in the software game...
So check these out. All of them definitely affected my thinking and behavior over the course of my career -- in positive ways. Next time, some more specific BI and Consulting book recs.
Wednesday, March 2, 2011
An Introduction
Ahoy there, I'm Andy Tegethoff.
Lothario, roustabout, no-good-nik, bass player, consultant. Call me what you will. :)
I have a loving family consisting of a wife and two boys, and I've got about a dozen years in the IT game in Metro Atlanta, with the last 4 of them spent as a Business Intelligence consultant. Prior to that I did software development in a number of environment and across several platforms. Chiefly, from a technical perspective, I was a solid VB/C# guy who knew programming for both Oracle and SQL Server better than most. I spent time in the trenches, survived Y2K and the DotCom flameout; I'm one of the "good ones" in IT.
Not that I am a techie by birthright. I got my BA in English Lit/Psychology, and have been a would-be rock star for more like 20 years. I was always more of a Drama or Band geek than a Math geek. However, as it turns out, the TRS80 CoCo I got for Christmas when I was 9 shaped my destiny beyond allowing me to write a Light Cycle videogame....
But as Dr. Evil would assert, "the circumstance of my upbringing are quite inconsequential." Neither here nor there. A topic for a later post (I can't give it all away now). The overall point is, once I entered the working world I found quickly that I could do tech work and yet interact comfortably with actual human businesspeople. Which is a bit of an edge it seems.
In 2006 I started work for a consulting company called Northridge Systems (a Microsoft Partner with practices specializing in custom Dev, SharePoint, creative design, and BI). While I began at NR doing web and Windows dev work, the opportunity arose for me to join the firm's nascent BI team. So after examining my options, I jumped at it. As I said previously, I was the SQL expert in most programming situations in my career, anyway. Plus I was burnt out on pure dev, and wasn't seeing a clear career path that I liked from that position.
BI, on the other hand, seemed relatively glamorous, and well-suited to my particualar strengths. I liked the "mo-money" aspect for sure, and I also liked the prospects career-wise. But it turns out it was kind of a "through the looking glass" type thing. Like parenting or owning a luxury yacht. I didn't really understand my situation until I was in the middle of it. And it took some getting used to.
But now I've feel like I've really found a professional niche, one reasonably safe from outsourcing due to cultural necessities, and narrow enough to be either unappealing or unattainable to many in the industry. I like doing it, and I think I have a great aptitude for consulting. BI work is much more strategic than the Dev work I used to do, and the communication aspects are so critical, that I feel like it really fits my skill set well.
So I've decided to start a blog where I can potentially share my insights (for what they're worth) from my BI consulting experiences. The blog title (with tongue securely in cheek) is pretty accurate. I'm really aiming to keep this largely above the technical fray, and more about the "philosophical conundra" of BI. I think so much of what BI is about gets lost in those details that I'd like to make it about what role business intelligence can/should play in the enterprise, how to make it work, etc.
So we'll see how it goes. Hope to see you around. Perhaps later we'll enjoy pie.
Subscribe to:
Posts (Atom)