Tag Info

Hot answers tagged

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


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.


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

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

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


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

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

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

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

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

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

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


2

The IBM Redbooks have a couple of good titles on modeling a data warehouse. Data Modeling Techniques for Data Warehousing Dimensional Modeling: In a Business Intelligence Environment You might be better served with an OLTP database in 5NF, though. It's not hard to test simplified versions of both.


2

Thanks to @JackPDouglas for the guidance, I think I may have an answer to my own question. It's a hack but I think it will work: The user is interested only in some types of payments and some types of products. I will attach subquery only for those payment types in the detail view: select case when exists ( select top 1 1 from ...


2

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.


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


2

I would normalize the fact table into this form: StudentId Level Subject 34567 Major Math 34567 Minor 1 French 34567 Minor 2 Biology etc. and add the level as a dimension. Kind regards Thomas


2

The way to do this is what Kimball called a Type-2 or Type-6 slowly changing dimension.. Essentially, a type-2 SCD has a synthetic dimension key, and a unique key consisting of the natural key of the underlying entity (in this case the flyer) and an 'effective from' date. The synthetic key is joined against the fact table, so you can attach it with a ...


2

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


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


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

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


2

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


1

This is how I would approach it myself. Remember to think of your Facts as "actions" or "verbs" and your dimensions as the the descriptors of your facts. So your invoice and invoice line items are both facts. One approach to dealing with this is what you mentioned, making the fact table to the granularity of the invoice level line item. This will lead to ...


1

With some offline conversations, Zane was able to determine that if they could keep a connection open, it would hasten the validation. To that end, I suggested they change the property on the Connection Manager to flip the RetainSameConnection property to True from its default.


1

Yes, it is possible. The easiest way would be to go with Management Studio to the proper SSAS server, right click on the database -> Script Database as -> Create to -> New query window. That will generate a .XMLA file that creates the complete definition of your SSAS database. That's without the prepared data inside. When you need your database and its ...


1

I recommend Vertica. You can get the free community edition that allows up to 1TB of data. If you normalize your web logs when you load them in, chances are they'd compress down and may fit under 1TB, as Vertica has a pretty powerful data compression engine itself. If not, I'd still recommend trying out the platform, but the license fee isn't the cheapest ...


1

@MK01... Your scenario is absolutely perfect use-case to create Oracle OLAP cubes to handle adhoc reporting. All Oracle BI/DW systems should have Oracle OLAP cubes as part of design (or as part of DW aggregation strategy). My previous post had some useful links, please go through those links. You can post questions at Oracle OLAP Forum also @ ...



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