CODEWITHSUNDEEP
sql-serverssasreportingolap
Aref Karimi
Aref Karimi

Reputation: 1890

Do I need a cube?

We have a content ingestion system which receives (mobile) digital contents of different types (Music, Ringtone, Video, Game, Wallpaper etc) from various providers (Sony, Universal Music, EA Games etc) and then dispatches them across several online stores (e.g. Store1, Store2 etc).

The managers want to know how many of each content type, in a given time window, has been come through from each suppliers and they have gone to which store!

To me it seems like a report that needs an OLAP cube. Am I correct? The problem is that I am a .NET developer and not much skilled in BI and SQ Server Analysis Services therefore I want to make this simple yet flexible and meaningful. Is there an easier way of having a reporting cube, and a data mart to produce reports like this? (I am not sure if we can purchase SSAS and SSIS licenses at all).

And for such data mart and cube, what structure is suggested?

Upvotes: 1

Views: 755

Answers (4)

Sean Zhao
Sean Zhao

Reputation: 1516

Create a cube might be overkilled for your scenario as your data is not quite complicate and not so big. But excel might not enough as it is hard to pivot data in your database directly.

You can try embed WebPivotTable into your website or your application. It provide all functions of excel pivot table and can be connect to CSV/Excel files or connect to database by web service interface. It is web based and the front end user interface are quite intuitive so that users can easily get what he want by simple drag and drops. Here is demo and Documents.

Of course, if you still want to create a cube, this tool can also be very helpful as it can connect to SSAS cubes directly.

Upvotes: 0

Nicolas
Nicolas

Reputation: 1

You can solve this with just standard queries or views in SQL Server. Tools such as PowerPivot for Excel also allow you to create local cubes with very little effort.

Of course, purchasing an SSAS license and moving to a cube environment has several advantages, despite the extra cost:

  • Cubes are faster and allow for more complex calculations than SQL Queries
  • With the introduction of the SSAS Tabular Model, making cubes really isn't hard anymore
  • Creating cubes often forces you to clean up your data model, which has a positive effect on your architecture overall in most cases

Upvotes: 0

Mike Honey
Mike Honey

Reputation: 15037

An SSAS cube presented in Excel Pivot Tables is probably still the most powerful and flexible end-user query tool out there, with a very low learning curve (most managers/analysts can already use Excel). Once they have a cube they can satisfy many requirements themselves, without you needing to constantly tweak queries. Even when they do want something more complex, you have a perfect source for report/query design and testing.

But designing and building an SSAS cube is very difficult and they are quite obscure to debug.

I suggest starting with Power Pivot - it's a free Excel Add-In that builds an in-memory cube, and presents the results as Excel Pivot Tables. It scales well through advanced compression and the resulting Model can be published to an SSAS Tabular server. The calculation language is DAX which is an improvement on the horrible MDX - DAX reads more like Excel functions.

This site is probably the best starting point for Power Pivot:

http://www.powerpivotpro.com/

Upvotes: 0

Nick.Mc
Nick.Mc

Reputation: 19235

From your description, a cube isn't necessary. Assuming this data is in a database you can just write a query to get that result. If you've bought a licence of SQL Server (i,e, not the free edition) then you already have SSAS, SSIS, SSRS.

Some of a cube's main advantages are:

  • It's easier for end users to do adhoc reporting
  • Performance is often better than a relational (SQL Query) source

Some disadvantages are:

  • You need to spend processing time 'building' the cube
  • The query language (MDX) can be a challenge to learn

You don't have an adhoc user analysis requirement here

Upvotes: 1

Related Questions