Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm very new to Microsoft Sql Server Business Intelligence and Analysis Service(but I'm programming for years with SQL Server).Can any one describe Measures and Dimensions in Cubes in Simple words(If it's possible with images)?

thanks

share|improve this question

migrated from stackoverflow.com Jul 3 '13 at 14:00

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 10 down vote accepted

Think about a process or event you might want to analyze. Let's say you were building Lougle Analytics, and want to analyze visits† to your site.

A fact table represents a process or event you want to analyze, in this case it is a list of site visits. What things might be useful to you in analyzing site visits?

  • Information about the web browser (brand, screen resolution, ...)
  • Information about the user (country, state, city, ISP, based on their IP address)
  • Information about when the visit started (year, quarter, month, week, day)
  • The duration of the visit
  • The referring page, landing page, and exit page (title, url path)
  • The number of pages visited during the visit

Technically, you could put this all into one table - as you would in Excel, but that'd get real big real fast, so we'll do ONE level of normalization.

So your fact table will look like this:

web_browser_key          bigint
ip_address_key           bigint
start_date_key           int
referring_page_key       bigint
landing_page_key         bigint
exit_page_key            bigint
duration_seconds         int
number_of_pages_visited  int

It has a bunch of key values that refer to values in other tables††, and two non-keys. The non-keys are numeric values and are called Measures. You can't really normalize out those columns, right? It's something you could add up or take an average of, right?

The other tables are called Dimension tables, and the ip_address dimension table might look like this:

ip_address_key  bigserial primary key, /* use meaningless surrogate keys */
ip_address      inet unique,
country         text,
region          text,
city            text,
latitude        numeric(8,6),
longitude       numeric(9,6)

Notice that it is not normalized: country could be derived from city. However in a data warehouse, we care about facilitating analysis first. Notice that some of the data in the dimension table is hierarchical: country > region > city.

A logical Cube is just the collection of Dimensions and Measures that you are working with. It can have more than 3 Dimensions. Just think of a Dimension as a column in your data set.

OLAP is a set of operations you can do against a data set, such a pivoting, slicing, dicing, drilling. Think of Excel PivotTables. An OLAP Server facilitates OLAP operations.

† as opposed to single-page requests
†† usually without foreign keys

share|improve this answer

In the image below which is an example of a basic Star Schema. The Dimensions are the Dim_Tables.

These are generally the values that you want to analyse the data by. So you want to look at a particular product sales, in a particular country, over a particular date range.

In the fact_sales table you have just the one Measure which is Units_Sold.

Measures (at the simplest level) are just things you that want to aggregate when analyzing based on criteria in the dimensions.

Basic Star Schema

If there is anything else more specific that you would like to know please let me know.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.