Here is what I would consider to be a magic post: http://richardlees.blogspot.com/2011/06/linkmember.html
Mr. Lees does the Lord's work here, and surely helped me out of a jam. I was trying to figure out a way to collect a single measure across multiple role-playing dimensions. Everything I tried was epic fail, including the solution I initially arrived at which was to create 3 separate queries and let SSRS sort them out....
To set the scene, an example of the problem I was having would be:
- I want to find the numbers of New Widgets, Sold Widgets, and Improper Widgets, by month over the last 12 months (Rolling 12 Month totals).
- In my SSAS cube I have a Measure for Widget Count, and I have 3 role-playing Date dimensions -- Released Date, Sale Date, Inspection Failed Date -- all based on a single Date dimension living in the underlying Data Mart.
- Appropriately filtering and slicing the Widget Count by any of the above dates yields correct results. But since the cube believes (techically properly, I guess) that those role-playing dimensions are all actually different dimensions, I cannot get a good slicer Axis out of only one of the date dimensions. So, in a nutshell
In other words, I couldn't get into a single query/dataset all 3 of the desired measures. This was because I really needed to query a single measure across 3 different role-playing dimensions.
So, in comes Mr. Lees. He introduced me to the magic of LinkMember(). And thus I quickly solved my problem. LinkMember allowed me to create a Calculated Member that gave me totals aligned with each of the Role Playing Dimensions I was looking at. Booyah, granny!
Showing posts with label Business Intelligence. Show all posts
Showing posts with label Business Intelligence. Show all posts
Thursday, November 15, 2012
Friday, December 2, 2011
RDL won't open in Design View in BIDS 2008? No problem.
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....
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....
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.
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. :)
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)