MDX is an Acronym for Multi Dimensional eXpression and we will be using this language in two ways:
Expression Language: where we can write our own expression in the database to work with these expression allowing us to store the logic in centralize logic in the cube to allow everyone that use the data to get same result every time they run the expression rather than everyone create their own version of expression which make sure all out put from the expression is the same.
Query Language: we can use MDX as Query language that is where we retrieve data from cube as bulk like we use normal SQL to retrieve data from database.
So now that you know a bit about cube let’s get down to business and get to know where it actually get used.
So as we discuss this earlier we can use this to query a special data structure, which is called cube and use that result with our analytics infrastructure that is in place in your organization like SQL Server Analyses Service, Oracle Essbase and SAP Netweaver BI.
Develop cube object:
There are many cube objects we can create with MDX such as calculated member, which is an expression we stored in cube that is calculated when a query executed and give us that calculated member, and we also have KPI Key performance indicated which we can use both with data and visual indicator to show us how the performance of some certain product or services works either it’s going down or up or normal and there is many other cube objects that we can create I just named some of them.
Create or modify cube component:
We can also use MDX to alter cube object with MDX like update, alter or delete component of cube with MDX like we have DDL Data Manipulation Language in normal SQL, there is many other ways were we can use MDX to play with cube data I just mentioned some of them.
Beside that there is many other ways we can use MDX with many other platform like we can directly run ad-hoc queries in our SQL server database by using SQL server management studio to connect to a cube and run your queries their.
And also we can use Microsoft excel to connect to a data set and use that to make your own MDX to run in database or by simply using drag and drop features of Microsoft excel to retrieve data from data set.
To finalise this there is many place specially in Microsoft world where we can use MDX to retrieve data, create reports, create filters or generate charts in many Microsoft product like Integrated services, Microsoft BI, SharePoint services and many more.