Numeric Keys RULE! :: Tip O' The Day

When you’re developing attributes that have a bunch of members, let’s say more than a million (1,000,000,000), you should definitely use numeric keys.  Don’t use strings or anything like that or you will suffer delays.  With business intelligence cube development we’re talking about airport length delays here!

So next time you’re tearing through some ETL or data manipulation processes or just building attributes with what you have, make sure to toss those numeric keys in there to assure the speed that a good Cube should have!

Lacking MDX Contemplations

So here I am plugging in some dimensions and getting some measures in Visual Studio when I stumble on the fact that I can’t get the MDX from what I’m doing.  What should I use to do this?

One tool I know that will get the MDX for a report is ProClarity, but are there any other “good” tools out there that will do it?

…anyone got some ideas, pointers, or suggestions?  Off to do some research myself also.

Bitten By the Copy+Paste Membership oh Dunce World

Thanks James, for pointing out I’m a dickhead.  You saved me at least a few more minutes of troubleshooting!  SqlprofileProvider is in the Profile namespace branch unlike EVERY other membership provider in ASP.NET 2.0.

I guess I’ll be the 5th with the google”able” error message of ‘Could not load type ‘System.Web.Security.SqlProfileProvider’!

Technorati Tags: SqlProfileProvider

del.icio.us Tags: SqlProfileProvider

Rockin' It At WebTrends

As usual, the first week or three at a new position is very relaxed or at least somewhat calm.  Which for me leads to a bit of boredom, but I always know that just around the corner of being the FNG (F-ing New Guy) is the fast paced action of the project work.

Now that I’ve landed from the FNG phase I’m now carrying weight on three separate projects, and I’m stoked because of it!  With that I’ll have another treasure trove of entry material for the blog (not that I need extra material).

Several of the key technology points that I’m working on at the moment include; SSIS work for future reference, documentation of technical process, and tons and tons of T-SQL Stored Procedures and Cube Customizations.  Probably more of the later than the others and I’m ready to roll!

Smile [:)]  …not sure why I had to make this announcement via my blog, but I just felt the random and odd desire to do so.

Pretend T-SQL BI Queries

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.