BI Blogs

All content, that is! Do you remember the post from Shelley Sessoms about the topics that are especially high on our wish list for publishing through SAS Press? That post generated a lot of response and questions. It’s always so gratifying to see the SAS User Community spring into action to help other SAS users. Thank you, all!

Posts like those also generate a lot of questions from would-be authors.

One of those questions often asked is “How long does my book have to be?” Although some publishers prescribe a certain page count for books, we tend to be somewhat flexible and want your content to drive the length. As more and more content goes online and is delivered in different formats, “page count” and how to deliver the content has become even more flexible.

• If you’re thinking about writing a “book,” we do want to offer a few tips to guide your thinking as you plan the content. Short and sweet can be just what your audience needs. Gone are the days where a certain page count is “required” to give a book the requisite “shelf presence” in a brick and mortar store. If you have a topic that is more the length of a traditional article than a book, please don’t shy away from contacting us with your idea! We have ways to market and distribute this content just as we would a full-length book. Your fellow SAS users may find your article or “booklet” just what they need to answer a specific question.
• Think modular. SAS Press is in the planning stages to offer chapters of SAS Press books for individual sale, and quite possibly as a book is being developed. Over time, our goal is to be able to allow SAS users to combine these chapters to develop their own custom books. The better a chapter stands alone, the better the chance that your content can be marketed and sold in different ways.
• Consider how your full-length book might be developed in stages. Here’s what we mean. Write some chapters and send them to us. While we’re reviewing and editing the chapters, you can work on your next set of chapters. Rather than taking a linear approach to developing the content, we overlap the work so that the schedule is more compact and the content is “finished” in a shorter timeframe. This could be especially important for new SAS technology or topics that address new developments in the market. If chapters are more modular and the content is developed in stages, SAS Press and you serve the SAS user community even better by making the content available even sooner.
• Longer books are not always optimal. A book does not have to address anything and everything in the first edition. As you plan your content, consider the most important topics to publish first. Then, what topics would you add next? Are there topics that are nice to have but not required? Perhaps these could be blog posts or articles. One of your main goals as a SAS author, we know, is to support the SAS user community. Sometimes, publishing a shorter book in a shorter timeframe is the best approach to take.
• Content doesn’t always have to be in book form. Maybe some of your content could be delivered as a blog post or a podcast, or a webinar, or an article. All of these content delivery methods support each other and further promote you as a subject matter expert and thought leader. However, the content doesn’t have to be in written form.

? Contact us and we’ll see what we can do!

This post was cross-posted from Stacey Hamilton’s blog on sascommunity.org

One common issue that gets highlighted when someone uses BI EE is its inability to leverage Parent Child Hierarchies out of the box. 11g release scheduled sometime later next year is supposed to address this seamlessly. Having said that, being consultants ourselves most of the times we find ourselves in situations wherein we would have to use the parent child hierarchies. I would be covering a technique today that can basically address hierarchical reporting on parent-child hierarchies, in the current release itself.

It is possible to mimic value based hierarchical reporting in BI EE in a number of ways. I will provide 3 high level options here. Though there can be other possibilities they would only be slight variations of the 3 listed below

1. The first option is to flatten the hierarchies and convert the parent-child into a level based hierarchy. This is the approach that Oracle’s BI Applications product called CPM Analytics uses. This is a straight forward approach wherein you have a pl/sql or a sql program which basically converts the hierarchy into level based wherein the number of the levels is pre-determined.

2. The second option is to mimic everything using a combination of Answers UI navigation and Repository based drills.

3. The third option is to provide out of the box drills from the repository using a combination of BI Server Federation and Oracle database hierarchical operators. Here there is no need for setting up custom navigation in Answers.

I would be covering the 3rd option which requires fair bit of understanding of Federation, how BI Server uses the logical table sources and the issues inherent with a parent-child hierarchy. I would try to address them in a simplified manner here. Lets take the Global Schema. I will be using a parent child hierarchy table for the Products dimension (shown below)

As you see, it is a simple parent child hierarchical table. The idea behind the technique is to have one Logical table source for every parent-child level. This can be best illustrated by the diagram below

So effectively we would have n-1 logical table sources for n levels. For drilling from Level 1 to Level 2, we would use the Parent to Child Relation. From Level 2 to Level 3, we make the BI Server to switch to another logical table source wherein Level 2 would now be the parent and Level 3 would be the child. But when we drill, the values of Level 1 would have to be propagated across all the table sources. This is where the crux of the technique lies. So the first step in our technique is to alter the above parent child hierarchy table to contain the Level information as well as Level-0 product information i.e every record in the parent-child table should have the Level Number and should have all the Level-0 products that roll into the parent.

In Oracle 10g, this can be done easily through the sql below. We are doing this to ensure that we get the rolled up numbers for every parent. There can be multiple variations of the script below. But all we need is a level number and all the level-0 children of every record in the parent-child table. All the sub-queries below are not actually necessary.

SELECT LEVEL_1, LEVEL_2, PRODUCT_NAME, PRODUCT_ID, LEVEL_NUM
FROM
(
SELECT LEVEL_1,
CASE WHEN LEVEL_1 = LEVEL_2 THEN LEVEL_3 ELSE LEVEL_2 END LEVEL_2,
LEVEL_3 PRODUCT_NAME, LEVEL_3_ID PRODUCT_ID
FROM
(SELECT LEVEL_1,
CASE WHEN INSTR(PATH,'|',1,2) = 0 THEN LEVEL_1
WHEN INSTR(PATH,'|',1,3) = 0
THEN SUBSTR(PATH,INSTR(PATH,'|',1,2) + 1,LENGTH(PATH))
ELSE SUBSTR(PATH,INSTR(PATH,'|',1,2)+1,INSTR(PATH,'|',1,3)-INSTR(PATH,'|',1,2)-1)
END LEVEL_2,
LEVEL_3 LEVEL_0,
PATH LEVEL_1_ID, LEVEL_3_ID, LEVEL_3, LEAF
FROM
(
SELECT SYS_CONNECT_BY_PATH(PARENT_DSC,'|') PATH,
CONNECT_BY_ROOT PARENT_ID LEVEL_1_ID,
PRODUCT_ID LEVEL_3_ID,
CONNECT_BY_ROOT PARENT_DSC LEVEL_1,
PRODUCT_DSC LEVEL_3,
CONNECT_BY_ISLEAF LEAF,
LEVEL
FROM
(SELECT TO_NUMBER(A.PRODUCT_ID) PRODUCT_ID,
TO_NUMBER(A.PARENT_ID) PARENT_ID,
A.PRODUCT_DSC PRODUCT_DSC,
B.PRODUCT_DSC PARENT_DSC
FROM PRODUCT_CHILD_PARENT A,
PRODUCT_CHILD_PARENT B WHERE B.PRODUCT_ID(+) = A.PARENT_ID)
CONNECT BY PRIOR PRODUCT_ID = PARENT_ID) A
WHERE LEAF = 1)) A,
(SELECT DISTINCT PARENT_DSC, LEVEL LEVEL_NUM FROM
(SELECT TO_NUMBER(A.PRODUCT_ID) PRODUCT_ID,
TO_NUMBER(A.PARENT_ID) PARENT_ID,
A.PRODUCT_DSC PRODUCT_DSC,
B.PRODUCT_DSC PARENT_DSC
FROM PRODUCT_CHILD_PARENT A,
PRODUCT_CHILD_PARENT B WHERE B.PRODUCT_ID(+) = A.PARENT_ID)
CONNECT BY PRIOR PRODUCT_ID = PARENT_ID
START WITH PARENT_DSC = 'Total Product') B
WHERE A.LEVEL_1 = B.PARENT_DSC

The idea is to create a table which would hold the output of the above script. Now import this table into the physical layer. Since Product id would no more be unique, make a complex join with the product_id in this table and the fact.

All this gives us is the information required for the first logical table source. Remember when we jump from one logical table source to another we would still be having the filter for Level 1, Level 2 etc. If we leave those columns to be mapped to Parent or child columns, the drills would not work. So, for each additional logical table source we would need an additional Select view within the physical layer. This select view will be a Cartesian product of the filterable columns. Do not worry about the performance much as the Cartesian product would still be producing only valid combinations as we would be filtering them while drilling(but this filter in effect actually does not filter anything from a reporting standpoint). This Cartesian product select view is only for disabling the filters while making the jump. So, for the 2nd logical table source, create a select view in the physical layer using the sql below.

SELECT
B.LEVEL_1 LEVEL_1,
A.LEVEL_1 LEVEL_2,
A.LEVEL_2 LEVEL_3,
A.PRODUCT_ID PRODUCT_ID,
A.PRODUCT_NAME PRODUCT_NAME,
A.LEVEL_NUM LEVEL_NUM
FROM
(SELECT DISTINCT LEVEL_1 FROM PRODUCT_PARENT_CHILD_TBL1) B,
PRODUCT_PARENT_CHILD_TBL1 A

In the same way for the 3rd logical table source we would now need a Cartesian product of 2 tables, one with Level1, Level 2 columns and the other with the source of the first logical table source.

SELECT
B.LEVEL_1 LEVEL_1,
B.LEVEL_2 LEVEL_2,
A.LEVEL_1 LEVEL_3,
A.LEVEL_2 LEVEL_4,
A.PRODUCT_ID PRODUCT_ID,
A.PRODUCT_NAME PRODUCT_NAME,
A.LEVEL_NUM LEVEL_NUM
FROM
PRODUCT_PARENT_CHILD_TBL1 A,
(SELECT DISTINCT LEVEL_1, LEVEL_2 FROM PRODUCT_PARENT_CHILD_TBL1) B

Now make a physical complex join between these 2 aliases with the fact table. In the example above, i have stopped with 4 levels. Depending on your requirement and the depth of the hierarchy, the same procedure above can be repeated for more levels.

Once we have the physical sources ready, the next step is to design the Business Model Layer. As discussed above, we would have 3 logical table sources contributing to the product dimension. And each source would have a mapping as shown below

The ordering of the Logical table sources is very important. Generally whenever there are 2 or more dimensional logical table sources that contribute to the same set of columns, the first logical table source would chosen for querying(not always true – It is dependent on the measure as well). So, when we create a report containing LEVEL1 and a fact measure we would be hitting the parent child table (And not the Cartesian Product views). And the other important aspect to this is the filtering on Level Number. When we move from one logical table source to another, as we are negating the effect of the drill filter using Cartesian product, we would have to explicitly apply the level filters for each logical table source as shown below(For level 1 lts the filter would be equated for Number 1, level 2 to number 2 and so on.

Once this is done, just create the hierarchy as shown below.

This will ensure that our BMM is parent-child hierarchy aware for the product dimension. The drills would be seamless for an end user

There can be many more improvements to above technique. For example, instead of the Cartesian Product select view we could just have another table with repeating Level 1/ Level 2 values. But the idea would remain the same.

In our “People, Process & Politics” series we have discussed how non-technical aspects of business intelligence, data integration or data warehousing projects are the critical components of success. Sorry product vendors, but no matter how impressive the tool, if a company fails on the 3 Ps then the projects using those tools will fail to produce reasonable business value.

On the technology side, enterprises need to organize their integration efforts using an integration competency center (ICC) approach. An enterprise needs to identify the scope of its data integration efforts, define its vision and architecture, and then implement that vision. A blueprint is necessary to build what you want but you also need your resources to effectively execute to build what the business needs. 

ICC organizational Models

Too often people envision an ICC to be a centralized group that controls all integration work. In addition, ICCs are perceived to be a large corporation creation that just does not fit in a small to medium enterprise. On both counts, they could be but they do not have to be. 

There is a wide spectrum of choices from simply agreeing on common practices to actually having a centralized integration development group. The options fall into four categories:

• Common practices
• Technology standards
• Shared services 
• Centralized development

Although the ICC organizational choices above are listed in an increasing level of control, scope and size, it is neither necessary nor recommended for an enterprise to step up the ICC organization ladder. The organizational approach that best fits an enterprise is dependent on its situation rather than some esoteric, one-size-fits-all model.

Common Practices

The simplest ICC model is one that documents and shares common practices across integration projects. Each independent project is able to leverage what was learned from previous integration development efforts without reinventing the wheel. It saves time, reduces costs and enables each project team to concentrate on expanding the overall enterprise’s integration expertise, thereby contributing new or improved practices based on what they’ve learned. In this manner, enterprise integration expertise continues to grow project-by-project rather than being lost as each project is completed and resources inevitably scatter. 

In addition, there is a considerable amount of industry knowledge gained from successful and unsuccessful integration projects that has formed a set of data integration best practices. Tapping into that pool of best practices should improve an enterprise’s integration efforts and help avoid many of the learning mistakes everyone new encounters. Just to set expectations: there is no single recipe that applies to all situations, but rather a data integration cookbook of best practices that an enterprise needs to pick which recipes apply to them.

Technology Standards

The second ICC model involves not only establishing common practices that can be used by all integration projects, but standardizing on a common integration platform. This approach goes beyond sharing ideas by creating a technology platform each independent integration project can tap into. This approach avoids the costly and time-consuming tool evaluation and selection process; avoids the cost of redundant or overlapping software and supporting infrastructure; and, if the integration standard is followed, provides the potential for a common pool of expertise in the enterprise that might be enlisted to work on various projects. 

Standardizing on integration technology enables the integration projects to concentrate on creating business value rather than continually tying up resources in the evaluation, selection, purchase, training, development and deployment of multiple integration technologies.

Shared Services

The third organizational ICC model involves creating a group of dedicated people, but in a virtual or decentralized manner, to develop the integration components of all projects. The ICC determines the strategy, designs the architecture, establishes the common practices and selects the technology platform as prerequisites to its development efforts. The individual integration project teams are still responsible for the development activities and deliverables, however, the ICC assigns resources from their common pool of talent to work on those project. This is a terrific approach to developing and nurturing integration talent and expertise (from an employee perspective) and a very productive manner to leverage individual skills thus reducing costs (from an enterprise perspective.)

Centralized Services

The final organizational model is a completely centralized ICC operation. Typically with this approach, an enterprise-wide integration program is established with a business governing group to fund and prioritize the integration portfolio. In the centralized services model, the ICC operates as the systems integrator responsible for all integration work throughout the enterprise. All data integration components are pulled out of projects and placed into the integration program. Just as with the shared services model, the ICC determines the strategy, designs the architecture, establishes the common practices, and selects the technology platform as prerequisites to its development efforts. However, unlike the previous model, the ICC operates the entire integration program.

Next

We will examine the pros and cons of each ICC model so that you can determine what may work best for your enterprise in follow-up posts in this series.

In a recent post I sketched out an idea I had about consuming data from SSRS data within Excel: rather than have SSRS push data to a new Excel spreadsheet by rendering a report to Excel, what I wanted to do was have an Excel spreadsheet that pulled data from an SSRS report. Why do this? Well, we all know that users always want their data in Excel rather than in any other format. If we take an SSRS report and render it to Excel, though, we have two problems:

1. Getting an SSRS report to render to Excel in exactly the way you want, at best, involves a lot of trial-and-error. Even then when you render to Excel there’s a whole load of Excel functionality that SSRS doesn’t support – for example, you can’t get native SSRS to render a workbook containing a macro.
2. Ever time we run the report, we are creating a new workbook. So once we’ve got the workbook there isn’t much point in doing any extra work in it, for example adding new formulas or charts, because it’s going to be superseded by a newer workbook the next time the report is run.

e Softartisans Officewriter (which MS licensed a long time ago and possibly will appear with SSRS 2008 R2) will solve the first problem, because it allows you to upload an Excel workbook to SSRS which has data injected into it when the report is rendered, but not the second.

However, it is possible to pull data into Excel from SSRS and avoid these problems. Excel allows you to import data from an XML document into a worksheet; since you can get an SSRS report to render to an XML document, all you need to do is hook Excel directly up to the XML file generated by SSRS. Here’s how:

• The key to getting this to work is URL access to SSRS reports. Excel needs to know where the XML file it’s importing is – and you can give it the URL of an SSRS report, and in that URL specify that the report should be rendered to XML. Let’s take the Sales Order Detail report from the Adventure Works sample reports as an example:
  
  On my machine, the URL for rendering this report into XML is as follows:
  http://myserver/ReportServer?%2fAdventureWorks+2008+Sample+Reports%2fSales+Order+Detail+2008&rs:Command=Render&rs:Format=XML
  Paste this into your browser and you’ll automatically get an XML file downloaded; you can find more details on URL addressability of SSRS reports here.
• Now, open Excel 2007, click on the big round Office button in the top left corner, click the Excel Options button and on the Popular tab check the box “Show Developer tab in the Ribbon”. This will ensure you can see the functionality within Excel we’re going to be working with.
• Open the Developer tab and click on the Source button to open up the Source pane, then the XML Maps button in the Source pane, then Add on the XML Maps dialog, and then enter the URL of the SSRS report in the File Name box on the Select XML Source dialog and click Open.
  
• The XML Source dialog will now be populated. Click on a cell in the worksheet, and then right-click on a node in the XML Source pane and choose Map Element to map an element into a cell; click the Refresh Data button in the ribbon to actually bring the data into the worksheet. Here’s what the data from the report above looks like when mapped into Excel:
  

The point is that every time you hit the Refresh Data button in Excel the report is rendered, so you’re able to build your worksheet around live data from SSRS. You can of course pull data directly from data sources like SQL Server in Excel, but the benefit of doing it this way is that you can take advantage of SSRS features like caching and snapshots, and of course as an end user you may not have direct access to the source database anyway.

There are some obvious drawbacks to this approach:

• It’s a bit too technical to set up for end users, except perhaps for the most technical of Excel power-users.
• There isn’t an easy way to pass parameters to reports. You can of course pass parameters through the URL, but it would be great if it could be done from a dropdown box in the worksheet. I think with a little bit of coding you could create an Excel addin that would do this though.
• Rendering to XML isn’t the ideal format for this task – although I’m not sure there is an ideal format (the RPL format used by Report Viewer might be a good candidate but it’s not documented). Books Online has details of how reports behave when rendered to XML; one obvious drawback is that there’s no pagination of data, so if you have a lot of data in your report spread across multiple pages, you’ll get all the data from every page in Excel.

I think this this approach might be useful when you have a large number of existing Excel reports that currently have data copied-and-pasted into them manually and which can’t (for whatever reason) be turned into full SSRS reports.

If you’ve been wondering why I’ve not been posting to the blog recently, it’s because I’ve been grappling with the new release of the Oracle BI Applications that uses Oracle Data Integrator as the ETL engine. This is an interesting release as it does away with Informatica and even the DAC, but it uses the same approach of SDE and SIL mappings but mapped on to ODI and a new tool called the Confguration Manager. So how does it work and how does it compare to the 7.9.5 and 7.9.6 releases of the BI Apps?

First of all it’s worth setting out what this 7.9.5.2 release does, and what it doesn’t do. First of all, the sources and targets that it supports are only a small subset of those covered by the 7.9.5 and 7.9.6 releases. The only source supported is Oracle E-Business Suite 11.5.10 which necessarily is only on the Oracle Database, and the only target supported is Oracle 10gR2. Within E-Business Suite, the only BI Apps modules that are supported are Financials, Human Resources, Supply Chain & Order Management, and Procurement and Spend and moreover, as this is the 7.9.5.2 release – a branch of the 7.9.5 main release – you don’t get any of the new subject areas that are in the 7.9.6. release that continues to use Informatica. Now one thing to bear in mind now is that there is no guarantee that there’ll be a 7.9.6.x release using ODI and therefore you might need to wait until the 11g BI Apps release before you can take advantage of areas like Projects currently covered by 7.9.6, a wait that may well by eighteen months or so (assuming that BI EE 11g is out in the first half of 2010). Finally, if you’re a customer looking to implement the BI Apps, there are going to be far fewer people out there with 7.9.5.2 experience than with mainstream BI Apps experience, and so you’re going to really have to want to use ODI with the BI Apps to go for this version – for most customers I’d still recommend going down the Informatica route at least until the 11g release.

If you take a look at the architecture of the 7.9.5.2 release of the BI Apps, you can see that pre-built ODI interfaces, packages and transformations take the place of the equivalent mappings, workflows and transformations that come with the 7.9.5 and 7.9.6 version of the BI Apps. Knowledge Modules within ODI handle the extracts, loading and transformation of data between source and target and the BI Apps ships with a number of custom knowledge modules to replicate, for example, the mapplets feature in Informatica.

The stated aims for Oracle in this ODI-backed release are firstly, to speed up data loading through the use of database set-based transformations; secondly, to make deployment quicker and simpler, and thirdly, to make all the technologies more tightly integrated. For the first objective, we’ll have to wait and see until we can run the same execution plans using both Informatica and the DAC, and I’m hoping to have these test results in place for my session on this release at the forthcoming Open World in San Francisco, and for our Training Days event just after this in London. Deployment and Integration is however better (at least once you work out how it all installs, it took me about six attempts before I got it all working).

If you look at this topology diagram from the Oracle documentation, there are three servers involved in the Informatica-driven 7.9.5 and 7.9.6 architecture, made up of the repository and integration servers for Informatica, and the DAC server that controls the Informatica workflows. There are also Informatica and DAC clients which are used for customizing mappings, and for controlling and monitoring ETL execution plans respectively.

If you take a look at the corresponding topology diagram for 7.9.5.2, the number of servers has gone down to one, as has the number of clients.

Now I think this is slightly disingenuous as you typically use two ODI agents – one for workflow, one for executing the packages – and there are also two clients, the ODI client and also the Configuration Manager which takes the place of the DAC. But it’s still definately the case that the 7.9.5.2 release seems to have less “moving parts”, there is no DAC server (with all of it’s technologies to learn) controlling Informatica (another, non-Oracle technology to learn) and there’s no DAC repository as all of it’s functionality has now been moved into the ODI repository or into a set of tables used by the Configuration Manager. It’s not quite the simplification that Oracle claim but if you’re already an Oracle shop and use ODI, it’s just another set of ODI projects and models that you need to run.

So once you’ve got it all installed, what does it all look like? Well once you’re in the ODI Designer application, the Projects view shows you all of the PLP, SDE, SIL and so on folders that correspond to the various adapters in the Informatica repository, except of course they only cover EBS 11.5.10 as a source.

You can also see the area where execution plans are stored; the concept around these is a little different to how they worked in the DAC before, but you still select subject areas to load and let the Configuration Manager (the DAC replacement) turn these into calls to the equivalent to Informatica workflows in this release, ODI “packages”.

The ODI Designer application also holds the data definitions for the source and target databases, and you use this tool to create the data warehouse tables in the target database before you commence your first load. If you’ve read my posting on adding partitioning to the 7.9.5 release of the Oracle BI Applications you’ll be interested to note that the 7.9.5.2 release supports range-based partitioning on tables (though not compression, though this should work if you manually apply it after the initial table creation), presumably creating bitmap indexes “local” rather than the default “global”.

So with the DAC gone, what’s it’s replacement? Well much of the functionality has moved into the various ODI client tools and the DAC repository itself is now just part of the ODI project metadata, but you still need a user-friendly tool to select and then run the data warehouse load routines and the tool that does this is called the Configuration Manager. This is a web (ADF)-based application that runs on Weblogic that currently you need to separately install after the main BI Apps installation (I understand in future this will be automatic, at least for Windows platforms). This has a subset of the functionality of the DAC Console, allowing you for example to construct and then run execution plans, however it doesn’t have the data lineage and metadata exploration features that I found useful in the DAC Console, presumably this functionality will be (or already has been) replaced by packaged reports that run against the ODI repository for use with Oracle BI EE.

The Configuration Manager interface is quite easy to use, once you log in you are presented with a list of options where you can define parameters, build and then execute an execution plan, and then monitor the execution of those plans.

Creating execution plans is a simpler process than with the DAC, as you don’t need to define and populate the various parameters that are passed between the DAC Server and the Informatica Server, and there is no “create orderered tasks” step either that sequences all of the mappings that are required to load your chosen subject areas. In the screenshot below, you can see where I’m choosing the various subject areas that I want to see in my execution plan.

In future postings in this series, I’ll look in more detail at the technical differences under the covers in this release, how you go about doing the initial load and how the customization process works. For now though, you can download BI Apps 7.9.5.2 from OTN, and if you’re going to use it you’ll need a full license copy of ODI 10.1.3.5 as well in order to use the mappings.

I picked up the term “creative analyst” in late June on the phone with Lyzasoft CEO Scott Davis. But what does he mean?

He described one analyst he’s known of. This guy arrived at a new job with strong recommendations for his ability to tear apart a dataset. He could slice, dice, build related charts and pivot tables — but only with canned data. That is, data someone had given him. This analyst struggled with synthesis — blending separate datasets, for example, or making a formula to derive values, or simply experimenting and asking unforeseen questions.

The ability to improvise and create something new is a “prime differentiator” among analysts, says Davis.

Many of these creative, synthesizing analysts, he says, also tend to feel they have a personal brand. They have a style of charting they prefer, for example, and they produce a distinct set of information that is uniquely attractive to their subscribers within the company.

“You can sort of think of them as publishers,” he says. “They create these things that are in some ways more useful than reports from the BI tool. And they gauge their effectiveness by how many people follow them.”

Such lists have been around quite a while. Before PCs, people did the same kind of thing in hardcopy, producing a dozen or two binders with a distribution list clipped on the cover.

There’s a future, too. Davis expects to see Enterprise 2.0 — social networking within businesses — grow fastest among these analysts. They already have the social habits: commenting, trust, wikis, etc.

He says, “A spreadmart is nothing but a primitive social networking mechanism.”

Looks like they have finally reached a partial agreement after months of negotiating.  So in about 3 to 6 months Yahoo will be utilizing Bing search behind the scenes.  Guess they are trying to cut into Google’s search which handles more than 70% of the searches on the web.  According to this article Google actually handles close to 90% of the searches.

Microsoft and Yahoo Agree on Search Deal

The high tech titans
- IBM (IBM), HP (HPQ), Oracle, Microsoft (MSFT), SAP (SAP) and EMC (EMC) – and
their  smaller software brethren have
been nibbling at tuck-in acquisitions. Still, they have been building their cash hordes and watching on the sidelines to
determine the extent of the recession (it did not turn out to be the 2nd coming of the Great Depression) and
IT spending cutbacks.

Some have speculated that they  were waiting for the valuations of their
acquisitions to bottom out before they pounced. If that was the primary factor, the buying binge would be in
full swing since software valuations have rebounded significantly since the market lows.

Instead, the reason for their restraint is probably their conservative financial management - one of their under appreciated characteristics. They have virtually no debt (the credit
crisis does not affect their operations as it does their
customers) and they generate cash flow from software
maintenance fees. This means the titans can wait until they feel the time is right to start up their acquisition machines. That
time is when they have some confidence
that IT spending will pick up or when one of their rivals starts the acquisition
engines.

IBM’s announcement
yesterday that it will be acquiring SPSS (SPSS) for $1.2 billion in cash is
likely to be the trigger event for its
rivals to jump back into the M&A waters with both feet.  Just as dozens of software acquisitions a few years ago
culminated in business intelligence (BI) powerhouses Business Objects, Hyperion and Cognos being bought by
SAP, Oracle and IBM respectively, the IBM acquisition is going to trigger purchase of enterprise
software companies. 

The categories that are holding their own through the recession and will grow
significantly when IT spending rebounds are the most desirable acquisitions. These categories include BI
(including business/predictive, data mining), data integration or middleware, SaaS (software-as-a-service) or
on-demand software and enterprise application vendors oriented towards specific industries or
business processes.

We follow these
software categories in our two indexes:

• BI/DW Index
• On-Demand Software Index or SaaS (software-as-a-service) Index

These indexes track
both the potential buyers and sellers in the enterprise software M&A
activity.

Open source software
(OSS) will be in play IF the titans can figure out how they can create a
profitable  business model to justify the
investment or if they feel they can leverage the software to hurt rivals. 
(This is not a great business strategy, but egos sometimes supersedes business sense.) Emerging technologies such as columnar databases will
continue to tuck-in purchases.

In our next post on
acquisitions, we will look at the crystal ball and match buyers with sellers.
In our match making we will speculate on
the likely match-ups and who we feel should walk down the aisle together.

I am pleased to announce that PASSMN SQL Summit 2009 presenter sessions and topic suggestions are now being accepted.

On Friday, September 25, 2009 PASSMN will be hosting its first (and FREE) SQL Server Summit event and we are currently looking for speakers to talk about SQL Server related topics in regards to Administration and Maintenance, Development, and Business Intelligence.  This includes topics such as upgrades, best practices, tips/tricks, etc.  We are also looking for case study related presentations, so if you have implemented a SQL Server solution that you would like to share with the group we are very interested in hearing your story.

To submit a session, use the Call for Presenters survey.  The survey will be open until August 17th at 9 AM.  Sessions should be approximately 40-50 minutes.

Call for Presenters survey

In an effort to create the type of event that the PASSMN community is looking for, we are also taking suggestions on topics.  If you aren’t interested in presenting but know a topic you are very interested in hearing about, please complete the following survey.  We do ask for some contact information with it so that we can contact you if we need clarity.

Topic Suggestion Survey

A

Most of those versatile researchers of the data-driven world — the business analysts, creative analysts, or even cowboy analysts — probably run on a different schedule from their managers. Paul Graham’s latest essay compares “manager’s schedule” and “maker’s schedule.”

I’m no analyst, just a writer. But the more analysts I meet, the more I find that analysts and journalists share a surprising number of characteristics. One of them, I think, is the tendency to run on “maker’s schedule,” as explained by Graham:

When you’re operating on the maker’s schedule, meetings are a disaster. A single meeting can blow a whole afternoon, by breaking it into two pieces each too small to do anything hard in. Plus you have to remember to go to the meeting.

Meetings are interruptions. They mess up any breadcrumb trail of thoughts that has not yet been laid down in permanent memory. That’s why I’m writing this at 11:39 at night, when I can let a ghost of an idea hang unattended without fear of new email or a phone call dissolving it. Graham writes about once keeping a dinner-to-3 a.m. workday.

Now he wears a VC hat, and he can’t avoid meetings. So he schedules “office hours” at the end of the day.

He hopes that pointing out the two kinds of schedule will make it easier for the “makers.” Their schedule tends to offend managers.

I hope this insight spreads.

What do you think? Do analysts you know work this way? Post a comment.

So IBM is buying SPSS to give it analytics capability and to allow it to better compete with Oracle and Microsoft.

Although I have never thought of Oracle or SAP providing true Analtytical capability, so I would say this gives IBM a one up.

Although Although, Oracle brought Thinking Machines ages ago which had a credible Data Mining capability/tool but then swallowed them up and delivered nothing that customers really used (well not in NZ anyway)

So will IBM leverage SPSS to provide a compelling message or lose it in its already massive product stack?

Also SAP/BO and SPSS were already partnering and playing nicely, so is this a first foray into the rumoured IBM buyout of SAP?

And as always where does this leave SAS, HP and Teradata?

So many questions  and only time well tell I suppose.

But one thing that is a fact is the big boys are getting bigger, and there are fewer companies out on their own.

I am trying to remember the days of Mainframe Accounting Systems (McCormack & Dodge, CA Mastermind etc) and see if there is a parallel, but that was more death by new entrant (SAP, Peoplesoft, Oracle Apps etc)

So can you remember a time where massive vendor consolidation happened and the companies left out survived, let me know if you can.

Ps, I am undecided if I will add Sybase to my SAS/HP/Teradata mix as I cant see how they can survive in the BI market (Sybase IQ etc) but then they still have a credible Relational Database.

Big news in our industry this morning: IBM plans to buy analytics software vendor SPSS for $1.2 billion.

In one sense, I’m sad to see SPSS disappearing into the large IBM stack. Besides SAS, SPSS was one of the last independent analytic software companies. A colleague says, “It’s the end of the analytics cold war.”

I’ve been saying all along that analytics is required for success. Yes, data integration, data quality, and query & reporting are important too but, as W. Edwards Deming says, “The object of taking data is to provide a basis for action.” If you collect data with a purpose (and even if you opportunistically collect data with no specific purpose), you need analytics to use that data to make the best decisions, take the best actions and foresee the best opportunities. Analytics is what helps derive the most value from data and what allows continuous learning and improvement

SAS has always been focused on helping customers extract truths from data with analytics — analysis is our middle name. Others are realizing the value of analytics, as reflected in this acquisition and in the interest around R and other analytical capabilities.

Why do analytics matter now more than ever? The growing volumes of data — both structured and unstructured — and the business world’s growing complexity and pace of change require analytics. Companies are under enormous stress. They’re faced with surviving the recession, preparing for an eventual recovery, competing globally… analytics is relevant everywhere.

Beyond providing validation for the analytics marketplace, what else does this acquisition mean? What do we think it says about the two companies involved? To be honest, it’s not too much of a surprise. SPSS has been on the market for years. They’ve been unable to raise funds for innovation, and their growth has been stagnant. Unlike SAS, they have not provided the extensibility many require to meet evolving needs.

IBM bought SPSS to fill a gap and to continue its strategy of buying the individual bits for a framework that lets them compete better with Oracle, SAP and others. IBM is primarily a hardware and services provider with a small fraction of their overall revenue coming from its software group.

What about joint SAS, IBM customers? How does the acquisition affect SAS’ partnership with IBM? Our partnership with IBM is sponsored within their hardware group, and this new IBM acquisition is being handled through their software group. The collaboration we have with IBM Global Services and the Hardware Group will continue—and this is in the best interest of customers.

While IBM spends time integrating SPSS into its software stack, SAS will continue to innovate. We’ll maintain focus on providing value to our customers, evolving with them to meet their changing needs. We’ll keep listening to customers and keep coming up with new ways to solve their business problems. We are free to partner, collaborate and innovate with any partner to help deliver the analytics infrastructure needed regardless of environment, industry, level of expertise or application area. SAS will keep investing generously in R&D as we have always done and we’ll continue to provide customers with many options.

Overall, this acquisition further validates that there’s a difference between plain old BI and business analytics. The ongoing differentiation in our marketplace will come from analytics. And last year, who led the marketplace in analytics? Companies chose SAS Analytics more often than the next most popular 16 analytics suppliers COMBINED (including SPSS). That’s according to IDC’s June 2009 Worldwide Business Intelligence Tools 2008 Vendor Shares report.

I’m very pleased to announce details of our second-ever Training Days event, to be held at the Bloomsbury Hotel in London on October 27th – 29th 2009. This year I’m especially pleased to be co-hosting the event with Venkat Janakiraman and Christian Berg, two experts in the Oracle BI EE world and star speakers at the recent Rittman Mead BI Forum in Brighton.

Like last year’s Training Days event, the focus is on in-depth technical knowledge around Oracle BI EE, Essbase, Oracle Data Integrator and the BI Applications, with each of us running several sessions over the three days. Unlike other “beginners-level” courses this event focuses on real project issues and we’ll be going through the reality of designing, architecting, performance-tuning and integrating products across the range of Oracle’s BI and EPM product stack. I’m particularly pleased to have Venkat on board because of his deep knowledge of OBIEE and Essbase integration, and it’s great to have Christian presenting as well as he’s worked on so many successful OBIEE and Oracle BI Applications projects. Anyway, here’s the agenda for the three days:

• Day 1

• Oracle BI and EPM architecture overview – Mark Rittman
• Oracle BI EE Data Modeling against DW and 3NF sources – Mark Rittman
• Oracle BI Delivers + Integration with Java and BI Publisher – Venkat Janakiraman
• What’s new in Oracle BI, DW and EPM from Oracle Open World – Mark Rittman

• Oracle BI EE Data Modeling against Essbase – Venkat Janakiraman
• Leveraging MDX functions and calculations in OBIEE – Christian Berg
• Integrating Security across OBIEE and EPM – Venkat Janakiraman
• I can do this in Hyperion – how do I do it in OBIEE? – Christian Berg and Venkat Janakiraman

• OBIEE Systems Management with OEM BI Mgmt Pack – Mark Rittman
• OBIEE Configuration Management Best Practices – Christian Berg
• ODI functionality in Oracle BI Applications – Mark Rittman
• ODI Integration with Essbase, Planning and Oracle EPM Suite – Venkat Janakiraman

As with our BI Forum in Brighton earlier in the year, this is a great opportunity to meet and train with real-world Oracle BI and EPM consultants who have real project experience to back up their training materials. With lots of discussions and the opportunity for you to bring your own laptops and try out some of the techniques we discuss, this is a rare opportunity to take your OBIEE, Oracle BI and EPM knowledge up to the next level. Unlike the BI Forum though, this is an intensive three days with just three trainers, where we’ll look at topics in much greater detail and follow a common theme from start to finish.

Like last year, you can either book for just one day, or you can book for all three in a “three for the price of two” deal (though last year, everyone ended up staying for all three days). There is also an “early-bird” offer where we are discounting both rates by 10% up to the end of September, so if you’re interested in coming take a look at the event website for more details. The rate includes lunch on all three days and a special meal on the second night where we can kick back and discuss the world of Oracle BI in one of London’s top restaurants.

I’m really excited about this event and I know Christian and Venkat are as well. We only have twenty spaces available, so if you’re interested make sure you register now and we’ll see you in London in October.

Session submission is now open for SQLBits V, which, as I said a week or two ago, is taking place near Newport in Wales on November 19th-21st. We’re looking for sessions on any SQL Server-related subject and we actively encourage submissions from people with little or no conference speaking experience – we always make room on the agenda for new faces. And it’s not an exclusively-UK event either: we’ve had quite a few European and US speakers at previous events, so if you’re looking for an excuse to come to the UK for a holiday then this is it!

You can submit sessions by completing a speaker profile:
http://www.sqlbits.com/Profile/SpeakerProfile.aspx

and then going to:
http://www.sqlbits.com/Profile/MySessions.aspx

I hate having to maintain the same ETL logic in two different places; it violates everything I know about good coding practices. When using Java, PL/SQL, or even shell script, a good developer will modularize particular bits of his or her code to make sure distinct business logic is maintained in only one place. But this rule of thumb is difficult in ETL coding: mappings are set-based processes that encapsulate an entire load from source to target. Removing some portion of this logic into a distinct function or method, which is the paradigm for OLTP systems, usually won’t scale in an ETL load. But developing one mapping that always performs efficiently no matter the data-set is also difficult: a mapping that runs efficiently for a days worth of data might not be the best alternative for running the historical load, for instance. A good example is with a partition by join process, which can cause data sets to increase exponentially with the more records that are processed.

One solution is to write two versions of the same mapping: one that executes the daily load, and one that executes the historical load, each optimized for the particular situation. But this violates the one immutable rule mentioned above: we just simply shouldn’t maintain our business logic in two places. A bug-fix here and a bug-fix there, and pretty soon, one process is a rev or two behind the other. And with Agile approaches being more common in data warehouses than non-Agile methodologies, it’s not uncommon to have to run historical versions of certain mappings more than once.

So I sometimes get around this by developing a single mapping within a process flow, and providing logic in the process flow that breaks up the load into sets… usually months or quarters. This allows me to optimize the mapping for smaller data-sets–which honestly is the most important–without requiring me to write it again with a different approach for the historical load. I can easily run the daily load through this same process without concern, because the process flow will simply iterate over the mapping a single time.

First, I’ll create source and target versions of the SH.SALES table to use for my example. I’ll also create a version of the SH.TIMES table in the source schema.

SQL> create table source.sales_stg as select * from sh.sales;

Table created.

Elapsed: 00:00:05.77
SQL> create table target.sales_fact as select * from sh.sales where 1=0;

Table created.

Elapsed: 00:00:00.09
SQL> create table source.times as select * from sh.times;

Table created.

Elapsed: 00:00:02.26
SQL>

I’ll need to create two database functions: one that returns the distinct number of desired sets in our source table–in this case months–and another that returns the particular set based on a ranking value. This will allow me to use a WHILE LOOP activity in a process flow because I can determine: how many distinct sets to work through, and which set we should use on each iteration. I wrote these functions in generic fashion, so they are not tied to particular tables. Instead, the table name is passed as a parameter. The other parameter is the particular column I use to divide the source table into sets. In this example, the column is a DATE column, and to give me some textual attributes to use in the function, I join the source table to the TIMES table, but you could use whatever DATE dimension is currently in your warehouse.

The first function gives me the distinct number of sets in the source table. For increased performance, partitioning the source table on the dividing column is probably a good idea.

SQL> CREATE OR REPLACE FUNCTION get_num_months ( p_table VARCHAR2, p_column VARCHAR2 )
  2        RETURN NUMBER
  3     AS
  4        l_num_months NUMBER;
  5        l_sql LONG;
  6     BEGIN
  7
  8        -- build the dynamic SQL statement
  9        l_sql :=
 10        'SELECT COUNT(DISTINCT calendar_month_desc) num_months '
 11        ||'FROM '
 12        ||p_table
 13        ||' tt JOIN source.times dd ON (dd.time_id = trunc(tt.'
 14        ||p_column
 15        ||'))';
 16
 17        -- execute the statement binding the statement into a variable
 18        EXECUTE IMMEDIATE l_sql
 19        INTO l_num_months;
 20
 21        RETURN l_num_months;
 22     END get_num_months;
 23  /

Function created.

Elapsed: 00:00:00.42
SQL> select get_num_months('source.sales_stg','time_id') from dual;

GET_NUM_MONTHS('SOURCE.SALES_STG','TIME_ID')
--------------------------------------------
                                          48

1 row selected.

Elapsed: 00:00:01.71
SQL>

The second produces a distinct year-month combination for each successive month that I’ll be working with. This is why I use the CALENDAR_MONTH_DESC column in the function above: it’s very easy to build a process that filters on a single textual column for ease of use, but you could modify the function to work on start and end dates instead. So when I’m looping through all the distinct sets in this table, I’ll be passing this function a ranking number, which is the current iteration, to get back the distinct CALENDAR_MONTH_DESC column for this particular set.

SQL> CREATE OR REPLACE FUNCTION get_year_month ( p_table VARCHAR2, p_column VARCHAR2, p_rank NUMBER )
  2     RETURN VARCHAR2
  3  AS
  4     l_yearmo VARCHAR2(7);
  5     l_sql LONG;
  6  BEGIN
  7
  8     -- build the dynamic SQL statement
  9     l_sql :=
 10     'SELECT calendar_month_desc '
 11     ||'FROM (  SELECT calendar_month_desc, Rownum rn '
 12     ||'FROM ( SELECT DISTINCT calendar_month_desc '
 13     ||'FROM '
 14     ||p_table
 15     ||' tt JOIN source.times dd ON (dd.time_id = trunc(tt.'
 16     ||p_column
 17     ||')) ORDER BY calendar_month_desc )) WHERE rn = :rn';
 18
 19     -- bind the statement into a variable
 20     EXECUTE IMMEDIATE l_sql
 21     INTO l_yearmo
 22     USING p_rank;
 23
 24     RETURN l_yearmo;
 25  END get_year_month;
 26  /

Function created.

Elapsed: 00:00:00.00
SQL> select get_year_month( 'source.sales_stg','time_id',1 ) year_mo from dual;

YEAR_MO
----------
1998-01

1 row selected.

Elapsed: 00:00:00.90
SQL> select get_year_month( 'source.sales_stg','time_id',2 ) year_mo from dual;

YEAR_MO
----------
1998-02

1 row selected.

Elapsed: 00:00:00.74
SQL>

I use a bind variable for the filter on the ranking value to allow the database to only soft parse for each subsequent time it executes this function, which will be once for each distinct set. I would also recommend partitioning the source table by the column used to divide the sets, in this case, TIME_ID.

I build the following mapping to move the rows from the source table to the target table for a specific month. Notice the mapping INPUT PARAMETER operator that is being used as a filter in this operation to pull only the rows for the specific month at hand.

The source portion going into the joiner:

The join criteria:

And the target portion coming out of the joiner:

With the mapping in place, now I just need to put the whole thing together with a process flow. Note: for simplicity sake, and for ease of viewing, I didn’t create any error or warning handling for this flow. Of course, I don’t recommend that in practice.

The first thing the process flow does is execute the GET_NUM_MONTHS function and store that value in a variable called NUM_MONTHS. This value is stored before the WHILE LOOP is entered, so the value persists for the entire process flow.

After this step, I formally enter the loop structure, and the first step is an ASSIGN activity. In this case, I am assigning the variable YEAR_MONTH_RANK to be the current value of YEAR_MONTH_RANK, plus 1. This variable starts out with a value of 0, so the first time it runs through, it gets assigned the value of 1. YEAR_MONTH_RANK is actually our iterator… it increases by one each time I loop through the process.

The next activity in the loop is to call GET_YEAR_MONTH, passing in the current value of YEAR_MONTH_RANK. The results of the function will give me a year-month combination, that will be stored in the variable YEAR_MONTH.

By the time the process flow executes the mapping, the variable YEAR_MONTH contains a textual attribute for the year-month combination that I want to run for this set. That variable gets passed into the mapping via the mapping input parameter, and the data set is filtered from the source table. This is a WHILE LOOP, meaning that focus stays inside the loop as long as the CONDITION is met. The condition for this loop is that YEAR_MONTH_RANK < NUM_MONTHS. So the loop will execute for every distinct month in the SALES_STG table.

You can see from the Control Center job that the process flow is indeed looping through all the different months:

And you can also see that the count in the source and target tables is exactly the same:

SQL> select count(*) from source.sales_stg;

  COUNT(*)
----------
    918843

1 row selected.

Elapsed: 00:00:00.39
SQL> select count(*) from target.sales_fact;

  COUNT(*)
----------
    918843

1 row selected.

Elapsed: 00:00:01.65
SQL>

Today I was notified that a new release was available for BIDS Helper with the newly added Version Notification that was included with version 1.4.1.0 (BIDS Helper 1.4.1.0 available).

So what is new and what has been fixed?  Good question.  The new functionality that has been added is an SSIS Design Warnings Scanner which is similar to the SSAS AMO Warnings (Best Practice Alerts) that are part of BIDS 2008.  So now you can get Best Practice alerts with SSIS development.  Really cool 

This new release also includes fixes for:

• Change Variable Scope in Event Handler
• Dimension Health Check Fails with GUID Keys

and download the latest version – http://bidshelper.codeplex.com and start leveraging all of the features that the BIDS Helper add-in provides for developing SSAS, SSIS, and SSRS solutions with BIDS.