BI Blogs

For the second year running I’m late celebrating my blog birthday (it was yesterday); my only excuse is that I’m still reeling from the amount I’ve eaten over the last two weeks. But four years of blogging… wow… it feels like ages.

And to a certain extent I feel that, after all this time, I’m running out of things to say here. The actual writing of blog entries isn’t a problem, it’s more the problem of having something to write about. Part of the problem is me: I don’t want to write about things I don’t find interesting so I haven’t gone down the route of turning the blog into a MDX tutorial (Bill Pearson does that much better than I ever could, and only Mosha could ever cover the advanced stuff properly), but at the same time I’m not coming across so many MDX/SSAS issues or obscure features as I used to. Part of the problem is, too, that SSAS2008 was so light on new features that it didn’t provide me with much new to write about. So I’m hoping that Gemini, Kilimanjaro, Excel 14, Azure etc will give me something to get my teeth into in 2009; I’m sure they will. If not, well, I’ve always wanted to spend some time getting into Mondrian and other open source BI technologies. And with the economy the way it is I suppose I’ll have a lot more spare time for learning new stuff in the coming twelve months…

But anyway, bear with me and keep reading! For those of you who have stuck with me for the last four years, thanks, and best wishes for 2009.

During SQL PASS 2008 summit, I gave full day preconference seminar “Deep Dive to MDX”. The evaluation forms now have arrived, and the scores for the content, relevance and depth portion of the seminar are very good. The scores are not as high, however, for the time allocated (people argued that it should’ve been  2 days instead of one), and for the materials (since nobody received any materials). Both points are valid, one day turned out to be not enough, and perhaps two days wouldn’t have been enough either, since I got into less than a half of material that I had prepared. Also, due to some issues, I couldn’t hand down the presentation material – slides and MDX queries/calculations. These issues are cleared now, and I will start uploading the slides from the presentation. I don’t suppose they will be very useful for people who didn’t attend the session, since slides really only contain major talking points, and the rest was delivered verbally, but they probably will be very useful for people who did attend.

The time allowed to cover the following subjects:

• Overall Architecture
• Subcubes
• Sonar
• Storage Engine query plan
• Storage Engine cache
• Formula Engine query plan

• Block mode

More detailed Table of Content can be found here.

I will do series of posts uploading one subject at a time. The first subject is “Overall Architecture”, and it gives high-level picture of the components discussed further in the presentation. The slides are here.

Slightly unrelated announcement: MDX Studio 0.4.11 was also released today, with multiple UI enhancements, docked windows (Visual Studio style) etc. Versions for SSAS 2005 and SSAS 2008 are available, and can be downloaded from here.

Una de nuestras lectoras, Ivette Marrero, nos comenta acerca de dos interesantes artículos que se publicaron recientemente en TDWI (probablemente la publicación más prestigiosa sobre Business Intelligence y Data Warehouse a nivel mundial).

Se trata de BI: The Year in Review, de Stephen Sowyer y Analysis: BI Transformation in 2009, de Ted Cuzillo, y pretenden dar una visión de lo más importante en el campo del BI en el 2008 y lo que se espera sean las tendencias en el 2009.
Os recomiendo vivamente que los leais, pero en cualquier caso, os hacemos un pequeño resumen.

2008:

- Fue un año mucho más tranquilo que el 2007 (recordemos que se compraron por los grandes Hyperion, Cognos y Business Objects). Se trató más bien de un año de consolidación.
- Podríamos decir que fué el año de la eclosión de los DW Appliances (en España-Europa aun no muy extendidos, como Aster Data, ParAccel, Vertica, InfoBright y Kognitio. O las versiones de fabricantes como Oracle, HP, Teradata…
- Microsoft se volcó con el Business Intelligence: compro DataAllegro (DW Appliance), y sacó un SQL Server 2008 muy orientado al BI, así como el anuncio del nuevo AS (proyecto Gemini)
- Necesidad de realizar análisis online y acceso a grandes cantidades de datos. Los fabricantes se han puesto las pilas y han investigado para mejorar tecnologías que les permitan realizar muchos análisis que hasta ahora no eran fctibles en tiempo. Para ello, mucho esta teniendo que ver el uso de las Bases de Datos orientadas a columnas.

2009:
- Todo va a estar influenciado por la crisis. Influirá en la inversión en BI, en el cambio a tecnologías más económicas, a un uso más efectivo, etc…
- Se puede producir un lento declinar de los grandes fabricantes BI: Cognos, BO, Oracle, Microsoft, etc… en favor de otros más novedosos y económicos: Tableau, Quantrix, Panorama…
- Se buscará la sencillez, el uso individualizado, y aun siendo importante, se dejará en segundo término la integración.
- Cada vez se usará más el BI, por parte de los usuarios de negocio.
- El Business Intelligence cada vez ha ganado más peso en la sociedad y los negocios. Recordad la noche electoral americana.
- BI hará cada vez más foco en el factor humano y menos en la tecnología.
- Las pymes empezarán a hacer un uso extensivo de aplicaciones Business Intelligence.

Finalmente, desde el equipo de TodoBI, os queremos desear a todos nuestros lectores un Feliz Año 2009 y esperamos seguir viéndonos por aquí.
También esperamos hacer ‘nuestro propio resumen’ de las tendencias que vemos para el 2009, que desde luego, las hay bien interesantes.

Today is New Year’s Eve, and a kind of review of the year, here’s ten of my thoughts on what happened in Oracle B&DW in 2008.

1. Oracle Business Intelligence Enterprise Edition.

   2008 was the year that OBIEE went from early-adopter technology (at least for general Oracle customers) to a mainstream BI tool. A good proportion of these implementations were accompanied by the Oracle BI Applications, showing that the message of “buy vs. build” was resonating, typically with organizations who had tried building their own “first generation” DW themselves. Of course we’re still waiting for OBIEE 11g, with Answers+ being typically the 11g component (along with the web-based template builder for BI Publisher) typically being the bits that most customers are waiting for. That said, there were two major releases in the 10g codestream in 2008, with the 10.1.3.3.3 release providing Essbase support and the 10.1.3.3.4 release providing integration with EPM Workspace, as well as the new Sample Sales application that shows off development best practices. Certainly from my perspective I’m finding that customers are now implementing Oracle BI in the form of OBIEE, rather than Business Objects or Cognos, and for previous users of Discoverer there’s a lot of customer satisfaction with this new bit of technology. 2008 was a good year for Oracle BI and it’s good to have a product that finally holds it own in the marketplace.p>Essbase. Speaking of Essbase, who would have thought that this OLAP server would be the hot new technology in the Oracle world in 2008? My article on Essbase and OBIEE was in the top 10 tech articles for OTN last year, and everyone I know is either learning Essbase or dusting down their skills. Most customers I speak to who are looking to implement OBIEE also want to talk about EPM (Planning, Financial Consoidation etc) at the same time, and Essbase is the common factor that brings all of these together. Conference-wise, the biggest new area at ODTUG Kaleidoscope last year was the Essbase track, and look out for further Essbase content, together with crossover Essbase / OBIEE content, at next year’s Kaleidoscope. Now’s the time indeed to know your ASO from your BSO, your MDX from your SQL.

2. Oracle OLAP. Whilst Essbase has been grabbing the limelight, Oracle OLAP has finally delivered, six years on from the initial 9i releases, on the promise of integrated OLAP in the database. Cube Organized Materialized views have provided SELECT-level integration with SQL and query rewrite, whilst the new analytic expressions language in 11g looks likely to make it’s way into regular Oracle SQL as a way of simplifying analytic expressions. Just like Essbase and MDX will be must-have skills for Oracle BI implementors, Oracle OLAP, like materialized views, partitioning and compression, will be must-have skills for Oracle DW implementors. It’ll also be interesting to see whether Oracle OLAP gets parity with Essbase as a multi-dimensional data source in OBIEE 11g and Oracle BI Administrator 11g, if that’s the case (in terms of importing data sources) then it’ll be simple choice between in-database OLAP and external server OLAP when it comes to adding analytic horsepower to your OBIEE implementation.

3. Oracle Data Integrator. Whilst OBIEE and Essbase have been getting all the news, 2008 has been curiously quiet for Oracle Data Integrator. A rather low-key Data Quality add-in came along with the 10.1.3.4 release, but whilst I hear about the tool talked about by customers occaisionally, I don’t see many implementations. 2009 should be different though as the 10.1.3.5 release is the first to be certified with the Oracle BI Applications; I suspect judgement will be reserved until customers see how well it works as an alternative to Informatica in this scenario, a lot of which won’t be down to ODI but more down to how will it’s integrated with the DAC and how well (first time) the migrated Informatica mappings work.

4. Oracle Warehouse Builder. Whilst ODI has been pointed to as being the future of Oracle ETL, Oracle Warehouse Builder has quietly gone about becoming the default ETL tool (including the option of hand-coding) for Oracle shops. Ironically, of course, everyone now says they like it once they hear it’s been phased out or integrated with Oracle Data Integrator, and a few frustrations remain particularly around the Byzantine rules around control centers, locations, configurations and local/remote databases, but the 10.2.0.4 release was a solid one and most of the functionality new to 10.2 (Paris) now works well. I remember going into Oracle customer sites and having to persuade them to use OWB, now it’s the default, and there’s a good product management team who take the time to blog, get out to conferences and engage with the community. Truly a product success story.

5. Exadata and HP Oracle Database Machine. Of course the big news in 2008, at least from the Oracle product marketing machine, was Exadata and the Oracle Database Machine. Realistically it’s not something you’re going to buy for yourself, plug into your laptop and give it a whirl, but it addresses a market segment that Oracle clearly felt they were losing, and the geek inside all of us loves a new bit of hardware to go with our software. I think, like RAC and so on, we’ll reserve judgement until non-Oracle database specialists get their hands on it, but it was good to hear about databases, and data warehousing, at Open World in September and I suspect 2009 will bring similar groundbreaking news from the DW front.

6. ng>Alternative Analytic DBMS Technologies. One side-effect of the Exadata announcement, at least from my perspective, was that it opened up the question as to whether new hardware types are required when working with very large data warehouse databases. At the same time, vendors such as Vertica, Netezza and ParAccel raised their profile through hiring ex-Oracle product development people, reaching out to the blogger community and advocating non-traditional hardware architectures. At this point it’s difficult to say whether they are just a flash in the pan (I wouldn’t want to predicate my business on one hardware innovation) or whether we’re experiencing a Cambrian explosion of new technologies and vendors, but it’s certainly raised the provide of VLDB and data warehouses and their particular hardware needs.

7. The Economy. Outside of Oracle of course, the big news was the economy. Will customers pull in their horns and cancel all non-critical IT projects, or will the need to innovate despite cuts in budgets mean that BI&DW is seen as the only way to work out what’s important and what’s not? Certainly (fingers crossed) we’ve found that demand for BI&DW skills has if anything gone up, as an industry we’re able to deliver value fast and of course we focus on ROI and business benefits. I suspect that unless we experience a kind of IT spending “nuclear winter” then demand for BI that “delivers” will in reality be stronger than ever in 2009, like all recessions it’s a time for innovation and rapid business change, and organizations with a good view of their business going forward as well as going back will be the ones that emerge into 2010 stronger than ever.

8. Rittman Mead. 2008 was a transformative year for us, with our team going from Jon and I up to six full-timers and a number of associates. We’ve focused on the quality of our process, delivery and methodology, and as always we share whatever we learn and try and put back into the community as much as possible. As a small and agile organization we think we’re well placed to handle whatever the economy throws at us in 2009, and we’re looking forward to delivering on our book, developing further our methodology and delivering business-changing Oracle BI&DW systems for our customers.

9. Looking to 2009. Technology-wise, 2009 will be the most significant year for product releases for many years. OBIEE 11g should be out at some point (the first half of 2009 looks most likely), with Answers+, a new scorecarding application, a metadata API for the semantic layer and the Action Frameworks amongst the new features. OWB and ODI will become further fused together, we may get some new Essbase point releases and of course the 11gR2 release of the database should at least be announced in 2009. To my mind, 2010 is likely to be the year of convergence, but 2009 will have enough new releases to keep us busy and with the betas out soon, we’ll be writing the “how-tos” and seminar material in time for the official releases.

So that’s it for 2008; thanks to everyone who’s read the blog, good luck for 2009, and see you all back here in January.

Recently Jeff Kaplan wrote an interesting blog post called,” Will the Rising Cost of Sales Cost SaaS Companies VC Funding?” His article is in response to this post from Phil Wainewright on LucidEra’s Pipeline Healthcheck. Jeff raises the following concern about the view of the long-term viability of the SaaS model in the VC community:

“So, my concern isn’t whether SaaS is a profitable business model. Instead, my concern is whether the VCs, private equity firms and other traditional funding sources are going to retreat from the SaaS market because they have unrealistic expectations for this sector.”

Recently we asked VPs of Sales at software as a service (SaaS) companies the following question:

If you could ask a VC anything about your role as a VP of Sales, what would you ask them?

Here are some of the questions we received:

SaaS-Specific Questions:

• Do you prefer you SaaS companies to customer acquisition or bookings? Why?
• What are you thoughts on channel vs direct for SaaS companies?
• What is the next SaaS vertical that attracts you?

Skills-Based Questions:

• What is the most important trait you look for in a VP of Sales? When interviewing for a VP of Sales, what are the primary qualities that are most important to you? What do you look for in a good sales leader?
• Any tactical coaching for the VP of Sales on what to do giving the economy?
• What are the 3 fatal mistakes Sales VP’s make and how to correct them?
• What is the role of the VP of Sales on an executive team?

Board Meeting Questions:

• What are the key metrics you want to see at board meetings assuming numbers are being hit and/or assuming there was a bad quarter? What do you like to see from the VP of Sales for board meetings?
• What level of detail do you want to see in board meetings - key status or actual forecast/pipeline deals?
• Besides good news, what other information could be delivered in the board meeting to make you confident that the VP of Sales is executing?
• What makes a good board presentation from sales?

Working with the Board Questions:

• How should the VP Sales work with the Board to help with selling referrals to their network?
• In ‘09 planning are you involving the VP of Sales for realistic input on sales goals?
• What do you do if the truth about sales contradicts what the CEO is saying?
• Any advice on how to handle a CEO who protects the board from hearing the real situation?
• When the VP of Sales provides their board update, what kind of feedback drives you crazy?
• What puts and takes do you like to see from a revenue and EBITDA standpoint?
• How and when should a VP of Sales present “bad news” on lowering the forecast?
• What most frustrates you when the VP of Sales delivers bad results?

General Opinion Questions:

• What does “improving sales effectiveness” really mean?
• Is it better to fess up early that the product is “not marketable” or keep plowing ahead to get results?
• In a startup, whats are the key milestones you like to see before scaling the sales team?
• Given the challenging times we are faced with today, and some of the uncertainty of the future how should a VP Sales be prioritizing their time now vs. last year?
• Should the VP Sales meet with VC’s independently of the CEO?
• How do you deal with Board members who have no operating experience?

As many VPs of Sales sit staring at the fax machine facing the last day of 2008, it seemed like a good time to put these questions forward for reflection and hopefully a little enlightenment. What are your questions? Do any of these questions surprise you? Got some answers / opinions you’d like to share?

I hope you have a fantastic 2009 (and great last day of 2008).

When teaching people about business intelligence (BI), data integration and data warehousing, after introductions and reviewing the syllabus or agenda, I present my jargon and acronym slide. I always tease that there will be a quiz on these terms. I do this with people from IT, business groups, software vendors or students (in a Master’s degree program at an engineering university.) It does not matter what the technical expertise of the crowd - they always laugh.

Any IT specialty has its jargon, but we seem to have more than our share in business intelligence. Having a lot of terms and acronyms is usually fine, but in BI we do not always agree on the definition and, even worse, we sometimes use different terms for the same thing.

If the experts, industry analysts and pundits cannot agree then how does everyone else understand what is going on? How do people learn this field and leverage others’ experiences?

My New Year’s wish is that it becomes easier for people to get through the jargon and build or have access to better BI solutions.

My jargon list includes these terms or acronyms:

• Business Intelligence (BI)
• Performance Management (PM)
• Operational BI
• On-Line Analytical Processing (OLAP)
• MOLAP, ROLAP, DOLAP
• Analytical Applications
• Predictive Analytics
• “Slice & Dice” and Drill down
• Data Mining
• Data Visualization
• Dashboards, Scorecards
• Key Performance Indicators (KPIs)
• Data Shadow Systems or Spreadmarts
• DW & BI Appliances
• Data Warehouses (DW)
• Enterprise Data Warehouse (EDW)
• Data Marts (DM)
• Operational Data Stores (ODS)
• Hub & Spoke Architecture
• Data Integration
• Extract, Transform & Load (ETL)
• Enterprise Application Integration (EAI)
• Enterprise Information Integration (EII)
• Extract, Load & Transform (ELT)
• Change Data Capture (CDC)
• SOA (Service Oriented Architecture)
• Real-time Access, BI, DI or DW
• SaaS (Software-as-a-Service) or On-Demand Software versus On-Premise Software
• Cloud Computing
• E/R Modeling versus Dimensional Modeling
• Dimensions & Facts
• Star & Snowflake Schemas versus 3NF (3rd Normal Form)
• Conformed Dimensions
• Slowly Changing Dimensions (SCD)
• MDM (Master Data Management)
• CDI (Customer Data Integration)
• PIM Product Information Management)
• Open Source Software (OSS)
• Relational versus Columnar Databases
• Unstructured Data
• Enterprise Data Mashups
• Data Governance
• Metadata Management
• Enterprise Information Management (EIM)
• BICC (BI Centers of Excellence)
• ICC (Integration Centers of Excellence)
• Data quality (DQ) & data cleansing
• SMP versus MPP

Happy New Year

P.S. The graphic was generated by using Wordle. This is not an endorsement of the site.

Here’s a way-out thought I had over the Xmas break for a new approach to building BI reports….

Have you, when you’ve asked a typical non-technical business user what they want a report to look like, asked them to draw a quick sketch? I do all the time - I find seeing what the user wants the report layout to look like is much the best way to understand what they want and for the user it’s the best way to express their requirements. So on the back of the proverbial envelope you’d get something like this:

…and then go back to your desk and write the query and design the report layout in something like SSRS. So - why can’t we cut out a step and go direct from the sketch to the report design? I can see two options for the first step here:

• Using a tablet PC you write some software that works a bit like OneNote, but where the user can draw the outline of a report freehand. Unfortunately tablet PCs just aren’t that common and the really tech-phobic end user wouldn’t feel comfortable using one.
• The user draws the report on paper and then the drawing gets scanned; definitely something the most computer illiterate manager would be comfortable with.

take the freehand drawing and:

• Interpret the freehand lines into the borders of a table, and
• Interpret the text on columns and rows as either
  • Explicit selections of members, or
  • Set expressions

ers of a table should be from a freehand drawing must be possible (although implementation would be well beyond me). Interpreting what the user has written they want on columns and rows would present more problems:

• Handwriting recognition is notoriously difficult to do well, and usually the software needs to have a bit of practice to get good. On the other hand, in this particular scenario it should be easier because the user won’t be writing just any old text. For instance, if we assume that we’re working with an Analysis Services data source, we know that any text is either going to be the name of a member or something that will resolve to a set expression; we also know, for instance, if there are what look like multiple member names on the same position on the same axis they will all have to come from the same hierarchy.
• Resolving text to member names is all very well, but turning sentences into set expressions would be trickier. It seems reasonable to think that phrases like "Products where sales is greater than £100" could be interpreted effectively, but whether your average business user can write something as clear as that is debatable. Any tool would certainly have to prompt the user to confirm that the interpretations it has made was correct, and do to that it would have to resort to the kind of techy interface that the tool is trying to get away from.
• Similarly there are going to be ambiguities that need to be resolved when looking at a the design. For example, the drawing might have the years 2006, 2007 and 2008 on columns. But does this mean the report should always have these three years on columns, or the last three years with data, or something else?

dn’t work like magic, but at the same time I think it would offer some advantages over current report design tools, the designers of which have fallen into the trap of building a UI on top of the functionality they’ve got  available in MDX or SQL, rather than building a UI for what the user actually wants to do. After all, don’t you think that it’s actually very difficult to lay out anything other than the most simplistic reports in most report design tools, compared to how easy it would be to draw the report?

While LucidEra’s business intelligence predictions continue to receive interesting feedback (including comments from IDC’s Henry Morris), industry analyst Frank Gens has posted a video of IDC’s 2009 predictions to promote their new research and website. The video is a fairly comprehensive overview of their top 10 prediction themes facing IT in the coming year. While he foresees doom and gloom for the IT and telecom industries, predictions #3 and #8 bode well for the market shift to SaaS business intelligence. 

3) The Move to “the Cloud” Will Accelerate

4) Re-Invention of Information Access and Analysis

ntainer”>

Many times, I am brought in to help develop KPI’s for an organization. When it comes to the Marketing subject area, I find myself getting most excited by the clickstream data, an often overlooked yet rich source of correlative opportunity for the bold and analytical minds that seem to not exist within the core team itself. While marketing practitioners are some of the most adapt at doing it, I believe they either do not realize themselves to be doing it or more often, the company stigmatizes them as non-analytical, sales oriented as opposed to the true data analysts that they are. After all, didn’t Market Research originate out of Marketing, which tend to house the company’s statisticians? Here is a sample dashboard for measuring Marketing Efficacy.

It’s Boxing Day over in the UK, and this Christmas I’ve been staying over in Ireland with the in-laws. This afternoon I’ve been looking after the kids whilst Janet and her sister pop out to the Christmas Sales, and in-between watching Kung-Fu Panda I’ve been working on some star transformation examples (as you do), trying to answer some deceptively simple questions I was asked at a customer engagement earlier in December. All of this stemmed from a bit of help I gave one of the customer’s developers, who was struggling to get a star transformation working on their data warehouse query. In the end it turned out that a star transformation was taking place (more on this later on), but as we were talking I was asked a few simple questions;

• Firstly, if with nested loop joins, Oracle starts at the smallest dimension table, joins it to the fact table, and then progressively joins the results of this to the other dimension tables, why is a star transformation, that joins all dimension tables to the fact table at once, more efficient?
• Secondly, what exactly goes on during a star transformation?;
• Thirdly, how do you interpret a star transformation execution plan?,
• And fourthly, when did Oracle star adding messages to execution plans to tell you a star transformation took place?

Now I have to admit I did a sort of “hand-wavy” gesture and pointed out the BITMAP MERGEs and BITMAP ANDs in the execution plan, and explained how Oracle could filter data on multiple bitmap indexes very efficiently, but it got me thinking that in reality, I wasn’t too clear myself on just how star transformations actually worked, particularly to the level where I could explain the process to a novice. When it comes to recognizing star transformations in execution plans, with me it’s a bit like the famous quotation about censors spotting obscene material in films - “I’ll know it when I see it” - in that up until now I could never properly explain what’s going on but I know one when I see one. So, I thought it’d be worth dusting down the old SH Sales History sample schema and running some example scripts to go through how this feature works.

To start off, I create a copy of the SH.CUSTOMERS and SH.PRODUCTS tables, and two copies of the SH.SALES table. I then add primary key constraints to the two dimension tables. foreign key constraints to the fact tables, and then create bitmap indexes on one of the fact tables followed by b-tree indexes on the other fact table. Finally, I gather statistics on the tables and indexes so that I’m then ready to start the tests. The version of Oracle is 11.1.0.6 on Windows XP SP2.

SQL> set echo on
SQL> set autotrace traceonly
SQL> set pages 400
SQL> set lines 500

SQL> -- first of all, create copies of the customers, products and sales tables

SQL> -- with indexes and constraints applied as required

SQL> drop table sales_star;

Table dropped.

SQL> drop table customers_star;

Table dropped.

SQL> drop table products_star;

Table dropped.

SQL> -- first, the tables

SQL> create table sales_star
  2  as
  3  select * from sh.sales;
Table created.

SQL> create table customers_star
  2  as
  3  select * from sh.customers;
Table created.

SQL> create table products_star
  2  as
  3  select * from sh.products;
Table created.

SQL> -- then the required constraints

SQL> alter table customers_star add constraint cust_star_pk primary key (cust_id);
Table altered.

SQL> alter table products_star add constraint prod_star_pk primary key (prod_id);
Table altered.

SQL> alter table sales_star add constraint sales_prod_star_fk foreign key (prod_id) references products_star;
Table altered.

SQL> alter table sales_star add constraint sales_cust_star_fk foreign key (cust_id) references customers_star;
Table altered.

SQL> -- then the bitmap indexes on the fact table foreign key columns

SQL> create bitmap index sales_star_cust_bix on sales_star(cust_id);
Index created.

SQL> create bitmap index sales_star_prod_bix on sales_star(prod_id);
Index created.

SQL> -- then the bitmap indexes on the dimension attributes

SQL> create bitmap index customers_star_gender_bix on customers_star(cust_gender);
Index created.

SQL> create bitmap index customers_star_city_bix on customers_star(cust_city);
Index created.

SQL> create bitmap index products_star_subcategory_bix on products_star(prod_subcategory_desc);
Index created.

SQL> -- finally, gather table and index statistics

SQL> analyze table sales_star compute statistics for table for all indexes for all indexed columns;
Table analyzed.

SQL> analyze table customers_star compute statistics for table for all indexes for all indexed columns;
Table analyzed.

SQL> analyze table products_star compute statistics for table for all indexes for all indexed columns;
Table analyzed.

Ok, so we’re ready to roll. If I first run an ALTER SESSION command to turn off star transformations, and I then run a typical star query against the fact table with regular b-tree indexes on the dimension key columns, my execution plan looks like this:

SQL> alter session set star_transformation_enabled='false';
Session altered.

SQL> -- run a sample query, with star transformations disabled for the b-tree indexed fact table

SQL> select sum(quantity_sold)
  2  from   sales_no_star s, products_star p, customers_star c
  3  where  s.prod_id = p.prod_id
  4  and    s.cust_id = c.cust_id
  5  and    p.prod_subcategory_desc = 'Memory'
  6  and    c.cust_city = 'Oxford'
  7  and    c.cust_gender = 'M';

Execution Plan
----------------------------------------------------------
Plan hash value: 2214940036

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                               |     1 |    50 |   289   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE                  |                               |     1 |    50 |            |          |
|*  2 |   HASH JOIN                      |                               |   211 | 10550 |   289   (1)| 00:00:04 |
|   3 |    TABLE ACCESS BY INDEX ROWID   | CUSTOMERS_STAR                |    58 |   812 |    16   (0)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS  |                               |       |       |            |          |
|   5 |      BITMAP AND                  |                               |       |       |            |          |
|*  6 |       BITMAP INDEX SINGLE VALUE  | CUSTOMERS_STAR_CITY_BIX       |       |       |            |          |
|*  7 |       BITMAP INDEX SINGLE VALUE  | CUSTOMERS_STAR_GENDER_BIX     |       |       |            |          |
|   8 |    NESTED LOOPS                  |                               |       |       |            |          |
|   9 |     NESTED LOOPS                 |                               | 25523 |   897K|   272   (0)| 00:00:04 |
|  10 |      TABLE ACCESS BY INDEX ROWID | PRODUCTS_STAR                 |     2 |    32 |     2   (0)| 00:00:01 |
|  11 |       BITMAP CONVERSION TO ROWIDS|                               |       |       |            |          |
|* 12 |        BITMAP INDEX SINGLE VALUE | PRODUCTS_STAR_SUBCATEGORY_BIX |       |       |            |          |
|* 13 |      INDEX RANGE SCAN            | SALES_NO_STAR_PROD_BIX        | 12762 |       |    26   (0)| 00:00:01 |
|  14 |     TABLE ACCESS BY INDEX ROWID  | SALES_NO_STAR                 | 12762 |   249K|   135   (0)| 00:00:02 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("S"."CUST_ID"="C"."CUST_ID")
   6 - access("C"."CUST_CITY"='Oxford')
   7 - access("C"."CUST_GENDER"='M')
  12 - access("P"."PROD_SUBCATEGORY_DESC"='Memory')
  13 - access("S"."PROD_ID"="P"."PROD_ID")

Statistics
----------------------------------------------------------
        139  recursive calls
          0  db block gets
        597  consistent gets
         72  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

          1  rows processed

So if we read through the execution plan, starting at the right-most, top-most line (line 10), we can see the following steps happening (correct me if I’m wrong here)

• The PRODUCTS_STAR table is accessed via a bitmap index on the subcategory column, with two rows being estimated as being returned (steps 10,11 and 12)
• The filtered rows from the PRODUCTS_STAR table are then nested loop joined to the SALES_NO_STAR table, with the PRODUCTS_STAR table driving (steps 8,9, 13 and 14)
• Around 25,000 rows are returned from this join and are then hash joined to the CUSTOMERS_STAR table, returning around 211 rows
• This data is then sorted and then aggregated to give us the final results.

So the overall costs of this execution plan is around 289, around 897k or so of data is being accessed, two joins take place with the first one joining a two row dataset with a 12000 row dataset to return 25000 rows, with the second one joining this 25000 row dataset to a 58 row dataset. So that’s a lot of rows moving around.

A variation on this is when we still have star transformations disabled, but we change the b-tree indexes on the fact table to bitmap indexes, in preparation for running a full star transformation. In this case, the execution plan looks a little different, it’s still not a star transformation but what it looks like is something not unlike an old-fashioned star join, where the two dimension tables are cartesian joined together before the results are then nested loop joined to the fact table.

SQL> -- now run it for the bitmap indexed fact table

SQL> select sum(quantity_sold)
  2  from   sales_star s, products_star p, customers_star c
  3  where  s.prod_id = p.prod_id
  4  and    s.cust_id = c.cust_id
  5  and    p.prod_subcategory_desc = 'Memory'
  6  and    c.cust_city = 'Oxford'
  7  and    c.cust_gender = 'M';

Execution Plan
----------------------------------------------------------
Plan hash value: 2730556593

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                               |     1 |    50 |   387   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE                   |                               |     1 |    50 |            |          |
|   2 |   NESTED LOOPS                    |                               |       |       |            |          |
|   3 |    NESTED LOOPS                   |                               |   211 | 10550 |   387   (1)| 00:00:05 |
|   4 |     MERGE JOIN CARTESIAN          |                               |   117 |  3510 |    36   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID  | PRODUCTS_STAR                 |     2 |    32 |     2   (0)| 00:00:01 |
|   6 |       BITMAP CONVERSION TO ROWIDS |                               |       |       |            |          |
|*  7 |        BITMAP INDEX SINGLE VALUE  | PRODUCTS_STAR_SUBCATEGORY_BIX |       |       |            |          |
|   8 |      BUFFER SORT                  |                               |    58 |   812 |    35   (0)| 00:00:01 |
|   9 |       TABLE ACCESS BY INDEX ROWID | CUSTOMERS_STAR                |    58 |   812 |    36   (0)| 00:00:01 |
|  10 |        BITMAP CONVERSION TO ROWIDS|                               |       |       |            |          |
|  11 |         BITMAP AND                |                               |       |       |            |          |
|* 12 |          BITMAP INDEX SINGLE VALUE| CUSTOMERS_STAR_CITY_BIX       |       |       |            |          |
|* 13 |          BITMAP INDEX SINGLE VALUE| CUSTOMERS_STAR_GENDER_BIX     |       |       |            |          |
|  14 |     BITMAP CONVERSION TO ROWIDS   |                               |       |       |            |          |
|  15 |      BITMAP AND                   |                               |       |       |            |          |
|* 16 |       BITMAP INDEX SINGLE VALUE   | SALES_STAR_CUST_BIX           |       |       |            |          |
|* 17 |       BITMAP INDEX SINGLE VALUE   | SALES_STAR_PROD_BIX           |       |       |            |          |
|  18 |    TABLE ACCESS BY INDEX ROWID    | SALES_STAR                    |     2 |    40 |   387   (1)| 00:00:05 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("P"."PROD_SUBCATEGORY_DESC"='Memory')
  12 - access("C"."CUST_CITY"='Oxford')
  13 - access("C"."CUST_GENDER"='M')
  16 - access("S"."CUST_ID"="C"."CUST_ID")
  17 - access("S"."PROD_ID"="P"."PROD_ID")

Statistics
----------------------------------------------------------
        139  recursive calls
          0  db block gets
        697  consistent gets
        118  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Now if you look at the rows, bytes and cost columns in the execution plan, the cost of the plan is about 50% higher than the previous one, but the amount of bytes and rows that move around are considerably less than the one before it, and step 4 in the execution plan shows a MERGE JOIN CARTESIAN that to me looks like it’s cartesian joining the two dimension tables. Now apart from watching Kung-Fu Panda I also happened to bring a copy of Jonathan Lewis’ Cost-Based Oracle Fundamentals along with me (a surprise present for the kids over Christmas) and so turning to page 258 I can see this is the old behavior of Oracle before star transformations were available, and could well be used when multi-column indexes (or apparently, multiple bitmap indexes) are available on a fact table and the cartesian product of the dimension tables isn’t too large. Whichever way, it’s one other way of combining fact and dimension tables when star transformations aren’t available, and it could well be what you see if you’ve set things up for star transformations but not actually turned them on (see this other article from the blog where we can also see them occurring), but they’re not really what we’re after.

Now if we turn on star transformations, here’s the execution plan that we’ll get:

SQL> alter session set star_transformation_enabled='true';

Session altered.

SQL> select sum(quantity_sold)
2  from   sales_star s, products_star p, customers_star c
3  where  s.prod_id = p.prod_id
4  and    s.cust_id = c.cust_id
5  and    p.prod_subcategory_desc = 'Memory'
6  and    c.cust_city = 'Oxford'
7  and    c.cust_gender = 'F';

Execution Plan
----------------------------------------------------------
Plan hash value: 2697143339

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                               |     1 |    20 |    13   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                    |                               |     1 |    20 |         |     |
|   2 |   TABLE ACCESS BY INDEX ROWID      | SALES_STAR                    |    13 |   266 |    13   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS     |                               |       |       |         |     |
|   4 |     BITMAP AND                     |                               |       |       |         |     |
|   5 |      BITMAP MERGE                  |                               |       |       |         |     |
|   6 |       BITMAP KEY ITERATION         |                               |       |       |         |     |
|   7 |        TABLE ACCESS BY INDEX ROWID | CUSTOMERS_STAR                |    29 |   406 |     9   (0)| 00:00:01 |
|   8 |         BITMAP CONVERSION TO ROWIDS|                               |       |       |         |     |
|   9 |          BITMAP AND                |                               |       |       |         |     |
|* 10 |           BITMAP INDEX SINGLE VALUE| CUSTOMERS_STAR_CITY_BIX       |       |       |         |     |
|* 11 |           BITMAP INDEX SINGLE VALUE| CUSTOMERS_STAR_GENDER_BIX     |       |       |         |     |
|* 12 |        BITMAP INDEX RANGE SCAN     | SALES_STAR_CUST_BIX           |       |       |         |     |
|  13 |      BITMAP MERGE                  |                               |       |       |         |     |
|  14 |       BITMAP KEY ITERATION         |                               |       |       |         |     |
|  15 |        TABLE ACCESS BY INDEX ROWID | PRODUCTS_STAR                 |     2 |    32 |     2   (0)| 00:00:01 |
|  16 |         BITMAP CONVERSION TO ROWIDS|                               |       |       |         |     |
|* 17 |          BITMAP INDEX SINGLE VALUE | PRODUCTS_STAR_SUBCATEGORY_BIX |       |       |         |     |
|* 18 |        BITMAP INDEX RANGE SCAN     | SALES_STAR_PROD_BIX           |       |       |         |     |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("C"."CUST_CITY"='Oxford')
11 - access("C"."CUST_GENDER"='F')
12 - access("S"."CUST_ID"="C"."CUST_ID")
17 - access("P"."PROD_SUBCATEGORY_DESC"='Memory')
18 - access("S"."PROD_ID"="P"."PROD_ID")

Note
-----
- star transformation used for this statement

Statistics
----------------------------------------------------------
1  recursive calls
0  db block gets
194  consistent gets
17  physical reads
0  redo size
428  bytes sent via SQL*Net to client
416  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
1  rows processed

So what’s happening here then? Well for a start, the cost of the plan has gone down to 13, from the 289 and 387 shown by the previous two execution plans. The largest number of rows we ever process is 29, with a maximum of 406 bytes of data compared to 897k of data in on of the other execution plans. Just looking at this plan compared to the other two, it certainly looks a lot more efficient to the other two, but what exactly is going on when the star transformation happens?

Well the first thing to understand about a star transformation is that it’s not a new kind of join, it’s more of a join strategy. When Oracle is allowed to consider star transformations and your tables are set up correctly (bitmap indexes on the fact table dimension key columns, constraints declared between the tables and so on), what happens is that your query is rewritten from, in my example, something like this:

select sum(quantity_sold)
from   sales_star s, products_star p, customers_star c
where  s.prod_id = p.prod_id
and    s.cust_id = c.cust_id
and    p.prod_subcategory_desc = 'Memory'
and    c.cust_city = 'Oxford'
and    c.cust_gender = 'F';

to this:

select sum(quantity_sold)
from   sales_star s, products_star p, customers_star c
where  s.prod_id in (select pr.prod_id
                     from products pr
                     where pr.prod_subcategory_desc = 'Memory')
and    s.cust_id in (select cu.cust_id
                     from customers cu
                     where cu.cust_city = 'Oxford'
                     and cu.cust_gender = 'F')
and    s.prod_id = p.prod_id
and    s.cust_id = c.cust_id;

Now this functionally returns the same data as the previous version of the query, but crucially it allows the cost-based optimizer to execute the query in the following way:

1. Firstly, steps 4 through to 18 initially build a list of dimension table ROWIDs for the filter conditions selected, and using the dimension primary keys, access the corresponding bitmap indexes on the fact tables. These bitmap indexes are then logically AND to provide the fact table final access bitmap.
2. Steps 2 and 3 then access the fact table using this bitmap, which is converted to ROWIDs.
3. If the query requested some of the dimension attributes, the next step (not shown in this execution plan) would probably be a couple of hash joins to join the (reduced) fact table back to the dimension tables
4. The final steps 0 and 1 are the initial SELECT statement and the final sorting and aggregation of the data.

From my perspective, the crucial thing to be aware of in the star transformation join strategy is the way that the bitmap indexes on the fact table are used. By creating these subqueries in the alternative version of the SQL statement, Oracle can identify which rows in the individual fact table bitmap indexes are of interest, and the nature of bitmap indexes means that they can then be logically ANDed, OR’d and so on very quickly to identify exactly which fact table rows are of interest to us, just Oracle does when it filters a dimension table on more than one bitmap indexed column. Once the fact table has been filtered in this way it’s then much cheaper to join the result to the individual dimension tables, and this is why a star transformation can be much more efficient than a series of nested loop joins between the fact table and the dimension tables - you end up filtering the fact table much earlier on and therefore end up shifting much less data around to answer your query.

So, that in my mind answers the questions “what does a star transformation actually do?” (it rewrites your query so that the fact table can be filtered using a logical AND of the bitmap indexed dimension key columns), and it also explains what’s going on in a star transformation explain plan compared to a regular explain plan. One question it didn’t answer though was when Oracle started adding the “star transformation used for this statement” message to explain plans - looking back at my previous examples, I started seeing it added to explain plans back from Oracle 10gR1, I’m not sure if it appeared in 9iR2 or earlier. I’m also not sure how accurate it is - I’m sure I’ve seen explain plans in 10gR2 that looked very much like a star transformation had taken place, but this message didn’t appear as part of the plan output (I used DBMS_XPLAN.DISPLAY if I remember correctly). If anyone knows any more about this feature then let me know.

Tenemos la suerte de contaros que en el último nº de la revista Gestion del Rendimiento, en su número 10, que os podéis descargar más abajo, se publica un artículo muy interesante de nuestro compañero Alvaro Galán, llamado ‘El proyecto de Cuadro de Mando’. (pag 24)

Descargar Revista.

Este artículo nace de la experiencia en múltiples proyectos de cuadro de mando desarrollados, entendiéndose como tales los que el usuario puede “utilizar” y no “construir” (si bien las posibilidades de “personalizar” son grandísimas, existe una sutil diferencia entre personalizar y construir), y pretende determinar qué factores pueden conducir al éxito de este tipo de proyectos.

“En los proyectos de cuadro de mando, el único factor que determina el éxito es un detalle que se conoce solo tras cerrar el proyecto: Que el cuadro de mando se está utilizando”

This article is part of a series discussing the Kimball Group’s “34 Subsystems of ETL“. The Subsystems are a group of “Best Practices” for delivering a BI/DW solution. In my articles, I discuss how each Subsystem can be implemented in SSIS or hand coded in Visual FoxPro.

You should keep a careful eye on the performance of your ETL system. It isn’t enough to simply schedule jobs and watch them run and run and run. You have got to put some effort into improving your processes so that the entire data warehousing operation works to its optimal level.

In order to discover areas of improvement, you will need to monitor as many processes as you can.

That said, I’ll skip a discussion on network, server, and operating system level performance monitoring (which includes monitoring disk access, memory usage, processor time, etc.). I am not an expert in this area and I simply do not know all the best practices. If you have fast, up-to-date servers, and you do a good job of developing ETL processes that mind their business and play nice, then there is likely not much you can uncover at this level anyway. You’ll need an expert. However, if you have some strange problems (like a load process that seems to take longer and longer over time; a package locks up mid-run; if you can’t seem to get anything else to run on the server while you load data), then exploring an application such as Performance Monitor in Windows is a good place to start.

Here are some good resources (which I had bookmarked):

• Computer Performance
• Network Performance Daily (blog)
• Computer Performance Monitoring
• Monitoring Resource Usage (System Monitor / SQL Server)
• PerfMon Sample: Performance Counters

What the ETL System Should Do

When you run a job, package, or script, you should log starting and ending times. In addition to logging these timestamps, include other details such as machine name, logged in user, process name, package versioning information, number of rows processed, number of errors, and any other useful bits of information that can be used to discover areas in which you can improve your ETL system. All of this data should be stored in your database as process metadata (more about process metadata here).

Combining this process metadata with other metrics on the server (CPU usage, disk space, memory allocation, etc.) should give you enough information to identify problem areas. The Kimball Group suggests that all this data be stored as process metadata. While I agree in principle, getting all this “extra” data into the database seems like overkill. I prefer to write to logs when I need to, and then examine the logs. This might be what the Kimball Group is actually suggesting, but it isn’t clear to me. Ideally, this logging can be switched on and off so that it does not interfere with production execution.

So what can you do with all this process metadata? You can use it to improve performance! From the Data Warehouse LifeCycle Toolkit, the following list (a bit paraphrased) will give you some things to look for:

• Poorly indexed queries
• SQL syntax causing wrong optimizer choice
• Insufficient RAM
• Sorting
• Slow transforms
• Excessive I/O
• Unnecessary writes followed by reads
• Bad aggregate management
• CDC applied too late
• Oportunities to run processes in parallel
• Unnecessary transaction logging
• Network traffic and file transfer issues

Planning for Performance

Now that you can see there are benefits to storing process metadata as part of an overall workflow monitoring capability in the data warehouse, you should also see that doing so is an ongoing effort. Workflow monitoring is not something you do once or twice, and then get busy on something else. I do a lot of planning along side my developing and I am very careful to allot adequate resources to this subsystem. Many managers don’t. If you are managing a group, or being managed by someone, and workflow monitoring is not on the plan, then do the necessary to get it there!

If you would like to know more about how I estimate time for this task, send me a message and I’ll be happy to discuss it with you.

SSIS and VFP

The tool you use makes little difference in this subsystem. All you need is access to the various variables (e.g. machine name, package name) and an ability to write them to the database. ETL Subsystem 6: Auditing is tied very closely to this subsystem. For details on how to implement an auditing subsystem (and hence have the ability to monitor your ETL) check out that post.

From Here

Remember that your job is not finished when your packages run without error. As well, it can be argued that poor performance should be treated like any other bug. Every effort should be made to improve the overall workflow and processing in order to squeeze every last second out of the system. You cannot do this unless you set aside ongoing resources for the life of the data warehouse system.

In my next post, I’ll talk a little about sorting, ETL Subsystem 28. We’re almost at the end of the line.

‘Tis the season for gift giving, receiving and the occasional re-gift. Because so many of LucidEra’s customers and partners rely on Salesforce.com for their CRM and platform as a service needs, I thought I’d post the nice Christmas card I received from them here. We hope you stop wrestling with too many enterprise business intelligence tools and harness the power of Cloud Computing in 2009.

Happy Holidays from all of us at LucidEra!

So here we are at the end of 2008. Another amazing year in the area of BI. In the 18 years I have been specialising in this IT sector, I suppose you might wonder what else is there to do here. After all BI is a mature market, Indeed many of my clients today could be classified as very mature users of business intelligence (BI). Some are on their second, third or even fourth generation of BI system implementations, with data warehouses and data marts, web enabled ad hoc query, reporting, and OLAP tools already deployed and well established across their user base.
Yet there is plenty more that can be done. A key question is how can companies with mature BI set-ups strengthen and evolve their existing investment? I still see lots of opportunity as we head into 2009.

There are several areas emerging to enhance and build on existing BI investment that can offer more value to a business. These include:
• Integration of BI with Information Management infrastructure for trusted data
• Integration of BI with Performance Management software to roll up metrics into higher level KPIs
• Capturing of additional insight from unstructured content (e.g. customer emails) and from external information on the internet (e.g. about market intelligence and about what people are saying about your products and services)
• Event driven and on-demand Operational BI - a hugely exciting area for 2009 to continuously monitor operations and deliver right -time BI in the context of process activities for continuous business optimisation
• Integration of Enterprise Search with BI to open up broader access to intelligence via a search interface
• Exploitation of appliances for lower total cost of ownership on specific workloads
• Integration of BI with social software and collaboration workspaces to facilitate sharing and exploitation of knowledge in a collaborative environment. This is particularly relevant for those of you wishing to exploit products IBM Lotus Quickr quickplaces as well as Microsoft SharePoint workspaces. Integrating BI here will become increasingly important in 2009.

These are just some of the ideas I will be discussing in the coming year and in a tough economic climate BI has never been more important. I wish all of you the best for the holiday season.

We decorated our Christmas tree last weekend — and I realized that the decorations were snapshots of our family history — basically our personal data warehouse. Certainly our tree will never make it into Good Housekeeping or win a prize for best decorations — you could say it was “eclectic” to be kind — but it is filled with many, many lovely memories…

The next SQLBits will be taking place in Manchester (in the UK) on March 28th 2009, and if you’re interested in submitting a session you can now do so here:
http://www.sqlbits.com/information/SessionSubmission.aspx

Remember, we’re always looking for new blood so don’t be nervous and have a go at speaking! We’re also looking for sponsors too:
http://www.sqlbits.com/information/Sponsorship.aspx
It’s the ideal venue to promote your SQL Server-related product or service to several hundred serious SQL Server users.

Hope to see you there!

Paul Greenberg has posted his list of 2009 CRM Companies to Watch For on his ZDNET blog. I’m happy to announce that LucidEra is listed at #5. His article provides an overview of each vendor. He concludes his LucidEra summary with this:

“What makes their model appealing is that they know their market, they focus on it, and they provide services of genuine value tailored to the market - which means basic services that are meaningful - not a lot of customization. But its what the SMB companies need. LucidEra provides it and in a time of transformation, getting what you need to either participate in that transformation or weather it, is comforting. Watch these guys.“

We are honored to be a part of such a distinguished list. The 11 vendors included are:

1. InsideView
2. Helpstream
3. Connectbeam
4. Zuora
5. LucidEra
6. Infusionsoft
7. Really Simple Systems
8. Jigsaw
9. Neighborhood America
10. Silverpop
11. Aplicor