Search code examples
sqlmdxdata-warehousestar-schema

Data Warehouse - MDX query to calculate top 5 universities

I am trying to create a star schema to analyze rankings on universities.

I created star schema which is at the below;

fact table;

Ranking

  • University_id
  • Time_id
  • Classification_id
  • ranking(measure)

the dimensions and hierarchies;

University_id

  • state
  • city
  • university_name

time_id

  • year
  • quarter
  • month

classification_id

  • category( it can be Engineering, or Social sciences etc.)
  • type ( it can be computer engineering, civil engineering etc.)
  • type_info (info about the type)

I want to write a MDX query that lists the top-5 universities in California in terms of Computer Engineering in 2011. The result should consists of university name and it's rating.

Here is my MDX query;

SELECT Measures.ranking ON COLUMNS
HEAD (ORDER ({[University_id].[California].[Members].[Children],
Measures.[ranking], BDESC, 5}) ON ROWS
WHERE (Measures.[ranking], [Time_id].[Year].[2011], [classification_id].[engineering].  [computer Engineering])

Is my design of Star schema and my relevant mdx query correct and efficient? Otherwise how can I improve my solution or make it correct? I also created the star schema therefore we can change it to improve the design.

Solution

  • The general idea is fine. Some remarks, however:

    • I would call the dimensions "University", "Time", etc. instead of "University_id" etc. The id is an internal key that is in many cases not exposed to users, and users think in terms of "University", not in terms of "id".
    • If you are using measures on the columns, you may not use them in the slicer axis (aka WHERE clause).
    • I am not sure how your ranking measure looks like. But if it really is a rank in the sense that lower numbers are better, you should order ascending instead of descending, or use BottomCount.