BI Blogs

I was needing to do an mdx DRILLTHROUGH report in Reporting Services and got this strange error: “Failed to parse the query to detect if it is MDX or DMX. (MDXQueryGenerator)”.  Went looking and came across Nick Barclay’s blog entry that talks about how you can’t do mdx DRILLDOWN queries in RS and that Darren Gosbell had written a bug report about it.  Microsoft responded that they decided not to support it since you could easily perform the same query in 2005 w/o using a drilldown statement.

It took me a little while to work out all the syntax of the different queries and thought I’d share it here as well as some performance differences that I found.

First of all, here is the DRILLTHROUGH query that I wrote.

DRILLTHROUGH
SELECT NON EMPTY { [Measures].[Internet Sales Amount]} ON 0
FROM [Adventure Works]
WHERE ( [Date].[Calendar Year].&[2003])
RETURN
     [$Customer].[Customer]
     ,[$Date].[Calendar Year]
     ,[$Product].[Category]
     ,[$Product].[Model Name]
     ,[$Product].[Product]
     ,[$Product].[Product Line]
     ,[$Product].[Subcategory]
     ,[$Promotion].[Promotion]
     ,[$Sales Reason].[Sales Reason]
     ,[$Sales Territory].[Sales Territory Region]
     ,[$Sales Territory].[Sales Territory Group]
     ,[$Ship Date].[Date]
     ,[Internet Sales].[Internet Sales Amount]
     ,[Internet Sales].[Internet Order Quantity]

Here is the equivalent query written in a way that will work in Reporting Services 2005.

SELECT NON EMPTY { [Measures].[Internet Sales Amount], [Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY {
     [Customer].[Customer].[Customer].ALLMEMBERS *
      [Date].[Calendar].[Date].&[550] : [Date].[Calendar].[Date].&[914] *
      [Product].[Category].[Category].ALLMEMBERS *
      [Product].[Model Name].[Model Name].ALLMEMBERS *
      [Product].[Product].[Product].ALLMEMBERS *
      [Product].[Product Line].[Product Line].ALLMEMBERS *
      [Product].[Subcategory].[Subcategory].ALLMEMBERS *
      [Promotion].[Promotion].[Promotion].ALLMEMBERS *
      [Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
      [Sales Territory].[Sales Territory Region].[Sales Territory Region].ALLMEMBERS *
      [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS *
      [Ship Date].[Date].[Date].ALLMEMBERS
} ON ROWS
FROM [Adventure Works]

I have found (very unscientifically) that on my server, there is a significant difference in performance.  The DRILLTHROUGH query ran in 14-16 seconds on average and the CROSSJOIN query ran in 23-25 seconds.  When the queries were smaller and included fewer dimensions and fewer rows because of the WHERE clause, they converged and there wasn’t a significant difference in performance – although on a small query I was finding that the CROSSJOIN query took 3 seconds and the DRILLTHROUGH took 1 second – a 200% difference.

Now I’m not an expert in how the query engine works, but it isn’t a surprise to me that the CROSSJOIN query would be slower than the DRILLTHROUGH query.  Maybe someone out there can detail why the DRILLTHROUGH is faster or if maybe there is another way to write the query to get the same results into RS without using a CROSSJOIN.

This seems to me that we have another instance of the integration of Reporting Services and Analysis Services getting shortshrifted again (read here).  Hopefully Microsoft will see this as an opportunity to improve the integration of OLAP into the Reporting Services product.

This white paper explains the importance of the IBM information infrastructure in terms of providing the information employees need to help their companies become on demand businesses. Of critical importance to this infrastructure are information services such as business intelligence solutions. IBM offers the widest range of business intelligence solutions along with the most scalable, highest-performance data warehouse software product to help customers meet their need for rapid query responses and in-depth business analysis.

WildTangent is the leading publisher of online and downloadable games. WildTangent uses SQL Server on several levels. The whole process was made more complex because the data was flowing between five stages (Production, Staging, Data warehouse, Datamarts, and OLAP Cubes) and four separate servers. Due to the extra schedule padding, the entire import process took so long that reports and cubes were not available until noon each day. After installing sqlSentry, WildTangent implemented sqlSentry’s advanced chaining capabilities. This allowed them the unique ability to connect a single data flow from start to finish even across multiple servers ensuring that each step would start as soon as its preceding step had completed.

You think so, do you? If you don’t work with the business users, how
are you going to know their real needs? How will you understand what
their data really means and how they use it? They may have issues with
data consolidation and manipulation that you couldn’t possibly imagine
on your own. Do you really know what they’re doing squirreled away in
their cubicles, staring at Excel spreadsheets? You should You can’t
solve a problem if you don’t know it exists.

Last night, the ODTUG 2006 Conference Committee announced that all handouts and presentations from this year’s (well, in fact that is last week’s) conference are available for download from the ODTUG website at http://www.odtug.com. Until August 1st, all material can be downloaded by the general public. After that date, you have to be a member […]

Bottom-up specification involves understanding and documenting the existing BI environment and then determining ways to improve and augment the reporting to take advantage of new technology, data availability or management. 

The classic bottom-up specification approach, therefore, involves these steps: 

1. Where are we?  Document our current context.

2. What constraints are there on better performance?  Analyze the context for inadequacies.

3. What options exist to remove these constraints?  Document them.

4. Prioritize these options.

5. Plan the project based on the scheduled, prioritized options.

Documenting the existing BI context results in tabulation of existing KPIs, metrics, transformations, data warehouse cubes, dimensions, and if practical the links to data sources, ETL specifications and report content. 

A product I have worked on that does most of this, in the Microsoft SQL Server and Analysis Services environment, and is evolving tocategorize all the BI database is called BI Documenter – see www.bidocumenter.com. 

A tool such as this is necessary to obtain snapshots of the BI context to be taken and stored. 

We can then analyze these data to establish the existing context, including:

• Defined KPIs

• Existing data cubes

• Data dimensions

• Data sources

• ETL paths and algorithms

• Report content and presentation information

• Recipients of KPI information

• Etc.

The Business Analyst will then be able to make some initial conclusions including:

• KPIs not reported

• Potential inadequate presentation of information

• Profiles of KPI reporting across the enterprise

• Gaps in reporting to corporate areas

• Duplications

• Etc.

The way is then clear to propose the plan and specification to improve the BI reporting environment. 

It appears that support for the requirements planning process is desirable, to provide structure to the task, and guide the analyst through the task, prompting the creative use of advanced reporting technology. 

Another product that I have helped with is BI Pathfinder – see www.bipathfinder.com 

This tool was, and still is, aimed at those analysts wanting to use a top-down approach, finding out requirements before committing to software or project expenditure. 

However, it is able to work with the output of a documentation tool, to allow rapid development of the specification using the existing BI context deficiencies as the starting point for improvement. 

We’re not yet able to do all this efficiently, but I’m convinced this is the way forward.

There is a way out of the mess we’re in because of poor BI Requirements definition.  The “waterfall”, or top-down, approach isn’t compatible with the instant gratification desire promoted by BI software vendors and exponents of the prototyping incremental development methodologies. 

I accept that we are not going back to the top-down development models for BI systems; executives want faster turnaround time.However, speeding up implementation by short-cutting requirements analysis is only satisfactory if the initial results can be fine-tuned in subsequent iterations to produce a good result.  So the starting point, the first iteration, must be well planned.   Often the initial BI design specifications are incomplete, and do not offer any creative or innovative content or presentation.  It’s like going to McDonalds or Burger King when the objective was “fine dining”.  But, being realistic, nothing is going to change the love affair consultants and IT analysts have with prototyping.  And it isn’t necessary.  All we have to do is to inculcate a habit of finding sensible, and creative, requirements at the outset. 

t size=”3″>Here is my prescription. 

Top-down, or waterfall, methods are seen as being inappropriate  – that leaves two basic options. 

1. Build what  already exists in a more glamorous package, with some catchy phrases like “drill-down” added

2. Employ the bottom-up planning approach, with some minimal top-down content [applied in a structured manner to minimize wasted time]

If we can make good use of the second option we should be well on the way to a consistently better BI context. 

A while back I blogged about gratitude and generosity, which was mostly about how it made ME feel when I was experiencing those feelings in times of change and growth.  What’s the flip side of that coin, or the other end of that stick, or whatever metaphor you want to use?  How does expressing gratitude to others for what they do feel?

Apparently pretty good; or at least good enough to respond with some very kind, personal notes of thanks.  A few weeks back I realized that I use two open source projects that provide exceptional products.  Truly, they’ve transcended the open source motto of "the code is the documentation and RTFM if there were one" and have created wonderful, easy to use products.  I realized that I had not given these people anything in return (I never encountered any bugs/etc to submit patches for!).

I donated, via their website instructions, to a CYGWIN developer and Gallery.  I received personal notes of thanks, expressing real gratitude.  It wasn’t for the money either (I donated 25 USD to each developer) but more of recongition of their contribution.  I get this.  If someone (me) is willing to pay someone whom they’ve never met before, willing to seek out the method (donation pages and paypal hoops), part with real money, while they’re under absolutely NO OBLIGATION or expectation to means that I think they did a great job.

Well they have! 

Have you ever considered donating to an open source project?  What open source projects do you get value from?  Consider dropping them $20 and see how good it makes them AND you feel!  I bet you’ll feel better giving $20 to the Apache foundation than paying your next enterprise software bill.

Like all great open source products, Pentaho Data Integration (Kettle) is a functional product in and of itself.  It has a very productive UI and delivers exceptional value as a tool in and of itself.  Most pieces of the Pentaho platform reflect a desire to keep the large communities around the original projects (Mondrian, JFree, etc) engaged; they are complete components in and of themselves.

When used together their value, as it relates to building solutions increases and exceeds their use independently.  I’ll be the first to admit that Pentaho is still fairly technical, but we’re rapidly building more and more graphical interfaces and usability features on top of the platform (many in the open source edition, but much is in the professional edition).  Much of this work involves making the "whole" (Pentaho)  work together to exceed the value of the pieces (Mondrian, Kettle, JFree, …).

A few things immediately come to mind of why Pentaho and Kettle together provide exceptional value as compared to used individually or with another open source reporting library:

1. Pentaho abstracts data access (optionally) from report generation which gives report developers the full POWER of Kettle for building reports.

   There are some things that are tough, if not downright impossible to do in SQL.  Ever do an HTTP retrieval of an XML doc, slurp in a custom lookup from Excel, do a few database joins and analytical calculations in a SQL statement?  I bet not.  Report developers are smart data dudes; having access to a tool that allows them to sort/pivot/group/aggregate/lookup/iterate/list goes on and on/etc empowers report developers in a way that a simple "JDBC" or "CSV" or "XQuery" alone can accomplish. 
   How is this made possible?
   Pentaho abstracts (optionally, it isn’t forced on customers) the data retrievals to lookup components.  This allows BI developers to use either a SQL lookup (DB), XQuery lookup(XML), MDXLookup (OLAP), or Kettle lookup (EII) to populate a "ResultSet."  Here’s the beauty; reports are generated off a result set instead of directly accessing the sources.  This means that a user can use the same reporting templates, framework, designer, etc and feed/calculate data from wherever they desire.  Truly opens a world of possibiliy where before there was "just SQL" or "ETL into DB tables."

2. bility to manage the entire solution in one place

   Pentaho has invested greatly in the idea of the solution being a set of "things" that make up your BI, reporting, DW solution.  This means you don’t have ETL in one repository, reports managed somewhere else, scheduling managed by a third party, etc.  It’s open source so that’s obviously a choice, but we can add much value by ensuring that someone who has to transform data, schedule that, email and monitor, secure, build reports, administer email bursting, etc can do some from one "solution repository." Managing an entire BI solution from one CVS repository?  Now that’s COOL (merge diff/patch anyone?).

3. onfiguration Management

   Kettle is quite flexible; the 2.3.0 release extends the scope and locations where you can use variable substitution.  From a practical standpoint this means that an entire Chef job can be parameterized and called from a Pentaho action sequence.  For instance, because you can do your DW load from inside Pentaho action sequences that means you can secure it, schedule it, monitor it, initiate it from an outside workflow via web service, etc.  In one of my recent Kettle solutions ALL OF THE PHYSICAL database, file, and security information was managed by Pentaho so the Kettle mappings can literally be moved from place to place and work inside of Pentaho. 

4. etadata and Additional Integration

   Pentaho is investing in making the tools more seamless.  In practice (this is not a roadmap or product direction statement) this means being able to interact with tables, connections, business views inside of Kettle in an identical (at least similar way) in the report designer.  For example, if you’ve defined the business name for a column to be "Actual Sales" Kettle and the Report Designer can now key off that same metadata and present a "consistent" view to the report/ETL developer instead of knowing that "ACT_SL_STD_CURR" is actual sales. 
   Another example is the plans to do some additional Mondrian/Kettle integration to make the building of Dimensions, Cubes, and Aggregates easier.

I’m off to do a presales visit to Ireland tomorrow, and the bit of technology
I’ll be demonstrating is the new Oracle
BI Suite Enterprise Edition. I had to put a demo together to show the
customer, and therefore I was able to spend a few hours this afternoon working
with Oracle
BI Answers and Dashboards. As with my other demos and articles, I used the Global
Electronics Sample Schema, and here’s how it looked at the end of the
afternoon:

The demo starts off with the user logging into the Web
interface, whereapon their default dashboard is displayed. In this case, it’s
got two tabs, one for Marketing Analysis, the other for a Sales Dashboard. When
they click on the other tab, the Sales Dashboard is shown:

I worked out a few good techniques and approaches when putting
the demo together, the first of which was around calculations. The Global Sample
Schema comes with two fact tables; one called "Units" that is
dimensioned by product, time, channel and customer, and another called
"Prices and Costs", that is dimensioned by just product and time. Both
fact tables are automatically joined together when you bring them into the
Semantic Model, so it’s fairly straightforward to bring them into the same
query, like this:

So what if you want to derive some additional columns, say
"revenue" (units * unit price) and "profit" (unit price -
unit cost)? Well, just like Discoverer, you can define the calculation either in
the metadata using the Windows-based Administrator tool, or you can define it
within the report. If you define it in the metadata, it’s an Administrator job
but it’s then available to all users (if you want it to be), if you define it in
your report, then it’s local to that report only.

To define a calculation in your report, you first have to add a
column from the left-hand panel to the report, and in my case I added the Units
column again. Then, you click on the fx button above the
new column, and redefine the formula to suit your calculation, like this:

Then, when you view the results, the calculated column is now in
the resultset. Note also the image in the table header; the product ships with a
number of title icons that you can include in your reports.

Once you’ve put your dataset together, there are quite a few
options around "visualization". In BI Answers, what you’d refer to as
a query in Discoverer is called a "request", and each request can have
one or more "views" associated with it. The most usual view is the
table view, what you get when you first put your query together, and this is
usually accompanied by the "Title" view, which is where you add your
logos and so on, like this:

See the "Title" value in the drop-down list under the
Answers tabs - that’s where you switch between views. Now, if you want to add a
visualization to the table, or any other type of view (a pivot-table, for
example), you click on the view drop-down list, and select the view you wish to
add.

In my case, I’m going to add a Gauge view, to see how it
compares with Discoverer’s Gauge portlet

The differences here are that you can change the size of the
gauges, but other than that the standard gauge is suprisingly similar to the
Discoverer Portlet one. One nice touch though is that you can change the type of
gauge to "bulb", which gives you an out-of-the-box "traffic
light" solution:

This’d be useful if you want to put a row of indicators, say, at
the top of your dashboard page to show whether certain products were selling
according to plan, or whether calls are being answered in time.

Once you’ve added your visualizations, it’s back then to the
View drop-down, and this time you need to select "Compound Layout",
remove the table view, and add in the Gauge view so that it’s all that’s
displayed when the request is deployed to the dashboard:

Then it’s just a simple case of deploying the request to the
dashboard, along with all the others.

As a quick plug, if anyone reading this article is a Discoverer
customer looking to migrate to BI Suite Enterprise Edition, we’re putting
together a migration program in conjunction with Oracle to help you make the
move. If you’re based in the UK, Europe or the USA, myself and my team are
working on a migration methodology to move Discoverer business areas, reports
and queries to BI Suite Enterprise Edition, and we’re looking for customers who
are willing to take part in a pilot program to start taking advantage of BI
Suite Enterprise Edition’s new features. If you’re interested, drop
me a line and we can have a chat about it.

That’s it for now. Apart from putting this demo together, I’ve
just finished off my article for OTN on BI Suite Enterprise Edition, which has
now gone for review and should be back to me with edits in the next month or so.
Apart from that, the other article I recently put together again for OTN, on
Data Profiling with Oracle Warehouse Builder 10gR2, should be up on their site
within the next few days, so check back there if you’re interested in how this
new Warehouse Builder feature works.

For those that don’t know the reference between lilac suit and Pete-s, just google it.    Doesn’t really matter though when compared with the great set of articles that my friend Pete-S is pumping out from the other side of the Atlantic.

Pete has in the trenches practical knowledge building BI and DW systems.  He’s both sharp, and practical (that’s rare you know!).  He’s running a series DW Wisdom and it’s some very useful content.

DW Wisdom

I like that Pete is comfortable enough with his own skills/abilities to question the "age old wisdoms" of DW.  Even if they are found to be true it’s good to see some real scientific "assumption breaking" to prove/disprove reality.

• Use as many small disks as possible – a 1TB disk would be a bad idea for a system that inherently reads large volumes of data, everything would go through a single IO point.

  • Keep all the OLTP tables separate from the DW systems; OLTP has lots of small, fast transactions, DW has slower, big reads. DW loves bitmap indexes, OLTP hates them.

  • Use high degrees of parallel processing

But are these truths still valid?

DI Wisdom (2) - more of the physical

Comparison of OLTP vs DW(OLAP-esque):  Great reference table:

DI Wisdom (3) - departmental business

When people found that their transactional systems were unsuited for BI reporting (perhaps because of the performance impact of running BI on a transactional system, or the transactional system did not hold all of the data required for reporting) they started to look towards dedicated data warehouses.

DI Wisdom (4)

Enterprise DW moves away from the tactical departmental “point solutions” and into something that fits with strategic aspirations of the enterprise. On the face of it having a single solution across the enterprise as distinct advantages:

• there is a single, consistent model of enterprise data

  • there is less duplication of data across the enterprise
  • it is possible to construct a security model such that the right people see all of the data that allows them to their jobs but not the information that is too sensitive for their job role
  • the origins of all of the business data can be traced back to source

In fact these aims are so laudable that they have been hijacked by other IT disciplines such as master data management, risk and compliance management, and business process reengineering.

DW Design (part 1)

I can’t agree with Pete more: A staging, 3NF warehouse, and then presentation layer (marts) I think is a very practical way to seperate concerns, and avoid tight coupling between source and reports.  A la Corporate Information Factory.

For a long while I have favoured a three section data warehouse design: a staging area where raw fact and reference data is validated for referential integrity, a third-normal form layer to hold the reference data and historical fact, and finally a presentation layer to hold denormalised reference data and aggregated fact. The staging layer is ‘private’ to the data warehouse but user query access (subject to business security rules) to other layers is permitted. In some cases it will not be possible to use a denormalised layer; but if you can use one, you should.

DW Design (2) – staging data

As mentioned yesterday, the staging area of the data warehouse has three functional uses:

• It is the initial target for data loads from source systems

  li>

  It validates the incoming data for integrity

p class=”MsoNormal”>It is the data source for information to be published the ‘user visible” layers of the data warehouse

blockquote>

Optionally, it may also be where the logic to transform incoming data is applied.

Great series Pete!  Now if only this were in a book that I could tell all my blog readers and colleagues to purchase! 

The second part of a mini-series on DW design. Today I look at staging data as part of the work flow between the source systems and the published data warehouse.

During one of the sessions on last week’s ODTUG 2006 conference, I saw a very interesting demo by Keith Laker. He discussed the problem of having several dozens of dimensions or descriptors for a particular fact and utter uselessness of a report with more than 40 or 50 qualifiers. Like an overview of salesrecords with […]

The bug of writing many-part posts has bitten - this post is the first in a series on my DW design philosophy. I say mine in the sense “it is the one I use most often” not that I own it!

Here I start with my broadbrush outline

Patrica Seybold Group identifies trends early enough to make a difference. Bridge the gap between information need and ability to make decisions. How can you achieve this and use “terrific search”? In this white paper, Patricia Seybold Group analyzes the answers.

What’s behind the door? An interactive tour of resources and information based on industry expertise and today’s critical information challenges.<br><br>Register and enter a landscape of solutions. Videos, white papers, executive briefs, and guides to products show you how to leverage Information On Demand to lower costs, manage risk and complexity, gain insight, and much more. Open the door and see what Information On Demand can do for you.

Today’s smaller-but-wiser organizations win in the local-to-world marketplace by employing the same technological solutions as the giants. Paradoxically, DB2 Content Manager 2006 gives SMB users a competitive advantage against their domestic peers while leveling the playing field around the world.