Post reply

To Cube or Not to Cube

  • September 23, 2010 at 1:28 pm

    #142087

    Years ago, I had some exposure to BI using IBM WebFocus. Unfortunately, it was years ago and I don't remember much beyond a few concepts.

    I've built (at my IT director's request) a sample SSAS cube and SSRS report that duplicates an existing report that queries a relational database. The two reports produce the same results. I've included several additional dimensions that we would commonly use for reporting in the SSAS project, so the cube could support several additional reports. All this is based on an Oracle data warehouse provided to us and updated nightly by a vendor.

    The IT Director feels that if we can show a distinct advantage of doing so, we should move to analysis services and MDX queries for our reports. If not, we'll continue using SQL queries.

    He would like to stick to a single way of reporting; either MDX or SQL queries. From what I've been able to demonstrate, he doesn't see a clear advantage cubes and MDX for reporting. If it's worth the effort, then we could move to MDX for all reports. If we stick with SQL, then there won't be the learning curve for the other programmers.

    I know a little about SSAS and less about MDX language, but I'm willing to learn.

    So, a few observations and questions:

    1. We both know that querying against a cube would be beneficial to our power users; certain managers, accountants, etc. The ability to query by a variety of dimensions would give them the power to create their own ad-hoc reports.

    2. The MDX query returns results from aggregated cube much faster than summarizing data from a relational table unless the aggregation is stored in a separate relational table.

    Should this be an either/or situation? Does it make sense to move entirely to SSAS and MDX queries for all reporting? The overhead includes creating and maintaining the dimensions and cubes and processing the cubes nightly, monthly, or whenever depending on how often each of the relational tables are updated.

    In the relational DB we have views of commonly used columns in joined tables. If someone asks for a column that isn't commonly used, we can join the view to another view or table. Can something like that be done in MDX queries?

    If not, is there a lot of overhead if seldom-used measures or dimensions are included in the in the cube?

    Does it make sense for some reports to be based on cubes and others on relational tables? I don't know enough about BI reporting to answer that question.

    I know that answers are likely to begin with "It depends...". If you have some specific answers, I would appreciate seeing them. If possible, point me at some information sources that aren't all marketing-speak.

    --Wil

  • September 27, 2010 at 8:07 am

    #1227699

    my 2p...

    pro-SQL

    any power users that use SQL are unlikely to know or switch to MDX. as a rule, a fair amount of people will know SQL, but very few MDX. i forget MDX half the time as it's easier to do it in SQL

    users can use bespoke SQL for their meta-data, their hierarchies, their quirks

    there are things that you can do in SQL that either weren't in the spec of the cube or just don't lend themselves to cubes at all. for instance, i worked at a company with a country-wide food sales cube and there was a massive Product dimension, but i needed to use sql to give Marketing a basket analysis (what was sold with what)

    pro-MDX

    will be quicker

    dimensions will be standardised (good luck!)

    sometimes the cube will massively simplify things that take a chunk of sql, e.g. a currency conversion or a non-commutative measure

  • September 28, 2010 at 1:30 am

    #1228178

    Another alternative is to look at EXCEL 2010 PowerPivot.

    I think PowerPivot is great except that there is no API for PowerPivot.

    You must use SharePoint to automatically refresh the content.

    After some exeperimentation I made my own API for the client version of

    PowerPivot (in VB.net) which means that I can create and referesh Excelfiles

    including PowerPivot automatically.

    /Gosta

  • September 28, 2010 at 6:03 am

    #1228274

    We actually use both cubes and SSRS reports against our RDMS (plus some adhoc queries here and there). The cubes and dimensions give our power users the ability to slice the data in many various ways. The SSRS reports are more static in nature, allowing end users to get data fairly quickly in a standardized format.

  • September 28, 2010 at 2:34 pm

    #1228663

    Thank you all for the replies. If anyone else has anything to add, please do so.

    My inclination is to use both technologies. The power users will appreciate the cubes with dimensions, it may make sense to use cubes for some report queries. Other reports can use SQL queries. I think the IT Director will go along with this as we learn more about OLAP databases.

    Darth V, I even understood the reference to "my 2p". I spent three years in East Anglia back in the early 1970s.

    --Wil

  • September 28, 2010 at 11:04 pm

    #1228760

    Hi,

    I will strongly suggest to move ahead with SSAS cubes and MDX if you have signification data into DM/DW(~> 1000MB). Performance will be key factor in deciding OLAP layer. There are two aspects:

    1) Existing functionality like existing reports

    2) Future aspect

    Existing Functionality: Existing report will be boosted with performance. Properly optimized cube provides multiple fold performance improvement.

    Future aspect: SSAS is first step to BI. You can do different kind of trend analysis(year to year comparison, KPI etc.) very easily. It opens way for data mining and dashboard concepts.

    Hope i am able to explain my point. Let me know if you need more inputs.

    Thanks,
    Amit G
    http://www.msbiconcepts.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply