Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    OLAP or not (SQL 2K /Access 2K3)

    I have one of those horrible last minute requests that I have go to have ready by 10.00am in the morning (approximately 12 hours time) <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

    The problem is I am looking for the approach for this as it has quite a lot of data. I have the following fields:

    ID, Product Code, Product Description, Sales date, PostCodeofCustomer, Sales Area

    I have written up the SQL script and found that I have just over 1.5 million to work with. By design I have ensured that all my product codes (1600 of them) are prefixed with 2 alpha codes.

    As usual the report's requirement specification from the business is very vague and it looks like a perfect candidate for a cross tab/ pivot table as it is sales counts per product per area per year per month ,however, I am in a conundrum. With this level of data, excel is out of the question ( or is it?) , I hate the idea of SQL back ends to Access ( as I am working from home and that is a lot of data to stream) Should I OLAP this and connect to Excel. I am keen on the latter as it is transmitting aggregate data but this is a one off report to the Directors and may be a little OTT, anyway your thoughts
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OLAP or not (SQL 2K /Access 2K3)

    You wouldn't want to run a crosstab query in Access based on that many SQL Server records. You'd need to run a pass-through query that runs on the server, but SQL Server 2000 doesn't have crosstab queries (SQL Server 2005 does have them). There are ways to simulate a crosstab query: see Cross tab queries with SQL Server 2000.

    You can create a pivot table in Excel based on external data, without importing the raw data into Excel, but I suspect it'll be very slow with 1,500,000 records.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: OLAP or not (SQL 2K /Access 2K3)

    Thanks Hans

    I agree with not doing the Cross tab in Access and Excel now you say it.

    I was just thinking about reducing the recordsets by making three tables for 2005, 2006 and 2007 to have bite size chunks. I'll try the external data route to Excel just to see how fast/slow it is. Hopefully when I am in the office it will be a little faster, I will try a simpel OLAP though and hopefully will improve as it aggregates on the server. Thank you
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •