Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [olap]

On-Line Analytical Processing, a class of systems designed for fast calculation, reporting and analysis of aggregate figures.

On-Line Analytical Processing (OLAP) systems are database-like systems optimised for querying in aggregate. Normally OLAP systems use specialised query languages and APIs, different from relational database systems.

Several different architectures are used in OLAP systems:

  • MOLAP (Multi-dimensional OLAP) systems persist the data in a specialised data store, and calculate and persist aggregate rollups of the data that can be used for faster querying. Examples of MOLAP systems include Microsoft SSAS and Hyperion Essbase.

  • ROLAP (Relational OLAP) systems front a standard database system and issue queries to the underlying database. Examples of ROLAP systems include Business Objects and Microsoft Report Builder.

  • HOLAP (Hybrid OLAP) systems query data from an underlying database store but calculate and cache aggregates in memory for performance. Examples of HOLAP systems include Mondrian and Microsoft SSAS.

  • In-memory OLAP systems read data from sources and maintain an in-memory data structure for fast queries. Examples of in-memory OLAP systems include QlikView and Microsoft PowerPivot.

103 questions
20
votes
1 answer

What exactly is the 'online' in OLAP and OLTP?

I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available at an instance in time. But OLAP queries can…
Zeruno
  • 547
  • 1
  • 4
  • 15
20
votes
2 answers

Difference between star schema and data cube?

I am involved in a new project where I have to create a data cube from the existing relational database system. I understood that the existing system is not properly designed, and I am not sure where to start. My question are: What is difference…
Rathish Kumar B
  • 2,320
  • 5
  • 24
  • 36
13
votes
3 answers

Good example of MDX vs SQL for analytical queries

Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results. Wikipedia says: While it is possible to translate some of…
Leftium
  • 769
  • 1
  • 7
  • 13
11
votes
3 answers

Is backing a website by a SQL Server 2012 OLAP cube considered reasonable?

I have been tasked with architecting a solution for a large retail chain. They want to allow each of its 1.2 million customers to log on to a web site to see the distribution of recent purchases (current month, previous month, year-to-date) over…
Rune
  • 213
  • 1
  • 5
10
votes
1 answer

Medians, Modes, Percentiles and OLAP

I'm newbie trying to wrap my head around OLAP, and I have a few questions. Question 1: Can an OLAP cube store medians, modes, percentiles? Question 2: Can an user-written MDX query return a summary of row-level data? (ex: % transactions > $100). Or…
Tommy O'Dell
  • 295
  • 1
  • 3
  • 7
8
votes
4 answers

What advantages does in-memory OLAP have over traditional systems with significant memory?

Do in-memory OLAP engines have advantages over the traditional OLAP engines backed by enough RAM to contain the entire cube(s)? For example, if I use a MOLAP engine (SSAS) and GB / TB of RAM where the entire cube (or even star-schema) is RAM…
kermatt
  • 403
  • 2
  • 8
7
votes
2 answers

SSIS Validation slow. OLTP OLAP running on same server

I have four identical servers 2 are used in production and the other two are demo machines/emergency fail over boxes. The more powerful machine a 16 core 127 gigs of Ram hosts both our Datawarehouse and our OLTP database. The less powerful box is an…
Zane
  • 3,530
  • 3
  • 25
  • 45
7
votes
1 answer

SSAS OLAP Semi-Additive Measure over Time Dimension

Issue: Help, I'm relatively new to building OLAP cubes in Microsoft's SQL Server Analysis Services and I need help with a semi-additive measure over a time dimension. In other words, I want to show the ending balance as a non-additive measure at…
Christopher Aliotta
  • 201
  • 1
  • 7
6
votes
2 answers

OLAP or OLATP - How to determine

I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP. There is no analysis server installed, the DB is inside the SQL Server instance hence it should be…
Atapi
  • 69
  • 1
  • 2
6
votes
2 answers

Is Data Warehousing possible in MySQL and PostgresSQL

I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I have no luck of running these kind of queries, so…
Pinser
  • 163
  • 1
  • 1
  • 5
6
votes
1 answer

How to report on sparse areas of sparse fact table

A source system tracks student attendance for a school district by reporting absence events. Attendance on any particular day can be determined by examining three datasets: school calendar, student enrollment, and absence. On any given school day,…
Gary Clarke
  • 161
  • 3
6
votes
1 answer

How do I display subtotals in cube browser in SSAS 2012 (Sql Server Data Tools)?

Apparently this feature was available in previous versions but has now been removed. What are some alternatives?
Sarah
  • 61
  • 2
6
votes
1 answer

How do you index alphanumeric foreign keys on large fact tables?

I have a field that is an alphanumeric field that ideally is a encrypted field for a non-unique identifier. It's used to relate other rather large fact tables in a many-to-many relationship. I do not have the related dimension for this field because…
Fastidious
  • 496
  • 1
  • 7
  • 15
6
votes
1 answer

Clustered columnstore index performance SQL Server 2014

I'm setting up an OLAP database using SQL Server 2014. The core fact table has about 40,000,000 rows, 225 columns with an average row size of 181 bytes. I've been toying around with the clustered columnstore index a bit without much luck. In general…
WV_Mapper
  • 161
  • 3
5
votes
2 answers

Are indexes unnecessary for columnar database?

In a columnar database, are indexes unnecessary or useless?
Yoga
  • 549
  • 3
  • 8
  • 15
1
2 3 4 5 6 7