Skip to main content
33 votes

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

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses ...
mustaccio's user avatar
  • 26k
16 votes
Accepted

Difference between star schema and data cube?

After spending some time, reading reference books, I came to the point, where I could define the difference between the star schema and data cubes. I could not comment on this definition but this ...
Rathish Kumar B's user avatar
11 votes
Accepted

Good example of MDX vs SQL for analytical queries

MDX and SQL are in no way the same, and often not even comparable, as they are querying multidimensional and relational databases respectively. You cannot query your existing relational database with ...
Tom V's user avatar
  • 15.7k
9 votes

Difference between star schema and data cube?

Qutoting from Kimball Dimensional Modeling Techniques Star Schemas and OLAP cubes Star schemas are dimensional structures deployed in a relational database management system (RDBMS). They ...
Galal Ouda's user avatar
8 votes

Good example of MDX vs SQL for analytical queries

OLAP Cubes/databases have the following characteristics: Obtain already aggregated information according to the needs of the user. Easy and fast access Ability to manipulate the aggregate data in ...
Yassine LD's user avatar
5 votes

SSAS Realtime Processing

You need to look into proactive caching for your partitions and dimensions where you can set up notifications based on a tracking table. From the documentation: Proactive caching provides ...
Tom V's user avatar
  • 15.7k
4 votes

Dealing with different types in a fact table

I would steer clear of the 3 option. It's called a snowflake instead of a star schema. It's a rather advanced solution which can be used when necessary but has it's own drawbacks. As usual refer to ...
Tom V's user avatar
  • 15.7k
4 votes

How does a column-oriented DBMS filter on multiple columns?

There are a few factors which reduce the horror of a full scan of the shop column. The values of each column can be stored in the same order: the first value of timestamp corresponds to the first ...
Michael Green's user avatar
4 votes

CUBE and calculating the number of dimensions

It seems like the "hierarchical dimension"s example given as (year, month, week) is treated really the same as 3 independent dimensions - all 8 possible combinations are allowed - that ...
Cade Roux's user avatar
  • 6,602
3 votes
Accepted

SSAS OLAP Semi-Additive Measure over Time Dimension

Solution The easiest way to accomplish the above is to set the CustomRollupColumn in your date dimension for both your Quarter and Year dimensions to the following equation: 'TAIL(NonEmptyCrossJoin( ...
Christopher Aliotta's user avatar
3 votes

Cons of using a star schema in a columnar database

You are mixing up three orthogonal concepts: data model (star schema), workload characteristics (OLTP vs. OLAP), and physical data organisation (columnar). Your data model has no bearing on whether ...
mustaccio's user avatar
  • 26k
3 votes

SSAS How to calculate Standard Deviation

To get this to work you need: To add a column in your DMV to produce the square of the raw value. SELECT [Value], square([Value]) as ValueSquared FROM factTable A measure which sums the raw ...
Sir Swears-a-lot's user avatar
3 votes
Accepted

SSAS How to calculate Standard Deviation

The StDev function does operate at a rollup grain per the first parameter you pass in. If you always want to calculate the StDev at the lowest level then you can write some mathematically equivalent ...
GregGalloway's user avatar
  • 1,385
3 votes
Accepted

SSAS where are olap cubes stored

Dimensions in Microsoft SQL Server Analysis Services support either ROLAP or MOLAP. Open the dimension in BIDS 2008, select the root dimension node and change this in the properties window. For ...
Martin Smith's user avatar
  • 84.7k
3 votes
Accepted

Star Schema from Relational Database

Ok, based on your very limited documentation, I would do the following: Fact Tables - Your fact table is your measurement table. It is the thing that happened. It is the meeting together of dimension ...
Anthony Genovese's user avatar
3 votes

How does a column-oriented DBMS filter on multiple columns?

Columns-organized storage engines are vastly different in implementation, so a general answer to your question is hardly possible. At a very basic level though a DBMS with a modestly advanced query ...
mustaccio's user avatar
  • 26k
3 votes

CUBE and calculating the number of dimensions

Welcome to DBA.SE. The question is more of a mathematical problem, than a database one. It might be better suited on a different site. However, 8 ^ 4 = 4096. Or the CUBE (3-level aggregate) to the ...
John K. N.'s user avatar
  • 17.8k
2 votes
Accepted

OLTP to OLAP process causing deadlocks and impacting customers

Perhaps this problem has been solved meanwhile. Databases need dedicated knowledge and skills, any 10 year old kid can play around with SQL Server Management Studio, but creating and maintaining ...
Erik Hart's user avatar
  • 510
2 votes
Accepted

After processing complete, SSAS DB slowly consumes all available space on disk, w. high memory use also

I found the answer eventually .... There was a job set up to incrementally process the cube. Incremental cube processing requires a query to be defined to select new fact data to merge into the cube. ...
Laurence's user avatar
  • 315
2 votes
Accepted

OLAP window functions in DB2

You are mixing up Db2 (Db2 for Linux, Unix and Windows) and Db2 for z/OS. Db2 9.7 is LUW (Linux, Unix and Windows) and FIST_VALUE is available on these OS'es. Db2 for z/OS does not support ...
MichaelTiefenbacher's user avatar
2 votes

SQL server OLAP tables perfomance

You don't give us much to go on regarding: The types of queries during daytime. Analytical queries, that accesses lots of data and does GROUP BY and similar? How the modifications are being performed ...
Tibor Karaszi's user avatar
1 vote

SQL to MDX translation?

Mondrian is indeed a good example, in that it's a ROLAP engine (in which MDX statements are translated to SQL which are then fired to the underlying database (other than it caches results, so ends up ...
nsousa's user avatar
  • 176
1 vote

Cons of using a star schema in a columnar database

Mustaccio's answer is good. In addition, every star schema I have ever seen was implemented on top of a relational DBMS, often Oracle. Also, the data in it was downstream from some kind or ...
Walter Mitty's user avatar
  • 4,316
1 vote

Why does there have to be a tradeoff between optimizing a DB for transactional data and optimizing a DB for analytical data?

Since there is a limited number of resources (cpu, memory, disk) and you want to use them as efficiently as possible. For OLTP you typically have many small transactions and concurrency is important....
Lennart - Slava Ukraini's user avatar
1 vote

In a data warehouse, should a measure be based on a fact or a dimension?

The problem with contrived examples like the one you provide is that whatever answer you find, it won't be helpful in a real life situation. "A number of distinct customers" is useless as a measure, ...
mustaccio's user avatar
  • 26k
1 vote
Accepted

Do I still need OLAP cubes after a SQL Server Upgrade

on this link: A quick overview of SSAS for DBAs you can find relevant information about SSAS that can help you to see why there are so many advantages using SSAS. You also need to manage permissions, ...
Marcello Miorelli's user avatar
1 vote

Restore from .db folder for Analysis Services

Yes. Attaching the database files for SSAS is a documented and supported operation. But unlike SQL Server Database Engine I wouldn't count on it working if the installed version of Analysis Services ...
David Browne - Microsoft's user avatar
1 vote

Dealing with different types in a fact table

@TomV Already answered this well, essentially if you want to split each case into separate dimensions you create a dummy record with a surrogate key. Can't say if it's right for your business case but ...
Max xaM's user avatar
  • 141
1 vote
Accepted

Is OLAP cube duplication of data?

It depends. Most products duplicate the data, and require ETL to "refresh" the data, running on a regular basis but implying there is some delay between live data and OLAP data. It also implies that ...
Bruno Guardia's user avatar
1 vote

pglogical for OLAP queries

Yes, there's some performance impact on the master because of logical decoding. It's not usually too bad. You can have both streaming physical replication for HA/failover and pglogical in use at the ...
Craig Ringer's user avatar
  • 56.4k

Only top scored, non community-wiki answers of a minimum length are eligible