In my eternal effort to keep up with everything, or at least the things related to what I do…  I found an excellent law of system design list by Mr Ward Pond over on his blog.  I always find lists interesting, as statistics show almost everyone does, but these types are actually of use.  Many of the points are true to the occupation and definitely worth reading and acknowledging.  Some of my own thoughts around Mr. Pond’s Laws are generally reinforcing the general undertone of each law.  The ones I generally have 2 cents to add are following.

#5 Pond mentions that the “value you add goes triple for the value that the job brings to you”.  I absolutely agree.  I would also add the correlation that without value being brought to the individual doing the job it is a worthless job.  If you can’t enjoy, even in some small way, you should probably either find a way to enjoy somehow or get the hell out of that type of work.  When #5 goes the other way, not only do you not add value to what you do and your coworkers efforts, but you detract extensively from your own life.

#6 “Play” with the product.  If you aren’t, are you even really doing your job?  Something I commonly ask of advanced DBAs, Software Developers, and even Architects that don’t write code anymore.  What are you doing then?  Why are you not doing it?  Get back to it, if you aren’t playing, something is big time wrong!

#8 Please, I implore people to know this, to simply know thy self.  Without this, you will never be good at the whole aspect of human contact, which regardless of what some might think, is pivotal to doing a good job at anything!

…and that leads me to the last law that I really must reiterate…

#10 Your code is a communication with someone else, who will likely come after you are gone.  This is true, it is frustrating, and sometimes agonizing, but simple leave some kind of whimsical comment is better than nothing.  I’ve made gross errors in this area of my career, but ongoing I always try to improve my comments.  On the same note, but the other end of the view, make sure you learn to grasp and read comments and code.  Make sure you actually know the technology (see #6), don’t blame inadequate knowledge and ability on the “guy that left” because you don’t understand patterns, objects, the language, the technology stack, or other scope.  Always learn, keep learning, and don’t stop.  With enough knowledge, even the most cryptic spaghetti isn’t all that bad to fix.

I just spent multiple minutes on the phone with Chase Cardholder Services attempting to find out the bloody freaking zip code to send a stupid payment to.  You would think that would be easy.

I couldn’t find it on the site, I’m sure it is buried there somewhere.

I could barely get to it on the phone.  I had to go through two phases of entering my 16 digit account number, plus other pieces of information just to get the blasted address!  Something is seriously wrong with their navigation.

One of the other interesting tidbits is that while navigating this, I was also reading a blog entry.  That blog entry just so points out a lazy lazy navigation practice – “show less choices”.

There is a connected irony between my complaint and his.  They need to get someone on their navigation and logical structure of their site right away!  I just experienced the negatives of their navigation, and their lack of providing information in a coordinated and collected fashion.  The billing address should BE UNDER THE PAYMENTS SECTION!!!  If anyone is paying attention, FIX THIS ASAP.

The services are good, the cards a decent, the point programs rock, but blast it I want to be able to use them better!

Thanks, Perturbed Adron

As I sit through the second day of training I can’t help but think back upon my previous experiences with OLAP and BI.  My thoughts run back to some other times doing the same things, but it just wasn’t initially called BI or OLAP, simply reports for executives and business decision making.  I find it interesting that there are specific labels for all of these efforts.

BI for the most part is just views, stored procedures, and other data getters that coordinates and correlates data into “information”.  The difference now is that there are tools and other applications that assist   This has been done for ages and ages.  There have been autonomous database servers setup just for this purpose before.  They where not called OLAP or BI servers, just simply report servers.

One of the solutions that I worked with in the past previous to dedicated OLAP and BI solutions was back at SCP Pool Corp.  We had a database that was used for OLTP work and another database that stored denormalized reporting data.  In addition to that we had break downs of data into views and other assorted organizations to provide extensive reports.

This solution was nothing more than what the BI and OLAP Cubes provide today.  The data was similar, the speed was similar, and the capabilities where similar.

I will admit though, that even with similar capabilities, what is simplified and assured with SSAS & SSIS in comparison to the manually created and manually coded solutions of yesteryear are vastly better.  I just like to draw the correlation that it isn’t particularly NEW, it is just DIFFERENT.  The decrease in time it takes to put together report cubes, process them because of simplified structures, and the ongoing enhancements to the underlying engines themselves make BI and OLAP work worth the investment.

Once again, just like interurban passenger cars are now light rail vehicles and horseless carriages are now cars, reporting databases are now OLAP/BI Cubes.

Yesterday I attended Maxamine Training as part of my initiation at WebTrends.  Maxamine is a web site scanner for finding tags, JavaScript, and other assorted objects within a web application/site.  The application tool and software is a client application with a server based storage base.  All in all, it is a decent product, and for anyone in need of a web site scanner they should definitely check it out.

Today I’m sitting in day one of Business Intelligence and OLAP Training.  It is an introductory class, which places me at risk of being a little bored, but it is still good to get a solid review with SQL Server 2005 Tools.  My last serious experience was with SQL Server 2000 way back in the days of my Jacksonville work.  SQL Server 2005 has some definite changes that are advantageous over the previous version.  Even though this class is introductory, I decided it would be a great opportunity to create a set of notes and a write up in relation to the class.  These notes and descriptions are what will follow over the next few days.

Definitions

OLTP – Online Transaction Processing

This form of database is used primarily for; transaction processing systems, normalized data, optimized for data entry, and has a focus on data integrity.

OLAP – Online Analytical Processing

This form of a database/warehouse is used primarily for;  analytical systems, denormalized data, heavily indexed, aggregated, and has a focus on reporting and analytics.

Star Schema – A Star Schema is a schema that is used in business intelligence OLAP Cubes that has the following characteristics;  single fact table, multiple dimension tables, all dimension tables directly related to fact table, and allow quickest processing time.

Snowflake Schema – A Snowflake Schema is a schema that is used in business intelligence OLAP Cubes that has the following characteristics;  single fact table, multiple dimension tables, dimension tables can be related to the fact table through other dimension tables, slower to process.  Sometimes faster to load than a star schema, but not particularly to process.

Fact Table Design – A fact table is created, generally, from multiple OLTP tables, foreign key columns to dimension tables, measure values, and provides a single level of granularity.  Fact tables also should have surrogate keys, be flattened to a star schema, may need to deal with nulls, and is the shared bus for dimensions.  It is also preferred, and sometimes a key need, to not have nulls in the cube.

Catalog – A database defined on an OLAP server.

Cube – A multidimensional data subset derived from relational tables, or a data warehouse, organized for rapid query response.  What I have called in the past, “Database Views on turbo steroids”!

Dimensions – Dimensions represent how the data is accessed.  Usually dimensions represent things, persons, a place, or other type.  The dimensions answer the “who”, “what”, or “where” and the “how” or “by” part of a question.  The dimension presents the how or by part of the question.

Hierarchy – A hierarchy is a structure within a dimension that represents the organization of data grouped in levels of most general to most specific.  An attribute hierarchy is a flat hierarchy containing a single attribute.  This would include a single level break out such as a product type.  A user defined hierarchy is a hierarchical arrangement of attributes.  This would include a break out of product type including sub product type.  Creating more multilevel hierarchy.  A level represents a specific detail about data within the dimension.  A member represents a specific value contained within a level.

Measures are key metrics, often referred to as the key performance indicators (KPI), of a business.  Measures answer the “what” part of the question, and are usually additive, discrete, and easily measured.  One example is that of sales, which is of course easily additive.  Others would include;  cost per item, item sale price, sold price, and other set values.  A calculated measure is the additive sum of the item sale prices, or sold prices for a period of time, or by quarter, or other measurement.

Best Practices from Day #1

• Use staging areas.  Staging areas help abstract, clean, and often speed up the process.- Surrogate keys should be used for dimension tables.- Use a shared bus of dimensions.- Fact table foreign keys  should be the surrogate keys not business keys.- All measures in fact table need to be the same level of granularity.

My Two Cents $$

When designing cubes one of the first things that needs to be done is forget.  Forget the OLTP, normalized, technical break out of data and storage.  The first thing to do is to think of the questions that need answered.

• What are the roll ups and sales for product x through y for the first quarter broken down into weeks, possibly even days, or time of days?- What are the locations that sales occur in, what time of year is it and how does that correlate to the weather (summer cloths sell in the summer, winter in the winter, etc)?- What is the break out of planned, projected, and literal sales and expenses against revenue?

This is one of the things that I find absolutely exiting and interesting about the field of business intelligence.  Business intelligence is the good way, best practice way, the smart way to make business decisions based on empirical evidence!

Now For The Microsoft Architecture

In Microsoft’s Technology Stack the UDM, or Unified Dimensional Model, is the cube itself represented in the Visual Studio SQL Server Analysis Services Interface.  The UDM combines both relational and analytical elements.

For more about specifics of BIDS, SQL Server Management Studio, and other Microsoft Architecture definitions check out my previous entry on these items.

To assist in visualizing what each part of the SSAS Visual Studio IDE make sure to clarify the various levels of abstraction.  The abstracted points to know are the Data Source Views (DSV) in relation to an actual Cube, where external sources come from, and how the various SSAS mechanisms work in conjunction with SQL Server Integration Services (SSIS).

The data source view provides an abstracted layer the Cube works on.  DSVs are derived upon existing warehouse tables.  These DSVs are also correlated appropriately for presentation within the Cube.  A common mistake is to think that a DSV can serve as a cube.  This is not suggested as major bottlenecks and other issues can occur.

Also, remember that the data warehouse is very important, not just as a data repository but also as an integration server.  Without being able to execute SSIS and associated ETL processes separately from the actual OLTP database is a sure fire death sentence to the OLTP Database Server.

OLAP Database

The OLAP database is the top level container for OLAP Objects.  This top level object contains the various objects;  data source, data source view, cube, dimension, security roles.  The data source is the provider or data connection.  The cube access is provided at this level along with the processing of the cube, retrieval with ROLAP and HOLAP, proactive caching, and write backs.

On the last note, a kind of summary for the class, the following points where made.  One: Don’t use any auto build features.  Two: Have your cube designed with the a ppropriate keys in place.  Three: Use a star schema data source to simplify dimension management.  Four: Dimensions are shared;  be careful not to duplicate.  Five: Use surrogate keys in base data source.  Six: Add all columns of interest as attributes.

My Summary:  The day went well with a lot of material covered.  Fortunately for me since I was exhausted from a crappy night’s sleep, most of the material was merely a refresher.  It was good though to step back through all these things, but also to see the SSAS way of doing things.  The last time, as I’ve probably mentioned, that I was doing this I was condemned to use SQL 2000.  All in all, I had a blast, getting to refresh the material and learning a few things about the SQL Server 2005 Analysis Services.

Technorati Tags: OLAP , BI , Business Intelligence , SQL Server Analysis Services , SQL Server , Best Practices , Calculated Measure , Snow Flake Schema , Star Schema , Maxamine , WebTrends , Key Performance Indicator , KPI , Hierarchy , Dimension , Cube , HOLAP , ROLAP , MOLAP

del.icio.us Tags: OLAP , BI , Business Intelligence , SQL Server Analysis Services , SQL Server , Best Practices , Calculated Measure , Snow Flake Schema , Star Schema , Maxamine , WebTrends , Key Performance Indicator , KPI , Hierarchy , Dimension , Cube , HOLAP , ROLAP , MOLAP

While ramping back up for heavy duty OLAP & BI work there where a few names that kept popping up.  These are definitely the heavy hitters in the field.  For my (and you fellow reader’s) future reference.

• The first person is Mosha Pasumansky.  He is the father of MDX at Microsoft.  His blog is full of great material on MDX and Cube info in general.
• Richard Tkachuk wrote an Introduction to MDX Scripting in Microsoft SQL Server 2005.
• One guru that does a lot of work in the field is Brian Knight, who I had the fortune of working with on a gig down in Jacksonville for a major ETL/BI/Cube Project.
• Vidas Matelis BI Blog.  Digging, dug, data mining.
• For a mix of BI/OLAP/SSAS/SSIS/ASP.NET/TFS and more, check out Richard Hundhausen’s Blog.
• Another great blog is SQL Server Code, Tips and Tricks, Performance Tuning.  This blog has a good mix of material covering internals and interviews with people in regards to SQL Server and related topics.
• Andy Leanord seems to be a good read too.  He has various design patterns associated with SSIS Development.
• The Microsoft PerformancePoint Team Blog is also a great read.  Good stuff.  🙂
• Musings on SQL Server 2005/SSIS/SSAS is on topic for this particular blog roll.
• Ward Pond’s SQL Server blog is also a good read.