Read-Only Setting Saves the Day
I enjoyed Michael Otey's article "Bridging the Gap" (March 2003, InstantDoc ID 37639). Our ERP and HR systems sit on AS/400s, and we've used Client Access and scheduled DTS packages to transfer data for more than 3 years. We download more than 30 files daily from the AS/400 systems into our SQL Server to power our intranet and make reporting easier for users. Here's another piece of advice when you're selecting the server settings on the Client Access ODBC setup: Choose the Connection type (Read/Write, Read/Call, Read-Only) wisely. My pumps get data only from the AS/400s, so my connections are set up as Read-Only. This saved me once when, as I was creating a new DTS package, I accidentally issued a truncate statement to my AS/400 connection instead of to my SQL Server connection. Thankfully, with the connection set up as Read-Only, the statement didn't execute.
—Mike Sturek
msturek@natco-us.com
Concurrent Role Limits
We're implementing our first data warehouse using SQL Server and Analysis
Services. Russ Whitney's article "Security and Parameterization" (December 2002, InstantDoc ID 27040) said Analysis Services supports 30 to 50 concurrent security roles. Is this number based on using a 32-bit system? We're buying a Unisys ES7000 64-bit system; how might a 64-bit system affect the concurrent-role factor?
—Gail M. Wade
gail.wade@raymondjames.com
Analysis Services architects mentioned the 30 to 50 roles in the context of using a 4-processor machine with 2GB of RAM as a "standard server." The limitation is related primarily to RAM because the Analysis Server loads all dimensions into RAM and uses a separate dimension cache for each security role. Obviously, cube size has everything to do with the amount of RAM your server will use. But I've heard about performance problems caused by having many roles defined, even if you aren't actively using them. I haven't verified this problem with Microsoft. Members of the Analysis Services team recommend putting your cube on one Analysis Server machine and linking it to other Analysis Services machines. Then, you can define the roles on the other (linked) machines. This delegates the role-specific caching to the intermediate machines and allows for separate administration of organizational units within a company. If this configuration scales better, you could emulate it with multiple Unisys ES7000 partitions. These are very general suggestions, and I recommend that you use Microsoft Premier Support to help you tune your specific application.
—Russ Whitney
CORRECTION
The query for "January MDX Puzzle Solution" (March 2003, InstantDoc ID 37802) included the wrong measure and used only the last three periods to determine the running average. The query in Listing A corrects these problems. Thanks to Ivica Zubcic for sending the correct answer.