Working with Analysis Services Programmatically
Analysis Services has three programmatic interfaces that you can use from your analysis application. The first two interfaces are client-side interfaces: ADO MD and OLE DB for OLAP. Both of these programming interfaces offer functionality for data exploration, metadata exploration, write-back capabilities, and read-only analysis functions. Only the write-back capabilities affect the contents of the cube that other users in the system share. If you want to make other types of changes to Analysis Services data programmatically, you have to use the administrative programming interface Decision Support Objects (DSO). DSO lets you create and alter cubes, dimensions, and calculated members and use other functions that you can perform interactively through the Analysis Manager application.
—Russ Whitney, "Analysis Services Interfaces," November 2002, InstantDoc ID 26678
Filtering on Member Properties in SQL Server 7.0
Even if your OLAP client application doesn't support Member Properties, you can still filter based on their values by using the virtual dimensions feature of SQL Server 7.0 OLAP Services. Virtual dimensions expose Member Properties as another dimension in which the members of the dimension are the individual values of the Member Property. After you've defined a Member Property in OLAP Manager, you can use that property as the basis for a virtual dimension. For example, the Store Size in the SQFT dimension in the FoodMart sample database is a virtual dimension based on the Store Sqft Member Property in the Store Name level of the Store dimension. By using OLAP Manager, you can tell the difference between a real dimension and a virtual dimension by looking at the icon in the cube editor. Figure A shows the three virtual dimensions based on Member Properties of the Store Name member. Virtual dimension icons have a small calculator as part of the image. Virtual dimensions include all the unique values of the underlying Member Property as dimension members, and these members aggregate to an ALL member. Thus, virtual dimensions have only two hierarchical levels. In the client application, the virtual dimensions show up as just another dimension and don't significantly increase the size of the cube. Unfortunately, in the current release of OLAP Services, virtual dimensions are slow compared to real dimensions. Still, virtual dimensions are worth using because they let you filter OLAP queries on Member Properties even when the client application might not directly support that capability.
—Brian Moran and Russ Whitney, Mastering OLAP, "Member Properties," August 1999, InstantDoc ID 5666
Improving Query Performance
As we explain in the article "Introducing Poormon," February 2003, InstantDoc ID 37468, when members of our DBA team were preparing our data for graphing, we executed some preliminary queries to pull data from the System Monitor, generated CounterData and CounterDetails tables, and received some interesting results. First, we found that pulling data from the default table structures was slow. Then, we added a calculated field and index to CounterData and found that queries performed significantly faster when CounterDateTime was an indexed datetime field rather than a non-indexed char(24) field. (We appreciate the assistance the SQL Server Magazine technical editors gave us in figuring this out.) But when we modified the structure of the CounterData table with the appropriate indexes and calculated fields, System Monitor wouldn't log the data at all, although our queries performed somewhat better. It turns out that System Monitor tries to recreate the tables when it finds structural changes in them. We also tried creating an INSTEAD OF trigger to route the data entry into another table. However, when we did so, SQL Server bulk-loaded the data and ignored triggers. We thought about modifying the tables, but you can't expect assistance from Microsoft if you change the system tables, so we recommend that you don't alter them.
In the Microsoft Platform Software Development Kit (SDK) under the Performance Monitor heading (at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/perfmon/base/performance_data.asp), Microsoft describes the fields of the CounterData table as Table A shows.
However, the description of CounterDateTime is incorrect. If you investigate the System Monitor tables CounterData and CounterDetails, you'll find that the counter names are stored in CounterDetails and counter values are stored in CounterData, using one column for every counter and logged one row at a time. For example, if you logged the 12 counters for 2 minutes, CounterDetails would contain 12 records for the names of the counters, whereas CounterData would contain 24 entries for each minute the data was logged. One way to make pulling data from this format more efficient and effective is to transform the data into a pivot-table format in which one column exists for the date and time and additional columns exist for each counter whose data you want to view. Interestingly, this is the same format that a System Monitor CSV file uses.
—Mark Solomon, February 2003, InstantDoc ID 37581
Using SQL ALIAS for the AS/400
The AS/400 supports a file concept known as multiple-member files, in which one file (or table) can possess several different members. Each member is a part of the same file or table and shares the same schema, but the members are uniquely named and have unique data. ODBC and OLE DB have no built-in mechanism for accessing multiple members. By default, ODBC always accesses the first member in a multimember file. To enable ODBC-based applications such as Data Transformation Services (DTS) to access multiple-member files, you need to use the AS/400's SQL ALIAS statement. The ALIAS statement lets you create an alias for each member you need to access. Then, your ODBC application can access the alias, which in turn connects to the appropriate member. These SQL aliases are persistent, so you need to create them only once. The following statement shows how to create an alias:
CREATE ALIAS MYLIB.FILE1MBR2 FOR MYLIB.MYFILE(MBR2)
This statement creates a new alias named FILE1MBR2 for the multimember file MYFILE. The ODBC or OLE DB application then connects to that specific member, using the alias name FILE1MBR2 to access the second member in the file MYFILE.
—Michael Otey, "Handling Multiple-Member Files," March 2003, InstantDoc ID 37754