Blog

Kognitio has an interesting session lined up on July 6-7 at Canary Wharf in London …

Last week when Steve Jobs introduced the Apple iCloud, the pictures of the servers behind …

Simba has been conducting a survey on how pervasively enterprises use Excel for BI.   We're …

A recent blog by TK Anand at Microsoft is a very important read for those …

I was at Sapphire last week and I have to say I was totally blown …

Simba sells various kinds of data access Software Development Kits (SDKs).  You might ask why; …

Very interesting news from Google this week as Google Docs now has pivot table functionality.  …

At the SAP Sapphire conference this week, I spent some time looking at the SAP …

I am at the annual SAP Sapphire conference this week and I attended a couple …

A lot of companies are developing solutions for the problem of big data.  However, these …

I was reading on Mark Rittman's blog that the new release of OBIEE now supports …

One of our large financial customers is using a novel and surprisingly-flexible OLAP dashboard technique …

I was reading an article in Information Management by Shaku Atre about "The Value of …

Simba is a data connectivity software company.  We are thus always dealing with data access …

How Do Excel and Business Intelligence Integrate in Your Organization? Simba invites you to participate …

I was reading a recent post on Information Management by Andreas Bitterer entitled "Consumerization and …

This year's premier Oracle Users Group conference, Collaborate 11, just ended in Orlando, and I was lucky enough …

The Teradata acquisition of Aster Data has now closed.  Teradata is positioning Aster Data as …

I recently had an interesting conversation with a member of Simba's ODBC/JDBC/ADO.NET driver SDK team about …

Leo Apotheker presented his future strategy for HP and the recent acquisition of Vertica was …

Kognitio has announced a new product called Pablo which allows you to do in-database OLAP …

Information Management has put out a list of 40 companies to watch which I thought …

I was reading a blog post from Bill Baker from May last year entitled "I.T. …

A large financial institution customer was recently relating how BI reporting was not the end …

Lot's of interesting stuff from HP CEO Leo Apotheker today about cloud computing and big …

Since Simba has been key in the OLE DB for OLAP (ODBO) and MDX query …

When describing the business of Simba Technologies (http://www.simba.com), for example to employment candidates, I occasionally …

While I was reading a lot of the analysis of the Aster Data acquistion by …

I was looking back at Teradata magazine and noticed an interesting article in the Q1/2011 …

Wow.  HP acquires Vertica and less than 3 weeks later, Teradata buys Aster Data!  2011 …

HP's acquisition of analytical database management system (ADBMS) vendor Vertica made headlines when it was announced …

Often when I talk to people about using the BI reporting features of Excel, particularly …

If you follow the Gartner Magic Quadrants, Teradata has provided a copy of the 2011 …

This month's Teradata magazine has a very interesting article by Matt Zenus explaining how the …

Echoing my earlier comments on how HP's acquisition of Vertica was confusing considering HP's partnering …

The big news on Valentine's day was the match up between HP and Vertica.  Very …

Dave Menninger spoke to some of the Kognitio folks and wrote a bit more about …

Here is the official press release about Kognitio's Pablo product: http://www.kognitio.com/next-big-thing-in-data-analytics-no-more-physical-cubes. There is also a …

I was at the Gartner BI Summit in London last week and Kognitio had a …

There are a number of ways to learn about OLAP concepts.  I'm not referring to …

One of the great things about the Simba MDX Provider for Oracle OLAP is that …

Most users of Simba’s MDX Provider for Oracle OLAP are end users, i.e. BI analysts …

Last week, two of my Simba colleagues and I had the opportunity to attend an …

Several times recently, such as after delivering a TDWI local chapter meeting talk, I explained …

There seems to be a lot of action in the data warehousing space. Last year, …

On news feeds and podcasts over the last few weeks, there have been lots of …

Contrary to folk wisdom, if it walks like a duck and quacks like a duck, …

At Simba, because we are in the business of MDX Providers that are typically accessed …

Last week, I commented on the post about HP giving up on BI (http://blogs.simba.com/simba_technologies_ceo_co/2010/12/is-hp-really-giving-up-on-bi-and-analytics.html).  This …

It’s been interesting to watch the list of producers of live OData Services grow over …

So, last week SAP goes into ramp up with HANA 1.0 and now I read …

“Layers. Onions have layers. Ogres have layers. Onions have layers. You get it? We both …

SAP just announced that their High performance ANalytical Appliance (HANA) is now available.  HANA supports …

Platform support for ODBC 3.8 has been available under Windows 7 (and Windows Server 2008 …

We sometimes get asked what is the advantage of using Excel PivotTables for BI against …

Last week was a very interesting week at the PASS conference in Seattle.  Microsoft made …

At Simba we often hear the term "spreadmart".   This informal term, even defined on Wikipedia, …

“The wonderful thing about standards is that there are so many to choose from.”   You’d …

Matt Zenus and Carlos Bouloy presented on the Teradata Business Intelligence Optimizer (TBIO) last week …

In my last post, I mentioned “The wonderful thing about standards is that there are so many to choose from.” Usually this is meant by the speaker in a pejorative sense, indicating perhaps a perceived unwillingness of some vendors to work within existing standards, and instead wishing to define their own standard for any number of reasons, such as avoiding patent and IP issues, wishing to establish market leadership, locking out competitors, etc.

One good reason for introducing a new standard is efficiency. Most modern relational databases management systems are transactional databases. SQL is inherently transactional, making it ideal for OLTP databases. So, does that necessarily mean that if you have a database, you should access the data using SQL? Since you have a hammer, does everything look like a nail?

OLTP databases often need to be able to write lots of data, quickly. But businesses later need to deeply analyze all that transactional data. For that, a copy is extracted from the OLTP database, transformed into a more optimal format, and loaded into a data warehouse or an OLAP database. Yes, you can still use SQL to look inside a data warehouse, but it’s harder to use SQL to find patterns, quickly aggregate across the whole dataset, or do comparisons such as a time series. You can grab a coffee while you wait for it to finish.

In the mid-1990s, Microsoft introduced a new language for Multi-Dimensional eXpressions against an OLAP database, called MDX. It is a powerful but compact query language that knows and understand multi-dimensional databases/cubes. For example, if you want to know the sales of souvenir mittens at your retail outlet in Vancouver, BC during the month of February when the 2010 Olympics were held, you would write an MDX query that this:

SELECT
[Store].[Store Country].[Canada].[Vancouver] ON COLUMNS,
[Product].[All Products].[Clothing].[Mittens] ON ROWS
FROM [Sales]
WHERE ([Measures].[Unit Sales], [Date].[2010].[February])

However, if you wanted to write this in SQL, you’d need this cumbersome SQL query:

SELECT SUM(Sales.[Unit Sales])
FROM (Sales INNER JOIN Stores
ON Sales.StoreID = Stores.StoreID)
INNER JOIN Products
ON Sales.ProductID = Products.ProductID
WHERE Stores.StoreCity = ‘Vancouver’
AND Products.ProductName = ‘Mittens’
AND Sales.SaleDate BETWEEN ’01-02-2010′ AND ’28-02-2010′

It becomes even more verbose to write an SQL expression that compares Feb 2010 sales to that of the previous month, or the previous year. In contrast, MDX makes it easy to write very short succinct queries that are more direct to read and understand, which in turn reduces errors. And you can write more general expressions like “prior period” comparisons, that at every level of drill-down in time hierarchy (e.g. day, month, quarter, year) show or compare to the prior unit of time at that level.

I liken it to writing a program in assembler code versus in a high-level language programming language. You could do it, but why would you? This is not a perfect example, since assembler code is often faster than the high level language. However, in the SQL vs. MDX debate, it would be the MDX that is both easier to read and often faster since it expresses more clearly the goal of the query!

In recent years, many BI client vendors (SAP Business Objects, Microsoft, Cognos, and others) allow connections using MDX. This is a great move as it allows more powerful and general queries, an opportunity for better performance (especially against a cube), and greater interoperability. The latter is particularly valuable in the modern enterprise composed of acquisitions and mergers, thus leading to multi-vendor BI challenges that need to be bridged.

I am at the Teradata Partners conference this week in San Diego and they are …

It’s not the sort of question that usually comes up over hors d'œuvres, but given …

I blogged about SAP's High Performance Analytical Appliance (HANA) yesterday and someone followed up and …

According to an article in PC World: On this article,  SAP's new High Performance Analytic …

It’s a good time to be making the switch from 32-bit to 64-bit Windows because …

Wayne Eckerson at TDWI wrote a nice overview and checklist about doing analytics on large …

Oracle OpenWorld was a zoo again this year as always.  The big announcements were Exadata …

According to CIO.com, "Pivot tables are perhaps one of the most popular methods for analyzing …

When I was in university, I had a professor who told the class, “The wonderful …

Since last year, Simba has partnered with Oracle to provide connectivity to the Oracle OLAP …

This space just got more interesting with IBM's intention to acquire Netezza this morning.  Read …

Oracle announced their fiscal Q1 and according to Larry Ellison, their Exadata pipeline has grown …

I am looking forward to OpenWorld next week because I am very curious what Larry …

If you are interested in connecting your Excel Pivot Tables to Oracle database OLAP Option …

Simba does a lot of work with Teradata, having recently partnered with Teradata on the …