Real-Time Business Intelligence with SQL Server 2005 Analysis Services: Webcast (10-11-2006)

Good afternoon all my SQL Real-Time Business Intelligence mind readers!

Sorry for the delay in getting these posted I have been travelling a lot!

Thanks for attending my web cast on Real-Time Business Intelligence with SQL Server 2005 Analysis Services. A huge THANKS to John Weston who hung out with us and did Q/A for everyone there were a lot of great questions.

Before I get to the questions I want o post some additional resources for you:

Stream of this web cast here:

Real-Time Business Intelligence with SQL Server 2005 Analysis Services

Web cast resource page (streams of the demos):


Virtual labs on SQL 2005:

Without any more delay here are the questions.  Please comment or email if you need more information.

Question: You are using the adventureworksdw(datawarehouse), adventureworks(OLTP) and adventure works dw(analysis services) - are you using all three in the SSIS package?
Answer: Yes, I used portions of those in all three in his demos in the webcast.

Question: Can you recommend a GOOD book to learn from?
Answer: Here is the best place to start. At the bottom there is an additional resources section that can lead to many good MS Press books on SQL. This is another good location for a book recommend by one of the web cast attendees: Microsoft Business Intelligence

Question: Did the data go from the oltp to the datawarehouse and then to the cube?
Answer: With the data we can go from the OLTP to the SSIS to the production cube, or you can go directly to the data

Question: How are collisions in data mappings handled within the data feeds?
Answer: You have to manually map out the data mappings in the SSIS definition. I am not sure about collisions you are discussing

Question: Do you recommend separation of the Analysis Service from the SQL Database engine in production so they do not compete with resources?
Answer: Yes depending upon size of databases and number of users and transactions it may make sense to run you Analysis services on separate server than your production Database server.

Question: Can you present the data through OWC through web page in Analysis Services 2005 like we did in the 200 version?
Answer: As far as I am aware, yes 2005 still supports that usage

Question: Do these features using incremental changes and proactive caching require that the source data be on the SQL Server platform? What if my source data (OLTP) is on an Oracle platform?
Answer: You can combine heterogeneous data sources in an Analysis Services cube. The cube can be sourced from data drawn from different heterogeneous sources. The data is combined by Analysis Services and presented in a unified manner to the user. I found this on this website:

Question: Can you provide download for the demo project?
Answer: The demos I did are from the sample data in adventureworks.

Question: When should you consider using the 64 bit version?
Answer: That is not a simple question. There are many factors including: database size, number of users, transactions, database design, other services that are running on the server that impact the decision to go to 64 bit, it is not strictly database size.

Question: What is a good tool to slice and dice the data?
Answer: Excel is probably your best best for slicing and dicing.

Question: I have a lot of data that has been stored in Excel workbooks that many users use to provide statuses. I have been Question to import this information to Data Mine. Is SISS the solution?
Answer: I would look at creating a OLTP SQL database to put the data in, then mine the data from that using Analysis Services.

Question: What are the added benefits are on a 64 bit machine for Analysis Services?
Answer: Like Analysis Services 2000 (32-bit), Analysis Services 2005 (32-bit) is unable to take advantage of the memory extensions of AWE. It therefore is restricted to 3GB of total usable physical memory. This makes 64-bit versions of SQL Server all the more relevant for large-scale enterprise business intelligence solutions that use Analysis Services.

The additional memory available on 64-bit platforms gives Analysis Services the capability of supporting very large dimensions or numerous large dimensions for faster access and processing. Consider the 64-bit environment if your OLAP application requires:

  • Very large dimensions. SQL Server 2005 (64-bit) has demonstrated support for very large dimensions.
  • Large memory use for process buffers.
  • Very large cubes. Significant performance benefits for very large cubes can be achieved through the use of the larger cache, thereby reducing the need for physical disk access for base cubes or aggregate data during queries. This can benefit Analysis Services deployments that have extensive fact partitions and large numbers of aggregates.
  • Fast cube processing. Memory availability reduces the need for writing to temporary files on the disk subsystem. Additional memory also allows for more parallelization during the aggregation stage.
  • A large number of concurrent users.

There is a great whitepaper here:

Question: Is it possible to dynamically (programmatically) change polling intervals.

Answer: I do not know, this may be a good place to start:

Comments (2)

  1. Jason says:

    hello, I recently migrated my Analysis Services 2005 cubes to a 64-bit server.  However, the query response time is still slow, i.e. it is still taking a long time to drill-down on dimensions using a pivot table in Excel.  

    Is it possible that my cubes are still running under 32-bit mode?  Is there a way I can check this?

    Also, do you have any recommendations on how I can improve my query response time?  



  2. Matt Hester says:

    Good afternoon Jason,

    Sorry for the delay in getting back to you, but this is a good guidance and best practices for you

Skip to main content