SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
Minimarts: Organizational Segmentation in Datawarehousing
About the Author

Rick, a specialist in database application development, who recently made the change to database administration, has worked in IT for 25 years in nearly all areas excluding sales! Quite an achievement! He currently works in a SQL Server 2005, ESRI ArcGIS SDE 9.3 environment with Visual Studio 2005. His appetite for knowledge in all things IT grows daily, although his first love is databases.

You can read more articles and blogs by Rick on his Critical Status blog, which was designed to keep track of and access his database and development related thoughts, ideas and issues.
Send to a Friend Printer Friendly Version PDF
Introduction

With the new insurgent push towards Business Intelligence and datawarehousing, we are all expected to create enterprise datasets for analytical reporting. These analytical reports will be used for decision support on an enterprise level. Yet, there are organizations within business units that have the same need for analytical data that would be a subset of their organizational datamart.

Business units consist of various operating centers. With Human Resources, there will be employee benefits, travel, employee records, compensation and a whole world of other cost centers that focus on their segments of business unit datamarts.

Therefore there are segments within each datamart that I will term "minimarts". Below I’ll give a brief discussion on Business Intelligence and datawarehousing, Microsoft’s strategy, the datawarehouse components and how these minimarts fit in.

Business Intelligence

Business Intelligence (BI) has been around as long as there have been economies. BI software in the enterprise has been a growing market. Most of the main players in database management offer products for storage, Extract, Transfer and Load (ETL), reporting and more. Microsoft has offered BI tools since SQL Server 7.0 but without a big splash. SS 2000 bolstered its offerings to make a move towards increasing market share, but it is with SS 2005 that they finally got aggressive in what is now a most precious and sought after commodity – analytical data for forecasting an organization’s direction!

As database administrators (DBA) we are the gatekeeper of the data kingdom in our organization. We need to prepare the proper infrastructure for the tools to supply this golden nugget. It is in fact our responsibility to produce high performance fully available information for everyone in the enterprise.

My fellow data shepherds, we either prefer to work with Microsoft products or we may have been “coerced” into migrating with SQL Server from Oracle, MySQL, DB2, Postgre or whatever. As SQL Server DBAs, I hope to that you will look into and even evangelize BI, not just to bolster your resumes, but to prove yourself indispensable in this troubling global economy!

Microsoft SQL Server 2005 and 2008

Why should you upgrade to SQL Server 2005 or 2008 from SQL Server 2000? There weren't dramatic improvements in the database engine performance-wise. It’s the addition of new (or late 2000 release) components for Business Intelligence that give you the reason to upgrade.

As stated above, Microsoft wants to be a leader in the growing BI market. A better interface to a quality RDBMS is a good first step, but to really gain market share in the Fortune 500 is to cheaply, effectively manage and display enterprise data.

SQL Server Analysis Services (SSAS) was introduced with SQL Server 2000 with very little fanfare. It was greatly improved and pushed to the forefront as a major component of SQL Server 2005. Along with the other major components, they can be managed with SQL Server Management Studio (SSMS).

SSMS is a single interface for DBAs and developers and a one stop shop for pulling data into a staging environment with SQL Server Integration Services (SSIS) and managing the reporting client with SQL Server Reporting Services (SSRS.) I can build an entire datawarehouse system from my workstation, remoting to all system servers and creating all objects and process from SSMS.

A version of Visual Studio (Business Intelligence Development System (BIDS)) is available for creating .NET Data Transformation System (DTSX) packages for ETL and cubes for business unit repositories. The solutions built with BIDS can be managed with SSMS.

The one stop shop approach offers us a quick development and management environment in a constantly changing business environment. We don’t need to trip over multiple applications and tools which slow us down and eat up our system resources. So let’s take a look at what BI consists of.

Datawarehouse

The main component of the BI is the datawarehouse. This consists of a repository of read-only aggregate enterprise organizational data. This is data relating to enterprise departments such as Human Resources, Finance, Sales, and Operations.

Datawarehouse architecture consists of a variety of data sources that are extracted, transformed and loaded (ETL) into the datawarehouse tier, i.e. loaded into staging databases then pushed into datamarts. The datamarts are replicated to Online Analytical Processing (OLAP) servers. The OLAP servers are used for queries by the Presentation/Client tier for reporting.

The datawarehouse architecture

Datamart

A datamart is a subset of the datawarehouse. It is business unit data for individual enterprise departments, as mentioned above. The data is stored in cubes of aggregate data of dimensions and facts instead of rows and columns. Think of a cube as a folder containing summarized files (tables) each based on a grouping such as periods/points in time, such as a month.

An example would be tables of sales data for each month. These are often very large datasets, usually containing all relevant data per business unit which, in being rather large, can have performance issues when querying.

Minimarts

Many decisions makers not only need to analyze data from within the business unit, but also across business units or the many segments within a business unit. Think of it as market segmentation – cubes for more a selective audience.

Your customers can be grouped by segments of business units or smaller functional groups with unique needs for reporting. Most segments will contain unique, pertinent subsets of data along with business unit-wide data for decision making. Therefore, cubes can be made from subsets of cubes in what I would call “minimarts.” These are datasets that can be made “precise” for more targeted queries thereby increasing performance, especially across a network, intranet, extranet (or even the general public internet).

The pros are pretty clear by targeting your audience. Performance will be greater than querying vast cubes of data that can go unused for most members of your customer base. Security can be better managed by using security groups – a topic for my next article. Report Models can be created in SSRS based on security groups for ad hoc querying or specialized report subscriptions for each minimart user base. It's not an intensive task to setup, monitor and train the smaller user base points of contact to successfully deploy SSRS projects.

Minimarts unfortunately require more maintenance as the volume of data is smaller and more exacting. Also, more applications or portals could be spawned by each break down from the business unit requiring more minimart cubes and web sites. There is a cost factor in the increased growth of projects related to each minimart.

Targeted Reporting and Applications

By targeting each group of decision makers with specialized (yet standardized) sites and minimarts, better and quicker decisions can be made. The good-will generated by providing tools to a greater audience, whether at the datamart of minimart level, will increase a demand for your services. This in turn should have an impact on your IT budget especially related to you and including your compensation package!

So I strongly recommend researching and developing a plan to bring Business Intelligence to your organization along with the intention of implementing minimarts for the various segments of your enterprise.

Microsoft’s collection of tools based around the product we already use, SQL Server, will provide quality BI at a reasonable cost if it is implemented wisely. So if you haven’t, install SSAS in a development environment and be creative!

Useful Links

Send to a Friend Printer Friendly Version PDF
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form