Hot answers tagged

11

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 ...


10

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 ...


8

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 ...


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 ...


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 ...


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.


5

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 ...


5

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 good ...


4

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 ...


4

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 ...


4

Not my field of expertise but as I understand it the difference in the majority of so-called in-memory OLAP databases (not a term I'm fond of, it's used as marketing pitch more than as a fair comparison of technologies) is column store indexes. Column-Stores vs Row-Stores (How Different Are They Really) is a good intro to the technology if you're familiar ...


4

Column store indexes will make an appearance in SQL Server 2012 (aka 'Denali'). Here is a link to a Power Point presentation by Conor Cunningham, Principal Software Architect in the SQL Server Query Processor team covering this new feature.


4

Databases designed with the assumption that they will be entirely resident in main memory can use structures such as T-tree indexes. But the real advantage is, IMDBs are just simpler. They do less (as they don't have to worry about managing a cache, or serializing writes for consistency, or anything to do with ACID-compliant I/O at all) so they execute fewer ...


4

Oracle OLAP option works really well. It is truly a hidden jewel in the database, which not many people realize should be part of every Oracle-database-based BI and DW solution. This is not a new server. It has been around since early 1970s, so it is even older than Oracle database. Oracle bought it in 1995 and "embedded" it inside the database starting ...


3

I was a bit underwhelmed with Oracle OLAP when I evaluated it circa 2005, mainly as it had poor support from front-end tools at the time (Discoverer 'Drake' had no drill-through support, and there was practically no support from third party tools). In the end that project went with MS Analysis services. @Ali's post suggests that it does have support from ...


3

I have deployed Logi Reports at a client and the free version is just fine for the users where I am. You can pay for the upgraded version which offers OLAP-like abilities. 2016 Update Logi no longer issues a free version. The paid version now has dashboard and analytic tools. This product was mentioned in another post iccube as a free OLAP but I have not ...


3

To answer your questions in order: The cube doesn't store medians, modes (or even averages), but you can write queries that calculate them and embed them as calculated measures in the cube. The ability to embed this sort of computation is one of the main unique selling points of OLAP technology. If you have a dimension that can identify individual rows ...


3

You need to start off with a cross join of detail and payment, because you say "products in any transactions that includes a certain type of payment are considered to be paid with that payment type", so you are effectively asking for parts of a transaction to be counted multiple times if there are multiple payment types used.


3

The short answer is no. Generally an OLAP database will precalculate aggregations while an OLTP system will have to do those calculations when it is asked. The language for interacting with cubes is appears similar to SQL but MDX is far different beast.


3

Cubes are very a different beast from a traditional database. There are several different kinds of cube storage processes depending on need (OLAP, MOLAP, ROLAP, etc.) which are all done differently depending on how real time the data needs to be. I actually did a webcast with another Microsoft MVP a couple of weeks ago where we talked a little about the ...


3

That's right. The cube browser control within SSDT is completely different to the one that used to exist in BIDS2008 or lower. This had me going at first too. The best way i found to get around this was to launch into excel, which can be done from the cube browser tab. I believe it even creates a temporary connection file for you too so you don't have to ...


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

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

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 ...


2

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 + + + +------------+ This kind of depends on your ETL loading procedures and reporting requirements. You essentially have 2 options Create separate measures To ...


2

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 in another cell. =cubemember("Connection","[DimBranch].[Name].[All].[" & B2 & "])"


2

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. In my case, this query was defined badly and was selecting the entire set of fact data, i.e. all the data that was already in the cube. Although fully ...


2

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 ...


2

There are a few potential advantages of using an intermediary staging database, which may or may not apply to your situation. There is no perfect, one-size fits all solution. Some of the potential advantages include: If it is appropriate, you can take a snapshot of your production database (you may have a daily backup or hot-site snapshot already) and ...


2

First your query doesn't look right, as you aren't accounting for NULL end dates. Fix that first, so that you have a flattened view of both tables. Once, you have the query working properly, you will use it as your DSV. In the DSV editor add that query as a 'named query'. That named query will be the source for your cube. Once the cube is created off of ...



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