• Teradata Aster announces SQL-H for SQL access to Hadoop

    Teradata Aster today announced SQL-H for SQL access to Hadoop.  You can read more here: …

  • See Simba Hive ODBC Connector in action

    I finally had some time to put together a demo of the Simba Hive ODBC …

  • We create 2.5 quintillion bytes of data a day?!?!?

    Wow!  Larry Bettino writes that we create 2.5 quintillion bytes of data a day in …

  • Hive grows up: New HiveServer2 API proposed

    Over the Easter weekend, Hive took a big step forward with a new API: https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Thrift+API …

  • Strata 2012 (Santa Clara) Playlist

    It's been a month since Strata 2012 and I've finally dug myself out of my …

  • The easy way to build a custom ODBC driver

    Simba is in the business of helping companies quickly and easily build ODBC drivers for …

  • Trends Shaping Next-Gen BI and Analytics

    I was reading an interesting piece from George Mathew at Alteryx entitled "5 Macro Trends …

  • Amazon’s new Big Data service – DynamoDB

    Jim Ericson at Information Management writes about DynamoDB – Amazon's new Big Data service here: …

  • Understanding the Oracle-Cloudera Big Data offering…

    The big news of the day is likely Oracle's announcement of the specifics of their …

  • Microsoft Isotope CTP ultra-slick…

    The Christmas and New Year season is finally passed. Or, at least, the turkey is …

  • Using Hive from ODBC

    Hadoop and Hive are definitely on everyone's mind these days. Whenever I see the words …

  • Making MapReduce easy to use

    Interesting article at Forbes by Dan Woods where he talks to Tasso Argyros from Teradata …

  • Microsoft announces support for ODBC on Linux

    Yes, you read the title correctly.  Microsoft is about to release a Linux ODBC driver …

  • ODBC is the gateway to the Cloud

    It's interesting that it is SQL Azure that is pushing Microsoft to invest more in …

  • My adventures at SAP TechEd and Sybase TechWave

    I was only briefly at SAP TechEd/Sybase TechWave this week. The biggest thing for me …

  • Vectorsoft ODBC Driver Performance Optimization

    Vectorsoft has been a long-time Simba customer. They have an interesting product called CONZEPT 16 …

  • 2011 Gartner Magic Quadrant for Data Warehouse Database Management Systems – published on Teradata website

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

  • ‘tis the season for predictions…

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

  • OData gaining traction

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

  • SAP HANA now available – supports MDX and SQL

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

  • ODBC 3.8 Applications and Drivers

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

  • MDX is MDX…, isn’t it?

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

  • Why MDX?

    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.

  • Build, buy, or something in-between?

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

  • Inside information on SAP BW and HANA

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

  • SAP’s High-Performance Analytic Appliance (HANA) will support both MDX and SQL query languages

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