1/17/2014

My MDX Journey

I worked for a company called Z-Tel in 2001.  While there they had some Cubes.  I had to report on those cubes.  Through Crystal Reports.  Using MDX.  Which I did.  Except I did not understand exactly what I was doing.

So that set me on a quest.  To learn the language MDX.  Except I didn't put much effort into it.  As other tasks required my attention.

And a decade went by.  And I soon had interest in the language again.  Except I didn't have a real project to work on.  I dabbled in Cube creation.  On my own.  At home.  In a sandbox environment.

Recently, my Consulting job required me to query against Cubes.  Through SSRS.  I did not tell myself that I didn't know what I was doing.  I simply got started.  And slowly.  After opening SSRS.  Creating a connection to the OLAP cube.  Opening the Query Builder.  Dragging and dropping Measures and Dimension fields.  The query was soon built.  Added the fields to the report.  And soon the report was created.  And sent to the Client.

I now understand what I'm doing.  Understand the relationships between Fact tables (Verbs) and Dimension tables (Nouns).  What a Measure is (Sums, Avgs, Mins, Maxs).  What an Axis is.  How Dimension tables Intersect at Points of Axis.  What a Column values vs. Row value.  How to Select from a Cube.  Apply Filters or Where clauses.

I understand it.  I can create queries using the Drag and Drop wizard.  I can read the MDX query and see what it's doing.  I can troubleshoot the code.  I can reverse engineer a Cube into an SSAS project.  Interrogate the Dim / Fact tables, what is their source. How are the Measures created.  How were the Hierarchies created.  The Attribute relationships.  Refresh the Cube when underlying table structrures change.

Yet, I have not sat down to write raw MDX code from scratch.  I have not used some of the built in functions.  Perhaps in time.

So 13 years after being introduced to Cubes and MDX, I now have a better understanding of how they work.  I tend to follow through on things.  Sometimes, more than not, it takes some time and effort.  You have to keep the faith.  With curiosities, hopes and dreams.

No comments:

Post a Comment