30
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 are given more or less immediately, or at least as soon as they are available. Batch processing would collect requests into, well, batches, and execute them on ...
17
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 quadrant" that I use to illustrate the difference (in your case, consider BI/DW and ETL the same as OLAP):
Basically, if you must touch a lot of data to produce ...
12
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 PostgreSQL - it has windowing functions and CTEs (common table expressions). It also supports CHECK CONSTRAINTs and a full range of set operators.
MySQL is ...
11
You could do this with an OLAP system - some of the benefits of SSAS for this type of application include:
SSAS can readily scale out - especially as this is a read-only application with no requirements for cube writeback.
Aggregations can be tuned to minimise the I/O allowing the cubes to be tuned for efficiency.
OLAP client software and third party ...
answered Jun 14 '13 at 9:10
ConcernedOfTunbridgeWells
16.6k2 gold badges53 silver badges70 bronze badges
11
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 MDX.
The main advantage of using a multidimensional model and using MDX to query it is that you are querying pre-aggregated data and that MDX is optimized to ...
10
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 that.
Building indexes does little more than calculating bitmap indexes for your attribute relationships. So depending on how many of those you have designed ...
8
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 different dimensions
A cube uses classical aggregation functions min, max, count, sum,
avg, but can also use specific aggregation functions.
MDX versus SQL :
MDX ...
7
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 anonymous data in roughly the same scale as your real example.
However, I went ahead and created a similar type of script myself. For the sake of simplicity, I ...
7
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 it can be a "smart" key where the integer is in the form yyyymmdd; e.g., the key for August 2, 2014 would be 20140802.
Date dimensions provide a set of ...
7
SSAS is a very meaty topic. Almost none of what you know about the database engine can be applied to Analysis Services. If the only goal would be to provide a back-end for this report, then getting up to speed on Analysis Services and implementing the OLAP database would be a pretty substantial overhead compared to a more conventional approach of ...
6
It is mostly referred as ETL process (extraction, transformation, and load).
Here are a link of MSDN article on Transforming OLTP Data to OLAP Data Warehouses. It is an old article but the same concept applied.
6
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 optimised for OLTP loads. Doing analytical queries is usually slow because it involves reading lots of rows.
That does not mean that you could not do OLAP on ...
5
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 dimensions, and not the cube, the measures will never be updated.
You could Process Update the dimensions which wouldn't unprocess the cube but would drop the indexes ...
5
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 of the tables where it exists, to have a collation of Latin1_General_BIN2. Since the value is derived from an algorithm, the casing of any alpha characters ...
5
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 implies they are independent/orthogonal.
So if you have 4 of these triplet dimensions, that's the same as 12 independent dimensions.
But in any case, the answer is ...
4
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 automatic MOLAP cache creation and
management for OLAP objects. The cubes immediately incorporate changes
that are made to the data in the database, based upon ...
4
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 Kimball when looking for datawarehouse design tips.
This is what they say in Snowflakes, Outriggers, and Bridges:
We generally encourage you to handle many-...
3
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 for day-to-day processing and storage of business information. Given this storage model, you need joins to.. well, join those relational tables together when you ...
3
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 look into different storage options for your cubes.
By default cubes use MOLAP (Multidimensional OLAP) in which you process a cube on a scheduled basis[1] and ...
3
Microsoft SQL Server Analysis Services uses the term "cube processing".
Kimball seems to usually use the term "dimensional model loading".
Accordingly, I use the term "ETL" to refer to copying data from the OLTP systems into the staging database (or for copying data from one OLTP database to another) and the term "cube processing" to refer to copying data ...
3
Yes this is a very reasonable solution. I've got clients who have SSAS with similar load and it works fine. Like any database design the performance you get will be directly related to how good the cube design is.
3
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 dimension to a measure group/cube/fact table the dimension relations are automatically generated based on the relations in the data source view, if a relation ...
3
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 fact data you open the cube and can similarly set the storage mode by selecting a measure group and altering the storage mode to one of HOLAP/ROLAP/MOLAP in its ...
3
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 tables, typically at a point in time. In your diagram,
RESPONSES is your fact table.
OPTIONS could be a fact table. If you denormalize it you could have ...
3
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 measures and avoid the StDev MDX function. This approach is described here. Let me know if that doesn't make sense. The MDX would be:
(([Measures].[Sum of ...
3
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 values. [Sum of Values]
A measure which sums the square of the raw values. [Sum of Square]
A count measure [Count of Values]
The Calculated measure syntax is:
...
3
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(
DESCENDANTS([Date].[Date Hierarchy].CurrentMember,1),1)
).item(0)'
Where:
[Date] = Dimension Name
[Date Hierarchy] = Your custom date hierarchy dimension
...
3
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 column-organised tables are appropriate for you; however, data organisation must reflect the nature of your workload (i.e. queries). For example, SELECT * FROM......
3
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 power of the possible ROLLUP values is the number of possible variations which is then 4096.
DISCLAIMER
I am no mathematician. If you want a thorough explanation, ...
2
Regarding why you would want to do this, imagine you want to see which words/short phrases in customer emails are associated with costly repairs, and you want to be able to analyze this using OLAP. It can be costly to tokenize/grammify many documents, so you might want to store the tokens/grams in a form which your OLAP server understands, ie columns.
...
Only top voted, non community-wiki answers of a minimum length are eligible
