locked
Best technology to use for custom reporting software RRS feed

  • Question

  • Hi,

    I’m looking for some pointers on which approach I should take when redesigning the database element of an existing reporting application. 

    In the existing system we have a primary database where the data is inserted/updated/deleted.  We then use transactional replication to regularly send these changes to a reporting database.  This reporting database contains a subset of the tables/columns from the primary database and has been indexed in such a way as to improve reading data.  The reporting software that uses this data performs various aggregations of the data held in this database.

    This system has served us adequately for six years or so but is not starting to creek under the weight of data it is being asked to process.  In the past we have gained performance increases by pre-calculating some of the most commonly used data and reading the stamped values.  One option under consideration is to broaden the scope of this stamping exercise; however I am sceptical that this will provide us with long term gains.  As such we are looking to revisit the way we store this data for reporting purposes.

    The amount of data being processes, while not trivial, is certainly a lot smaller than other data warehouses I have seen spoken about.  To give you an idea of the structure and size of the data being processed we have something like this:

    Customer (~150000 rows)

    CustomerID PK

    Order (~4million rows)

    OrderID PK

    CustomerID FK

    Order Lines (~120 million rows)

    OrderLineID PK

    OrderID FK

    Value

    The data in these tables is largely unchanged for most of the year with a large influx of data once a year.  The data from these tables gets filtered, aggregated and summed with the results of these calculations displayed in a custom charting application.  The primary goal
    of the new solution is to drastically improve the performance of report retrieval.

    I have had a quick browse around and I think I should be looking at using OLAP cubes in SSAS to report on this data.  Can someone please advise me if I am taking the correct approach with this?  If this is the correct approach, could you point me towards some good tutorials or
    guides for defining dimensions etc?  Also, any pointers on how this should be set up would be really helpful.

    Thanks
    Friday, March 16, 2012 4:10 PM

Answers

All replies

  • Hello,

    Refering to the section, from above:

    "The data in these tables is largely unchanged for most of the year with a large influx of data once a year. The data from these tables gets filtered, aggregated and summed with the results of these calculations displayed in a custom charting application. The primary goal of the new solution is to drastically improve the performance of report retrieval..."

    Based on this, yes, your approach is the correct approach. SSAS cubes would enable you to achive the above goal.

    I would recommend using SQL Server 2012 BI spectrum. Here is the tutorials you can start (you can also choose for other versions) for SSAS:

    Multidimensional Modeling (Adventure Works Tutorial)

    http://msdn.microsoft.com/en-US/library/ms170208(v=sql.110).aspx

    Along with SSAS, you can use SQL Server Reporting Services (http://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/SQL-Server-2012-reporting-services.aspx), for reporting solutions and SQL Server Integration Services (http://msdn.microsoft.com/en-us/library/ms141026(v=sql.110).aspx), as an ETL tool.

    Hope this helps.

    Thanks.


    Meer Al - MSFT

    • Marked as answer by jonesri Tuesday, April 3, 2012 10:45 AM
    Monday, March 19, 2012 5:18 PM
  • Hey Meer Al,

    Thanks for those links.  I have been working my way through the tutorial and all seemed to be making sense until I tried to make an average.  Am I right in thinking that SSAS will only allow me to average based on time?  I'm looking to average based on customer rather than time and it seems that SSAS doesn't easily accomodate this.

    Thanks.

    Thursday, March 22, 2012 1:03 PM
  • Hello Jonesri,

    You will be able to calculate the average (aggregate) by time, region, customer. This will depend on how you are creating your hierarchies. Please read the article for a good intro on how would they do the analysis.

    Project REAL: Analysis Services Technical Drilldown

    http://msdn.microsoft.com/en-us/library/cc966421.aspx

    Hope this helps.

    Thanks.


    Meer Al - MSFT

    Wednesday, March 28, 2012 3:51 PM