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 ...
- 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 ...
- 6,045
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 ...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 8,167
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 ...
- 760
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 ...
- 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 ...
- 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 ...
- 65.6k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 78k
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 ...
- 2,040
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 ...
- 8,849
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 ...
- 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 ...
- 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 ...
- 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 ...
- 3,072
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(
...
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 ...
- 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 ...
- 22.8k
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 ...
- 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 ...
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 ...
- 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 +
+ +
+--...
- 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 ...
- 141
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 ...
- 151
Only top scored, non community-wiki answers of a minimum length are eligible