Oh my freaking !@#!

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

Business Intelligence Observations

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.

BI/OLAP Training Day #1

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

The Guru(s) of MDX, OLAP, and SQL Server Analysis Services

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.

Web Analytics Grasped, Week One Wrap Up

Web analytics are derived in two primary ways, so far (more on this later), JavaScript tags and web server log files.  This is pretty much the universal way that web statistics are collected.  There are dozens of books out there that cover this in dozens of different ways;  Web Analytics Demystified (which I’m reading now), Web Analytics: An Hour a Day, Actionable Web Analytics, Web Site Measurement Hacks, Google Analytics 2.0, and diverging into a sub-topic of web analytics is Search Engine Optimization: An Hour a Day.  These books of course cover much more than just web analytic tagging methods, but they’re good resources for discovering more about the field.

Web analytics can be used by almost any sector of the web industry.  Some of these divisions include; content, commerce, lead generation, and self service sites.  Each of these separate divisions would track differing statistics, ranging from gross margin, gross margin return on investment (GMROI), net profit, total sales, average order size, or accessory attachment rate all the way to average page views per visit, average visits per visitor, click through on on-site ads, first-time versus returning visitors.  We analytics cover the full spectrum of feature sets in almost any type of web application you could think of.

I’ve also started to stumble into specific techno analytics speak.  One of the words that hit me kind of funny is sessionizing.  First off, it is not technically a word.  It breaks convention from a real word that would be created or used in the English Language.  But really, I guess nobody cares if it gets the point across.  Plus we’re talking about the marketing industry here; it couldn’t possibly hurt to draw more attention to something with faux words.

Sessionizing is a custom web analytics adjective.  Sessionizing is the process of assigning a unique visitor to one or more actions that occurred within a defined period or visit.  There are other words, and analytics speak, that I will cover later.  For now, I’ll just let the word sessionizing sink in a little bit.  If you think I’m joking, I’m very serious, so be aware when you hear it in the analytics industry, that it truly does mean something now.  I’m sure it will end up in the dictionary within the next few years anyway.  Then it will be as real as any other English Word.

Really though, it is just one more entertaining part of this part of the technology sector!

Some of the other things that starting here has made me ponder.  Some I can’t mention yet, others include; what would I measure on this site, how could I use this to improve my search engine optimization, and other such things that I generally don’t ponder.  I get the feeling that I’ll be thinking a lot more about these things in the coming days and weeks.  Cheers to that, I’m off for a few beers and a weekend of doing absolutely nothing in particular!