Often times there is no cube, no OLAP, no BI plan but someone wants reports that provide rollup data, data slices, sums, and even dimensional representations that are oriented toward the BI world.  Of course building these in SQL is possible, even easy sometimes, but it slams the processing of an RDBMS.  When working through these various SQL Queries it becomes a little more obvious, especially when the data rows number in the hundreds of thousands, exactly why OLAP and BI exist.

The following are the literal operators that provide cube like functionality within T-SQL.  However there are many issues with attempting to use T-SQL to get Cube sliced data and definitely issues with performance.  If the database is OLTP and has transactions occurring it is probably best to not even attempt queries with these operators.  It would be kind of like database server suicide.

ROLLUP – Rollup Is an aggregate function that creates an additional column of output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP.

Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.

CUBE – The CUBE operator generates a result set that is a multidimensional cube. A multidimensional cube is an expansion of fact data, or data that records individual events. The expansion is based on columns that the user wants to analyze. These columns are called dimensions. The cube is a result set that contains a cross tabulation of all the possible combinations of the dimensions.

The CUBE operator is specified in the GROUP BY clause of a SELECT statement. The select list contains the dimension columns and aggregate function expressions. The GROUP BY specifies the dimension columns and the keywords WITH CUBE. The result set contains all possible combinations of the values in the dimension columns, together with the aggregate values from the underlying rows that match that combination of dimension values.

SUM – Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored. May be followed by the OVER clause.

COUNT – Returns the number of items in a group. COUNT works like the COUNT_BIG function. The only difference between the two functions is their return values. COUNT always returns an int data type value. COUNT_BIG always returns a bigint data type value. May be followed by the OVER clause.

…and my favorite, for complexity reasons.

COMPUTE – Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the COMPUTE clause generates control-breaks and subtotals in the result set. You can specify COMPUTE BY and COMPUTE in the same query.

One of the twisted things about the COMPUTE statement is dealing with it as part of the result set, because simply put, it isn’t.  It comes back as a secondary result set which needs to be handled as such.

So when in need of some preparatory T-SQL data “munging” to see how things might be in a cube, check out BOL (SQL Server Book Online, comes with the server installation) for any of these operator keywords and you’ll get multiple examples.

So that is my quick summary of “wannabe” OLAP type T-SQL queries.  If you want real reporting on slices and dices of data though, you have to drop these and get with the real OLAP/BI solution world.

I’ve always know there had to be some rule or logic that someone has laid out for usability.  Especially from a marketing perspective.  Recently I took to reading a few pages of Web Analytics Demystified by Eric T. Peterson.  While reading through the book in the very first few pages the 10-second usability rule is mentioned.  It reads:

• One tenth of a second (0.1) is the limit for a user to feel that a system is responding immediately when engaged in a task.
• One second (1) is the limit for a user’s flow to remain uninterrupted, allowing them to notice a delay but not losing the feeling of direct connection with the task.
• Ten seconds (10) is the upper limit for keeping users focused on a single task.  When faced with delays of ten seconds or longer, users have a tendency to engage in side tasks or become frustrated with the lack of system response.

Thinking back over the last few years I’ve known more than a few people that needed a lesson in this concept.  Simply put, if you aren’t aware or paying attention to this concept when building UIs, you are already VERY close to failing.

So keep it close and in mind, a second is too much to lose!

So Microsoft has promised a November release date for .NET 3.5 and VS 08.  I’m stoked that it will (could?) be out by end of month!  The funny thing is I found out by reading about a SQL Server blog entry.  In that entry Denis Gobo is wondering, which I do also, where is SQL Server 2008?  Eh, I’m still stoked, and don’t mind waiting, but it would be nice to have some news from that front also.

Cheers to 08′!

I was having a conversation with a friend about their particular predicament and how they got fussed at for taking time, after being directed to, to write unit tests for someone else’s code.  Not only had this individual been directed to do these tests, but after providing estimates based on the idea that the application already was complete, or at least in a testing state.  So with the idea that the application was more complete than it was, unit test writing initiated only to find that the application needed major fixes.

Now correct me if I am wrong, but unit testing is done to find these issues and errors.  Sometimes, most of the time, almost all of time, unit testing is going to take longer than writing the application straight.  But the application will be more solid, have better integrity, the ability for regressions, faster future development, less crashing, better organization, and assuredly better meet the requirements the application is designed to meet.  So when someone takes the time to assure this level of integrity, the last thing a manager or anyone should do is to not realize what is going on when unit tests are being created.  Writing a unit test, especially under a TDD process, is NOT JUST WRITING CODE!

Rant Number 345,251,090,253.

So, to all the managers in the world, please read and focus on what a unit test is.  The reason and purpose of a unit test is vital to getting the results of what is promised from unit testing.

So don’t ask your developers to go and unit test applications that they did not write unless you expect them to go through and literally verify every single “unit” of work that is being performed.  They have to write a “unit test” for each “unit” of work so they must understand each “unit”.  That means, for all practical purposes, learning the entire use case and application in detail.  Remember, if done properly, this will probably take almost as much time as actually writing the application did.  It’ll be worth it, but make sure to have good expectations of what kind of resources and effort this will take!

Writing unit tests after the fact like this is not a good way to go about coding, but sometimes it needs to be done.  It is definitely good to do test driven development instead of the post test development style.

So please stop expecting the market this mythical hype, get rid of the pointy hair boss syndrome, and start dealing with reality.

I have to admit, it is pleasant to “relearn” something versus trying to learn it the first time.  The images, explanations, and past learning pour back into my memory.  The memories and experiences are almost always good, the times fun, and the excitement of figuring out something the first time is renewed.

With that I’ve dived back into my most recent study of business intelligence and various OLAP technologies from MDX to the storage mechanism full force.  Lately I’ve reviewed again the storage mechanisms, working to figure out what is inside the “black box”, that makes the OLAP Cube.  Much of it I’ve recollected over the last few weeks with ease, but the black box is a bit of a mystery.

What got me thinking about this wasn’t the action of relearning these last few weeks, but an article I read that has to do with this exact topic.  The Secret Sauce of Highly Productive Software Development hits the nail on the head of what makes an Agile team truly effective and faster, sometimes exponentially faster than a traditional team of software developers.  Learning is a key to making a team fast.  The team must always learn, every day, every hour something different comes up and they must adapt.  Everything from the new pattern to implement a needed feature to the simple “I gotta hit this button even though it is not in the documentation” knowledge.

After all that being said, if there was one feature of Agile Methodology that I have to pull out of the bag of tricks and say is important, it is learning and adapting to the tools and the project.  This aspect of the methodology is more important than almost any other part, of course all the other parts are built around enabling the developers to learn, adapt, and have process that can handle and integrate the “learning” into the actual development process as much as possible.

All this thinking on the topic of Agile and the basic inferiority of previous methods lends more credence to why Agile itself is permeating almost every methodology these days.  It is strange, almost like the other methodologies, are attempting to “learn” Agile.  There are of course those people out there that are naysayers of Agile, and that is fine, they’ll probably be out of the software development sector of the industry within the next 1-5 years.  If not they’ll be relegated to the “maintenance role” of 35 years and will hopefully be happy with their “white picket fence” dream home.  I personally am ready for the challenge of staying well ahead of the curve, far away from the mediocrity of the average, and pushing the envelope as much as possible.  Addictions aren’t generally a good thing, but I am addicted to solving this learning bottleneck within project efforts.  Not only am I addicted, I’m intrinsically connected to the whole learning concept.

On that note, I’m back to the blogs, books, and personal efforts of the day.