2022 Developer Survey is open! Take survey.

Hot answers tagged

30 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 ...
user avatar
  • 21.2k
17 votes

OLAP or OLATP - How to determine

From a DBA perspective, the key difference between OLAP and OLTP is the tuning method you apply to the queries. The read/write ratio doesn't really tell you anything useful. I have a little "magic ...
user avatar
12 votes
Accepted

Is Data Warehousing possible in MySQL and PostgresSQL

You can certainly use MySQL or PostgreSQL for this requirement using Python as your database access language. I've never used Python cubes so I can't speak to that. I would recommend that you use ...
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 ...
user avatar
  • 15.4k
10 votes
Accepted

SSAS cube process time increased after making fact and dimension small

Processing a cube largely consists of 3 steps, Getting the data building indexes calculating aggregations Step 2 and 3 are the least expensive (during processing) in my opinion so let's start with ...
user avatar
  • 15.4k
7 votes

date dimension OLAP cube design

Date dimensions are pretty standard in a data warehouse, and are highly recommended by Kimball as most facts tie to a date. Typically, the key is an integer. It can be a meaningless surrogate key, or ...
user avatar
  • 824
7 votes

Clustered columnstore index performance SQL Server 2014

If you can post the specific data and queries you are using, that is probably the only way we can help answer the question in the context of your specific case. You can use a script that generates ...
user avatar
7 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 ...
user avatar
6 votes

Is Data Warehousing possible in MySQL and PostgresSQL

This is a very broad answer, but that is because the question is very broad, too. MySQL has never been focused on OLAP, for one particular reason, its main engine, InnoDB, and MySQL cluster (NDB) are ...
user avatar
  • 14k
5 votes

Is processing required dimensions enough or to process the entire cube?

I think you are confusing things. First of all, processing the cube and processing the dimensions is sort of separate. I think you mean "processing the entire database". If you process only ...
user avatar
  • 15.4k
5 votes
Accepted

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

Assuming that your assumption is correct regarding the VARCHAR(50) field using a collation of SQL_Latin1_General_CP1_CI_AS, then you should consider altering those alphanumeric "code" fields in each ...
user avatar
5 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 ...
user avatar
  • 6,582
4 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 ...
user avatar
  • 15.4k
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 ...
user avatar
  • 15.4k
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 ...
user avatar
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 ...
user avatar
3 votes
Accepted

What's the point of SQL joins if you have OLAP?

You're basically talking about two entirely different technologies, used for two different purposes. A relational database stores information, commonly in a normalized fashion which is most suited ...
user avatar
3 votes
Accepted

Update OLAP Cube data

It's hard to give you a specific guidance on your best setup, but in any case there is no such thing as a trigger writing to an OLAP cube. I can give you some starting points though. You need to ...
user avatar
  • 15.4k
3 votes

SSAS cube fact and dimension relationship

What you are seeing is a typical example of a missing dimension relationship. Yes it has something to do with the relations in your dsv but easy to solve with dimension relationships. If you add a ...
user avatar
  • 15.4k
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 ...
user avatar
  • 1,365
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 ...
user avatar
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( ...
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 ...
user avatar
  • 21.2k
3 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 ...
user avatar
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 ...
user avatar
  • 14.9k
2 votes
Accepted

Best open source DB technology to store and query large, sparse matrices

You should take a look at SciDB - a database designed for precisely this use case. It has as as its major driver Michael Stonebraker, a man who knows a lot about databases - it would be an ...
user avatar
2 votes

Wich is the most efficient/easy way for end-user to get data from Analysis database?

You can put your cube formula in a separate cell and reference the cell where users type in the name they want. For example, if you have users put the branch name in cell B2, you can put this formula ...
user avatar
  • 824
2 votes
Accepted

Having Non-Numeric values as measures in Fact table

As you noted in the comments for your other question your fact table data seems to be something like this: +------------+ + Sick leave + + + + Comp off + + Sick leave + + + +--...
user avatar
  • 15.4k
2 votes
Accepted

Get distinct count on calculated member?

This helped me in getting it working! Just go to your cube and right click. Choose “Distinct count” from the begining. When you click ok. The new Measure Group will be created automatically. And you ...
user avatar
2 votes

Get distinct count on calculated member?

The limitation of a single distinct count per measure group is related to performance. To get both distinct count measures to show up in the same measure group, you can use the workaround suggested ...
user avatar

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