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


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

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

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

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

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


3

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.


3

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


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

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

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

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


1

If you want to see measures from two different fact tables side by side but different granularity, it's possible but not perfect. For example, the measure for purchase amount in FactPurchase has no monthly equivalent. To do this, it helps to have as many related dimensions between both fact tables. So if you have a dimension such as property type that is ...


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


1

In a datamart you need to denormalize time variant attributes to your fact table. Your transactional source database will have the flyer's club level on the flyer table, or possibly in a dated history table related to flyer as suggested by JNK. In your datamart, you need to apply the current club level of each particular flyer to the fact record that ...


1

This is a common design pattern for HR systems, so there's any amount of precedent for this type of approach. You will find that the format used by SSAS stores this type of data quite efficiently, so data volumes won't be a significant problem. I've seen this done on a HR cube with a row count around 300 million on fairly old hardware using AS2000. 2008R2 ...


1

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. This product was mentioned in another post iccube as a free OLAP but I have not used it. It looks to me like it tries to emulate the built in features that Oracle has on a Java ...



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