 | | |
| | |
|
Spaces home  Tim Peterson's BI Perfor...ProfileFriendsBlogMore |  Tools   |
Tim Peterson's BI Performance BlogLocal Cube and Other Strategies to Boost OLAP Cube Browsing Speed Performance
|
|||||||||||||||
|
Creating very large local cubes (1GB+)We have recently seen more organizations using very large local cube files. I used to suggest that it was best to keep local cube file size under 50MB. Now, however, I have seen local cubes used successfully that are 1 GB and larger in size. I have been re-thinking my ideas about the limitations of local cubes.
Here are some details:
1. Larger local cube files will be slower than small local cube files - but they can still have excellent performance. We have seen people very pleasantly surprised at the browsing speed of their 1GB local cubes.
2. You can create much larger local cube files using ASSL with a relational data source than you can using the Create Global Cube command or ASSL with an Analysis Server data source.
For example, using the relational ASSL on a Virtual PC with 2.4GB of RAM, I was able to create a 2GB local cube from a fact table with 56 million rows. On the same machine, local cube creation with an Analysis Server cube source was successful with 3 1/2 million fact table rows (332MB local cube), but failed with a memory error when attempting to create a local cube from 7 million rows.
I know of organizations using larger servers that were able to create 2GB local cubes using an Analysis Server cube source. The size of the local cube you can create is dependent on the resources (memory, in particular) of the computer you are using. But whatever hardware you are using, it appears that you can create larger local cubes using the relational source ASSL.
3. We are making some modifications in CubeSlice which will assist our customers in creating large local cube files. Our current build sets a time out for creating temp tables which is much lower than it should be. If you are having trouble creating large local cube files, let us know. We think we can help you be successful in doing so.
4. I am working on a white paper regarding the creation and use of large local cube files. Please let me know what you have experienced with them. Thanks! Why local cube creation with ASSL is superior to local cube creation with Create Global CubeI have written a white paper discussing seventeen reasons why the Analysis Services Scripting Language (ASSL) is better than the Create Global Cube command in creating local cubes in Analysis Services for SQL Server 2005.
ASSL can create local cubes that are smaller, faster, can be encrypted, and can include many more features.
You can read or download the paper here:
And please give me your feedback. Thanks!
Personal Data MartsI have just written a new paper called - Supplement Your Microsoft Business Intelligence Strategy with the Fast Performance and Excellent ROI of Personal Data Marts It's available at http://www.cubeslice.com/personaldatamarts.htm Here's a summary of my paper. You should read the whole thing, but if you don't have the time, here's what you're missing: 1. OLAP should be fast. OLAP is at its best OLAP browsing results are returned in less than one second, and very rarely in more than five seconds. 2. Personal data marts are an effective, but under-used strategy for delivering fast OLAP. 3. A personal data mart is a collection of local cube files customized for the needs of an individual user. 4. Personal data marts would be used more often if people knew about their benefits and had a convenient way to create local cube files. 5. Here's a list of specific situations where personal data marts can greatly improve cube browsing speed: Cubes have one or more large, flat dimensions 6. There are other strategies which may also take care of these performance issues: Add MOLAP aggregations 7. Here are the most important issues in calculating ROI for a personal data mart: If users need OLAP cubes, they need fast browsing OLAP cubes 8. Conclusion - The goal of using personal data marts is to make OLAP fast – convenient, easy, and effective. Fast cubes make for happy users. If there are happy users, there will be more users. And with fast cubes, each of those users will be able to find more insights to improve the organization.
From Nigel Pendse, The OLAP Report From Elizabeth Vitt, Microsoft SQL Server 2005 Analysis Services Performance Guide From Gabhan Berry, Build Better Cubes: Real-Life Advice on Building Analysis Services Cubes
Excluding Unused Members to Reduce the Size of the Local CubeWe have just released an updated copy of my paper describing the use of local cubes in Analysis Services 2005. This update incorporates improvements we have made in the local cube creation abilities of CubeSlice. You can get the updated copy of the paper here:
Using Local Cubes wtih Microsoft SQL Server 2005 Analysis Services
The most important change in the document is in the following section, which describes a dramatic improvement in CubeSlice.
Excluding Unused Members to Reduce the Size of the Local Cube
One of the biggest advantages of CubeSlice Relational ASSL is that it is the only local cube creation option where you can exclude unused members from the local cube.
Cubes often have dimension members that are not being used - members that are not linked to any records in the fact table. This happens to a greater extent when a local cube is created with slicing - limited to one Sales Rep, for example.
By removing unused dimension members, the size of a local cube can often be reduced by 50% to 90%. We have even seen one situation where the size of the local cube was reduced by 99.6%.
If you choose to exclude unused members, you should also consider choosing the CubeSlice option to create the local cube using temporary tables. The use of temporary tables often dramatically speeds up the process of creating local cubes - and that's especially true when excluding unused members. Hubs, Spokes, and the Personal Data MartRick Sherman has an article in the October 2007 issue of DMReview which ties in with the topic I wrote about yesterday. Sherman argues in favor of the traditional view of having a data warehouse as the hub and multiple data marts as spokes coming from that hub. He argues that this structure provides the most efficient way to provide data for each business user.
You can read his article at: http://www.dmreview.com/article_sub.cfm?articleId=1093535
Sherman says the following:
"The data marts franchised from the DW start the process of packaging data for business consumption. But why end there? Why not extend this approach to have the data marts become hubs for creating OLAP cubes or submarts for providing performance management, reporting and business analytics?"
This is the perspective that we have been trying to encourage in our Business Intelligence practice. From our perspective, the ideal structure for a Microsoft BI project is as follows:
1. Use SQL Server Integration Services to bring data into a data warehouse from a variety of source systems, which may be both inside and outside the organization.
2. Build one or more star schemas from the data in that data warehouse, so that the data is efficiently organized for multidimensional analysis.
3. Create Analysis Server cubes from the star schemas.
4. Create sets of local cube files for individual users - local cubes which become personal data marts, optimized with the exact data that each individual user wants to see.
Personal data marts can have much quicker querying time, because they only have data needed for a particular set of queries. If you create a local cube that has sales for one sales representative out of a thousand and data for the most recent month instead of the past five years, you can give a user the ability to experience almost instantaneous response as they browse their cubes. There are times when the personal data mart is inadequate and users will want to browse a larger set of data. But for day-to-day use, personal data marts provide the fastest possible querying - which is a tremendous benefit to the users.
|
||||||||||||||
|
|
Invite as friend