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!

I 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!

 

 

We 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:

 

 

The most important change in the document is in the following section, which describes a dramatic improvement in CubeSlice.

 

 

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.

Douglas  McDowell has an article in the October 2007 issue of SQL Server Magazine where he argues that it's usually best to move transactional data into a star schema before creating Analysis Server cubes. You can read his article (with subscription) at http://www.sqlmag.com/Authors/AuthorID/1537/1537.html

 

He lists several reasons for his argument:

 

1. OLTP-based cubes are usually poorly designed for BI.

2. OLTP-based cubes usually are full of dirty data.

3. OLTP-based cubes don't handle history.

4. OLTP-based cubes still have to be processed.

 

It's a great article. I agree with his reasons and I'd like to add another one:

 

5. You can often create a better local cube if your Analysis Server cubes are based on a star schema.

 

When creating an AS2005 local cube, you can use either an Analysis Services cube or the source relational data as the data source for the local cube. We support both types of local cube creation in CubeSlice, but we can often do a better job when using the source relational data - creating smaller, better-performing cubes more quickly.

 

If you're creating a local cube from an Analysis Server cube, it really doesn't matter what you're using as the source data for that AS2005 server cube. But if you want the advantages of using the source relational data option, it's much easier with a star schema data structure. Analysis Services 2005 is extremely flexible in designing cubes. You can include information from many relational tables in each dimension. But this flexibility makes it very difficult (or very time-consuming or impossible) for CubeSlice to create a local cube using the relational source option. Use a star schema for your server cubes and CubeSlice will have no problem creating local cubes from that relational data source. CubeSlice can handle snowflaking in the dimension tables of the star schema, but often cannot handle dimensions that are created from fields in multiple transactional database tables.

 

How important is this choice? When using the relational data source, the user has the option to remove  empty members from all the dimensions. This option often reduces the size of the local cubes by 50% to 90%, or even more. Smaller local cubes can be created more quickly and have better browsing performance. If you want the best possible local cubes, consider using a star schema as the source of your Analysis Server 2005 cubes.

 

 

 

Yesterday we released Build 125 of CubeSlice 9 - the first public build that can be used with the Community Technology Previews of SQL Server 2008 (Katmai). We have tested this build with CTP4, which was released in June 2007. Please let us know of any issues you find when using CubeSlice with the SQL Server 2008 CTP's. We want to be ready to provide full support for the creation of local cubes in SQL Server 2008 as soon as it is released.

Microsoft has recently announced the release of the Office PerformancePoint Server 2007 - "an integrated performance management application that allows business decision makers to be in control."

 

PerformancePoint Server is a next generation Business Intelligence tool, allowing users at multiple levels of an organization to be involved with planning, monitoring, and analyzing events.

 

From my perspective, one of the most interesting features of PerformancePoint Server is that it uses local cube files. Users can be given permission to do work on their assignments while they are off-line. Later, when they come back on-line, their data is synced with the Server. This off-line capability uses local cube files to store multidimensional data locally.

 

In my opinion, local cube files are an extremely under-utilized tool in the Microsoft Business Intelligence world. One reason for this lack of use is the difficulty in creating them. The most common way of creating local cube files, using the Create Global Cube command, has many shortcomings:

 

1. It does not provide enough options for limiting the objects included in the local cube.

2. It does not allow the user to remove unused members from the attributes of the dimensions. Removing unused members can reduce the size of a local cube from 50-90% and sometimes over 99%. This makes a tremendous difference in local cube creation time.

3. In some situations, the data in the local cube does not match the Analysis Server cube.

4. In some situations, the local cube creation fails.

 

These limitations are overcome in CubeSlice by using ASSL (Analysis Services Scripting Language) to create local cubes.

 

I think if local cubes were made more available, they would be used in all kinds of applications - giving users the ability to work in a disconnected environment, giving users subsets of OLAP data needed for particular purposes, giving users fast browsing cubes containing the data that is most important to them.

 

Hopefully, the PerformancePoint Server will lead the way toward a broader use of local cubes!

 

 

We have begun testing the creation of local cube files using CubeSlice 9 and SQL Server 2008 (previously known as Katmai). We are using the June 7 Community Technology Preview of SQL Server 2008. Initial results are excellent.

 

The current build of CubeSlice 9 needs a slight modification in order to create local cubes with SQL Server 2008. If you would like to test local cube creation with CubeSlice and SQL Server 2008, please let me know and I will get you the modified build of CubeSlice. You can contact me at tepATsdgcomputingDOTcom. For information about SQL Server 2008 and to sign up for the Community Technology Preiview, see Microsoft's web site:

 

 

 

There can be problems with member sorting in Analysis Services 2005 local cubes. Here is a description of the problem and how you can fix it.

 

In Analysis Services you can sort by the member name, the member key, or by a related attribute. When I use a related attribute for sorting, I normally set the AttributeHierarchyEnabled property for that attribute to False. If I'm only using the attribute for sorting there's no need to waste the processing time of building a separate attribute hierarchy for it.

 

But this common practice causes a problem. Local cubes do not support attributes with the AttributeHierarchyEnabled property set to False. Here is what happens to sorting when using the different ways of creating local cubes and what you can do about it:

 

1. CubeSlice Relational ASSL. The problem is completely fixed in our next build. Our testing shows proper sorting of members in all situations. The sorting is fixed automatically without the user being informed of any problem. (We hope to release this build by July 1st - if you need it sooner, let us know.)

 

2. CubeSlice OLAP ASSL. The problem can only be fixed by changing the properties in the Analysis Server cube. Set the AttributeHierarchyEnabled property to True for all attributes used in sorting. If you don't want the attribute to be visible in the cube, you can set the AttributeHierarchyVisible property to False. In the next build of CubeSlice, when this problem occurs, the local cube will be created, but the incorrect sorting will be reported in the Cube Creation Log in the Info field, with instructions as to how it can be fixed.

 

3. CREATE GLOBAL CUBE. The problem can only be fixed by changing the properties in the Analysis Server cube, as with CubeSlice OLAP ASSL.

 

4. Create Cube ASSL with a relational data source If you are directly editing ASSL, you can change the AttributeHierarchyEnabled element to True and the AttributeHierarchyVisible to False, and your local cube will be created with proper sorting.

 

5. Create Cube ASSL with an OLAP data source. If you reference any attribute that has the AttributeHierarchyEnabled property set to True, the creation of the local cube will fail. In our work on this probem, we have come to the conclusion that, when using an OLAP data source, the AttributeHierarchyEnabled property must be set to True in the source cube. (If any one knows a different way to handle this, please let us know.)

 

 

A group of developers from a large company asked me today how to divide up the creation of local cube files across multiple instances of CubeSlice, because they want to create as many local cubes as possible simultaneously. The members of this slicing attribute change weekly - with new members being added and existing members being dropped.

 

This is the type of situation where it's useful to use calculations to create the different sets of local cubes. Each CUS file would create a subset of all the local cubes. The formulas would be designed so that together they would create local cube files for all of the desired slicers.

 

The calculation you use to create the slices can be any valid MDX set formula. Here are some examples from the Adventure Works sample database:

 

1. Use the children function to create a set of slices for State/Province. Create the local cubes for each country in a separate CUS file.

 

[Customer].[Australia].children

 

2. You can do the same thing by referencing an attribute that is related to the attribute you are using for slicing. This formula can be used even when these attributes are not used as levels in a hierarchy. In this particular example, this formula gives the same result as the children function.

 

filter
(
[Customer].[State-Province].members,
[Customer].[State-Province].CurrentMember.Properties("Country") = "Australia"
)

 

3. You could also divide the set of members into subsets by referencing the names of the members. This example would include all the country/provinces that start with either A, B, or C.

 

filter
(
[Customer].[State-Province].members,
[Customer].[State-Province].currentmember.name > "a"
    and [Customer].[State-Province].currentmember.name < "d"
)

I have been asked to give a step-by-step description of how CubeSlice can be used to create and distribute customized local cubes for each sales region. This description is for creating local cubes from Analysis Services in SQL Server 2005. CubeSlice will do the same thing when using SQL Server 2000, but the options are a little different.  

Are the local cubes that you want to create for different users identical except that each one is for a different sales region? Are sales regions defined as a level or as an attribute in your cube?  

If so, you can use this strategy. If not, you can still create separate cubes for each user, but you will have to customize the cubes and views individually for each one.  

1. Open the CubeSlice Creator and start a new CUS file. Add a connection and a view.  

2. Go to the Local Cubes to Create tab and open the Local Cube Options form. Go to the Members tab.  

3. Add a slicer on sales regions. Choose the appropriate slicing option. The first two choices create single local cube files, while the third, fourth, and fifth choices create multiple local cubes, as follows:  

• Create separate cubes for every member of the attribute/level. This choice will create a separate local cube for every sales region.
• Create separate cubes for each included member. This choice allows you to pick the individual sales regions for which you want to create a local cube. 
• Create separate cube for each member selected by calculation. This choice would allow you to create separate cubes for the top ten sales regions or for all the regions within a particular district or for regions defined by some other formula.

4. After adding the slicer you will get a message asking if you want to use the same slicer on all other cubes that have the same dimension and attribute. Choose yes if you are creating several local cubes for different views for each user. The slicing choices you have made will be automatically applied to all the other cubes.  

5. Go to the Measure Groups tab. You may want to remove the dimension that has Sales Regions. Because each local cube will have only a single Sales Region, the dimension is not really needed in the local cube.  

6. Go to the General tab. Review the choice for Cube Syntax.  

• Use ASSL from Relational Source is usually the better choice. It supports more local cube features and it can make smaller, better performing local cube files. 
• Use ASSL form OLAP Source is the only choice you can use if you do not have access to the source relational database. It is also the only choice that supports the creation of local cubes from cubes with linked measure groups or linked dimensions. You may choose this option because you don’t want to use processing time in the relational database to create local cube files. 

7. NOTE: This option is being included in our next build. It is not in the current build. On the General tab, also consider the checkbox Remove members not used in any included measure group, except in time dimensions. Removing unused members can greatly reduce the size of a local cube that is being sliced.  

8. Make any other choices you want in the Local Cube Options form. Save and close the form.  

9. On the Views tab of the CubeSlice Creator, switch to a local connection. The local cube for the first sales region will be created. Create the view the way you want your users to initially see the cube data. Make sure you do it in a way that will be good for all sales regions and not just the sales region you are looking at now.  

10. Add additional local cubes and views if you want to do so. You can create several views that are connected to a single local cube. However, it is often better to create a separate local cube for each view. CubeSlice can load all the views at the same time – but only if there’s a separate local cube for each one. If not, the user will have to wait as they move from one view to the other for one view to be unloaded and the other view to be loaded.  

11. Go to the File Creation tab. Choose which files you want to create for distributing the local cubes. More on that choice below.  

12. Push the Create Files button. A full set of files will be created for each sales region, including all the local cube files and all the other files you have selected. The name of the sales region will be included in each file name, so that you can distinguish the files from one another.  

13. Choose File-Save to save the CubeSlice Specfication (CUS) file you have created. This CUS file will save all the choices you have made in setting up the local cubes and views.  

14. The next time you want to create this same set of local cube files you can open the CubeSlice Creator, select this CUS file, and push the Create Files button. If you want to automate the creation of the files, you can call the CubeSlice Creator from a scheduler or a batch file, specifying the appropriate CUS file, and all the files will be recreated. See the CubeSlice Help file for a description of the parameters that can be used when calling the CubeSlice Creator.  

Local Cube Distribution Options  

There are three ways of distributing the CubeSlice views and local cubes. They all look the same to the end user once the files are open. There are some differences related to opening the files. Some of our customers also use other OLAP client tools (especially Microsoft Excel) to view their local cubes.

1. Our preferred, or recommended, option is to use CubeSlice Share files. A CubeSlice Share is a self-extracting executable that contains a cuv file and one or more cub files. It also contains a copy of the CubeSlice Viewer so it can be used on machines that do not have CubeSlice Viewer installed. Obviously, opening the file requires running the executable, which can be a problem for some organizations that don't want their users to download, e-mail, or run new executables.

Organizations create and send out new CubeSlice Share files whenever they want their cube data updated.  

If users have the CubeSlice Viewer installed, they have more options when using CubeSlice Share files. If they want, they can save the changes they make in the views as they are browsing the local cube files. They can also create new views. These changes in the views can be either saved or discarded when the user receives a new CubeSlice Share file. 

2. A second option is to use CubeSlice Data (CUD) files. These are files that have the local cube data and the views included in the same file. They can only be used on a computer that has the CubeSlice Viewer installed. They are larger than CubeSlice Share files, because file compression is not used in creating them. Users can save changes in their views as they are using CubeSlice Data files, but these changes are lost when the CubeSlice Data file is replaced with a new one.  

3. A third option is to use a combination of local cube files and CubeSlice View (CUV) files. This option also requires that the CubeSlice Viewer be installed. All the files need to be placed in the same directory on the user's machine.  

When using this option, only the local cube files need to be replaced when new data is available.The CubeSlice View files can be sent out once and used repeatedly. Changes the user makes while browsing are all saved in the CUV file.

4. A fourth option is to use local cube files with a different OLAP browser, such as Microsoft Excel. Please note that Microsoft Excel 2007 has a much improved interface for viewing OLAP cubes. When using Microsoft Excel, a set of pivot tables and charts can be created for the users, all hooked up to separate local cube files. When new local cube files are sent out, they have to be placed in the location where Excel expects to find them. When the cubes have been replaced and the workbook is opened, the previous data will be displayed until the user starts browsing, when the new data will be loaded. We also have a macro that can be used when opening the workbook, so that the user sees the data from the new local cubes as soon as the spreadsheet is open (which can prevent a good deal of confusion).

I hope this is helpful for those of you getting started with CubeSlice. CubeSlice has a lot of other options which I haven't mentioned here. Please let me know if you have any questions.

Local cube files can dramatically improve cube browsing speed performance - but only if they're small enough:

 

1. For Small Cubes - browsing the Analysis Server cube and browsing a local cube both have excellent performance - but the local cube will usually be faster.

 

2. For Medium Cubes - browsing the Analysis Server cube and browsing a local cube will both usually have excellent performance, but performance can slow when using low levels of large dimensions. The Analysis Server cube will often be faster.

 

3. For Large Cubes - browsing the Analysis Server cube is still very good, as long as the cubes are properly optimized, but performance can be slow when working with large levels. Large local cubes have poor performance and will sometimes lock up client tools. But this is where the use of local cubes can be extremely beneficial - if you can find a way to create a set of small, high performance local cube files that give the user the portions of the large Analysis Server cubes that they really need.

 

What size of local cubes am I talking about? The tipping point between Small, Medium, and Large cubes varies enormously depending on server capacity, local computer capacity, the specific OLAP client tool being used, and network performance. But, as a general rule, I would say:

 

1. Small Cubes (both perform well, local cubes are faster) - Under 10-25 MB local cube file size.

2. Medium Cubes (both perform well, Analysis Server cubes are faster) - 25-50 MB local cube file size.

3. Large Cubes (local cubes perform poorly or not al all - create subsets) - Over 50-100 MB local cube file size.

 

The Solution - Use local cube files - especially for your large cubes - but keep the local cube files small by including only a small portion of the server cube. How can you do that? Here are the most effective strategies:

 

1. Remove large dimensions.

2. Remove large lower levels of large dimensions. When you do this, make sure you move the dimension key to the higher level, so the low levels aren't included because they're needed to provide the connection to the fact table.

3. Remove less important attributes from large dimensions.

4. Include only a slice of the members of a large dimension.

5. Slice a different dimension in a way that eliminates most of the members of all the large dimensions. For example, for some businesses, including sales only for the current month will eliminate 95% of the customers in a large customer dimension.

 

All of these strategies are possible as a point-and-click solution when using CubeSlice. They're also possible, of course, by directly manipulating the ASSL script used to create a local cube file.

 

This is the most important topic in the creation of local cube files - and the most important topic for this blog. I'll be discussing the details of minimizing the size and optimizing the performance of local cube files in many future posts. For now, if you want more information, please look at my whitepaper and demo that show how this can be done:

 

How to Dramatically Improve Browsing Speed Performance for SQL Server 2005 Analysis Services

 

Local Cube Performance Demo:

 

Feedback Please: What's your experience with the performance of different size local cube files?

 

 

 

 

 

 

 

 

 

 

I'm writing this blog for a variety of people. Here are the groups I have in mind:

 

1. Individuals who are using Microsoft Analysis Services and are looking for ways to improve their cube browsing speed performance. I've got some ideas about how to do that - and my main focus is on doing it by using local cube files.

 

2. Individuals who are using CubeSlice as a tool to create local cube files. I intend to keep you up-to-date on this blog with the best ways to create high performance local cube files using CubeSlice.

 

3. Individuals who are using CubeSlice as a starting point for creating local cube files, but are modifying the ASSL script created by CubeSlice to fit their own needs. I intend to write articles that focus on the technical aspects of the script needed to create local cube files.

 

4. Individuals who are creating local cubes on their own. There's not a lot of information out there on creating local cube files. I'm going to try to provide the information you need.

 

For all - Please send me your questions, suggestions, problems, ideas. I want this blog to be useful, helpful, relevant - and focussed on what you want!

 

Thanks!

 

A local cube is a file that contains multidimensional (OLAP) data, which can be browsed in the same way as an Analysis Server cube.

 

.cub

 

First appeared with Analysis Services in Microsoft SQL Server 7 and with Microsoft Excel in Office 2000. They received significant improvement with the release of Analysis Services in Microsoft SQL Server 2005.

 

As a general rule, a small local cube file will have faster browsing speed than the same Analysis Server cube, while a large local cube file will have slower browsing speed than the same Analysis Server cube.

 

Can we predict what size of local cube file will give excellent browsing performance?

 

Not exactly - but based on our experience we try to keep local cube files under 10 MB - and certainly under 25 MB.

 

If you have large Analysis Server cubes it's still good to use local cubes - in fact, it's even more important to use local cubes to get excellent perormance - but you're going to have to put only a portion of your server cube into your local cube.

 

The rule for local cubes and performance is to always try it and see how it works.

 

Microsoft Excel provides two wizards to create local cube files. One wizard is used when connected to an Excel Pivot Table and creates a local cube from an Analysis Server cube. The other wizard can be used to create a local cube from a relational database.

 

Our company, SDG Computing, Inc, first offered an automated local cube creation product, the Local Cube Task, in 2001. That product has now been converted into a product called CubeSlice.

 

Many other OLAP tool vendors have provided some support for creating local cube files. Most OLAP client tool vendors have provided support for browsing local cube files.

 

Microsoft has provided several ways to create a local cube programmatically as a part of their data access libraries:

 

CREATE LOCAL CUBE - Allows a user to create a local cube from either an Analysis Server cube or from a relational data source. First introduced with SQL Server 7. This command has reduced support and has been deprecated in SQL Server 2005.

CREATE GLOBAL CUBE - Allows a user to create a local cube from an Analysis Server cube. Introduced with SQL Server 2000.

ASSL (Analysis Services Scripting Language) CREATE - Allows a user to create a local cube from either an Analysis Server cube or a relational data source. Introduced with SQL Server 2005.

 

For more information on the different ways of programmatically creating local cubes, see: http://www.cubeslice.com/lcicdifferentwaystocreate.htm

 

 

Welcome to my BI Performance Blog! My goal is to help users maximize their OLAP browsing speed. I think one of the most effective ways of doing that is by using local cube files.

 

What's a local cube file?

 

I work as an independent consultant, author, mentor, and software designer in the Microsoft portion of the Business Intelligence world. In Microsoft BI, a local cube is a file containing OLAP cubes that can be browsed when you're not connected to an Analysis Server. For a user, browsing a local cube or browsing an Analysis Server cube makes no difference at all - it all looks the same.

 

Local cubes were created so that individuals don't have to always be connected to the network. You can take your OLAP data on your laptop. You can send a local cube to an individual who's not connected to your network.

 

And there's a special bonus that comes from using local cube files - dramatically improved cube browsing speed. It's often better for individuals to use local cubes even while they are connected to the network, because they're able to drill down, apply calculations, and sort their data so much more quickly.

 

There are many strategies for improving the browsing speed of OLAP cubes. But, in my experience, the most effective way to immediately and dramatically improve browsing speed is to use local cube files.

 

Most people using Analysis Services don't know about the advantages of using local cubes to enhance browsing performance. Why not? There are several reasons: