Cubegeek

Narrow Validation Joins with Wide Fact Results

I'm deep into some interesting and fun worlds of mind-numbing complexity and that is the world of comprehending and interpreting business rules written in SQL. Along the way I saw something I haven't seen before. I'm sure it has an interesting name.

The basic idea is a kind of validation restraint on a wide query which is established by joining a narrow query. Cool.

Here's the construction in abstract...

select blah_one ... blah_n
from table_of_facts facts
(select distinct key_one, key_two, key_three   
    from (select distinct key_one, key_two, key_three
          from validation_table
          where key_three = constraint
          UNION
          select distinct key_one, key_two, key_three
          from table_of_facts
          where key_three = constraint) acceptable )

where

acceptable.key_one = fact.key_one  and
acceptable.key_two = fact.key_two  and
acceptable.key_three = fact.key_three

Now isn't that crazy? Now this thing was written for DB2, and that may be a really snazzy way to make your query run fast under that engine. But I swear in 15 years I have never seen that kind of construction, where you essentially join a table to itself and a validation table and run the constraint against the join rather than on the where. Still, I see how it can make sense when you are making nice ascii operations with blah_one through through blah_n.

I'll test it out one day when I have time.

Consulting Links

A&J Computers
Description: A&J Computers Inc. specializes in delivering high-quality, low-cost computer services for our customers. Clients and partners alike appreciate our ability to provide services and resolutions strictly relevant to their needs. Through our team's extensive industry knowledge and real-world experiences, we clearly grasp and understand our clients’ needs. Applying this understanding to our customers’ issues guarantees their rapid resolution. In every project, the emphasis is on quality, value and customer satisfaction through performance enhancing technologies.
 

Answerthink
Description: Answerthink, Inc. is a business and technology consulting firm that enables companies to achieve world-class business performance. By leveraging the comprehensive database of its Hackett Group business, with its repository of enterprise best practice metrics and business process knowledge, the Company's business and technology solutions help clients improve performance and optimize returns on technology investments. The capabilities include performance measurement, business transformation, business applications, technology integration and offshore application maintenance and support. With strategic and functional knowledge in customer service, sales and marketing, finance, human resources, information technology (IT), procurement and supply chain management, the expertise extends across the entire enterprise.
 

Aspirity
Description: Aspirity today combines the best of its two predecessor organizations -- Aspirity/OLAP Train and EssPro -- which merged in July, 2001. The result is a world-class consulting and training organization with depth and breadth in all of the technologies of business intelligence and all of the important database and front-end platforms. With offices in Seattle, Chicago, Dallas, San Francisco and Denver, the new combined company can meet any organization's needs in the continental US.
 

Beacon Analytics 
Description: At Beacon Analytics, we help clients accelerate and maximize the return on their Hyperion software investment. Our team, like our business, is Hyperion and business analytics to the core. As a Hyperion Platinum Consulting Reseller Partner, we focus on Hyperion and bring a hands-on approach to jump-starting your implementation. Our credentials go well beyond Hyperion certification. The Beacon team has earned its reputation as an authority on Hyperion’s Essbase and Planning products, and has a track record of applying these technologies to meet the challenges of specific industries. Beacon’s expertise combined with a passion for success is producing client achievements that have become the envy of the Hyperion community
 

ETTAIN GROUP
Description: Who We Are ettain group is a privately held, award-winning IT Services firm that provides IT Staffing and Professional Services to companies throughout the Southeast. We have offices in Florida, Georgia and North Carolina. ettain group is comprised of over 40 dedicated full-time employees and more than 250 experienced consultants. Where We've Been Our IT Staffing practice was established in 1996 as an innovative alternative to the commodity based focus of the majority of staffing companies. Our focus on creating value and providing world class service to our clients and consultants has enabled us to sustain profitable growth and unparalleled excellence since our inception. Our Professional Services practice dates back to March of 2000, when we built a solid reputation for developing attractive, easy-to-use websites and applications for clients like Bank of America, BMW, eMachines, Lance, BellSouth and Muzak. In March of 2002, we combined these two distinct service offerings to form an innovative and flexible approach to solving the changing technology needs of our clients. Where We're Going Continuous innovation. An ever-expanding pool of talented IT professionals and a commitment to our clients and consultants has placed ettain group at the forefront of the IT Services industry and we intend to stay there.
 

Marketing Technologies Group
Description: Marketing Technologies Group (MTG) is an industry leading provider of OLAP and Business Intelligence consulting and training services, as well as one of the oldest and most technically certified Hyperion Essbase and IBM DB2 OLAP specialty firms on the planet.
Added on: 09-Sep-2005 | hits: 402 | Report broken link | Details

McAnalyst
Description: McAnalyst Consulting Incorporated has offered Essbase consulting services since 1995. McAnalyst’s West Coast affiliate, First Street Analysis, has provided Essbase consulting since 1994. McAnalyst provides Essbase solutions to a wide cross section of North American industry groups. McAnalyst delivers expert design and development expertise to other Essbase professionals, i.e., consulting for the consultant
 

Niche Technologies
Description: Now celebrating our10th anniversary in the OLAP field, Niche has delivered OLAP Business Solutions to over 70% of the Fortune 100 throughout the U.S., across Europe, and in the Middle East. This represents hundreds of diverse projects, spans more than 24 industries, every major OLAP tool, and diverse platforms.
 

Pivot Inc.
Description: Pivot, Inc. can provide consulting services for all phases of your Essbase project including Design, Implementation, Migration and Training. Pivot, Inc. has experience with Essbase, Wired for OLAP, Hyperion Integration Server, Front-End and Template development, Essbase Objects, and more.
 

Right! Solutions
Description: right!Solutions is a premier provider of consulting services for the full lifecycle of analytic application, business intelligence and ERP projects including evaluations, development, package implementations, upgrades, end-user training, workflow, reporting, modifications and production support. Ranked in the top 100 fastest growing companies by Inc. magazine, right!Solutions delivers high-quality consulting services that are flexible, innovative and creative to hundreds of large, mid-sized and small companies across all industries. Using our unique, interactive right! Methodology®, right!Solutions employs workshop-based tools and techniques that facilitate successful, rapid and cost-effective projects.
 

SDG Computing
Description: Tim Peterson, the chief consultant at SDG Computing, Inc., is the author of Microsoft SQL Server 2000 Data Transformation Services , (SAMS) December 2000.
 

Symmetry
Description: Founded in 1986, Symmetry is the recognized expert in designing and building advanced business analysis solutions, otherwise known as On-Line Analytical Processing (OLAP) systems. Corporate clients and business intelligence software vendors alike respect us for our: Market knowledge Grasp of business issues and processes Creative and successful solutions Understanding of OLAP product and market directions Demonstrated experience in multiple OLAP software products We pride ourselves in bringing quality, expertise, and business insights to our consulting engagements.
 

ThinkFast Consulting
Description: With six offices coast-to-coast, ThinkFast Consulting is dedicated to delivering solutions that put information at your fingertips. Information that helps you make strategic and tactical decisions to increase profitability, enhance client relationships, expand market share and improve planning resulting in heightened competitive advantage.
 

TomChester.net
Description: Tom Chester & Associates. Tom is a software inventor, database consultant, author and educator specializing in the OLAP niche.
 

US Analytics
Description: US-Analytics forges partnerships and focuses on working with some of the most strategic and leading-edge software and technology companies in the Performance Management Industry. Through these deep partnerships, US-Analytics is able to identify the right solution and strategy needed to design a customized Business Intelligence Environment. In addition to these strategic partnerships, we offer our clients the best of everything. From proven methodologies and implementation processes to industrial experience and technical expertise, our end goal is your satisfaction and another respected US-Analytics client reference. We take pride in our talents and abilities that have facilitated great success for our clients; US-Analytics is a very powerful partner for any organization.
 

Value Drivers
Description: Value Drivers provide our clients with visual and actionable analytics delivering one version of the truth. As a certified partner of ProClarity Corporation, we deliver consulting services that specialize in business solutions, analytics services, OLAP, and ERP implementations. Value Drivers’ balanced approach to transformation is powered by results-focused outcomes, a one-page strategic plan, and the power of ProClarity analytics. The solution guarantees the client a unique thumbprint in the marketplace. The Value Drivers team is comprised of seasoned specialists averaging over twelve years of industry experience. Between them they have founded over thirteen companies. We use a proven methodology to deliver strategic leadership in business process, software implementation, and application development. Value Drivers expertise is built on empowered pattern recognition and a track record for delivering real-world results.
 

Technorati Loves Me

(Now)

Technorati Profile

Binary Aggregation

(From the archives July 1997)

I finally came up with the solution. As we looked at the numbers on the paper reports for headcounts, I recognized that none of the numbers were tying. Every cell contained a possible double count depending on the rollup. Only zero level numbers were reasonable. I reasoned that at every level of aggregation, what we wanted was a yes or no by individual.

Binary Aggregation

The solution is to create a member formula for Heads which performs the equivalent of a binary aggregation at every level of aggregation of every dimensions but the top level of the Individual dimension.

In order to get unique heads as a measure do the following.

First, you must have a dimension for individuals and input records by individual.

Input a dummy measures field called Hits by adding a "1" as a column in your input file by modifying your load rule.

 Calc your database normally.

 When you report against such a database you'll get a problem with double counting, unless you drill down to the individual level.

 For example,

                                                       

Individual	Dim X	Dim Y	Hits
A	1	0	1
A	1	1	1
A	1	0	1
A	0	1	1
B	1	0	1
B	0	0	1

 

 

Notice that if you query Hits for XY = {1,0} you will get 3 despite the fact that there are only 2 distinct individuals. (A hits twice), Unless you break out the individual dimension, you can't know that A hit twice.

 

The solution is to aggregate Hits at the descendants of the Individual dimension according to the following logic:

hits = (if <>#missing) 1, #missing

Fix on the descendants of Individual, then aggregate the Individual dimension across all the other dimensions, then on a third pass, aggregate the Individual dimension by itself. I forget the actual syntax i wrote in the calc script but it worked like a charm. Thus reporting at the top level of Individual, you get distinct heads. Now you can rename the Hits measure to Heads.

At this point you can export the database and get rid of the Individual dimension, if you like.

Essbase Whiteboards

I found myself describing some Essbase internals yesterday. What a lot of fun that was. Took me back to my old Arbor days and training. It turns out that I remember more stuff about the innovations of version 4 and 5 than I know about how ASO actually works. But there are only 20 people on the planet who can actually describe the internals of ASO. Anyway, enjoy.