Knowledge Base

Can I create a local cube from a server cube that…

►Has Calculated Members?

Yes. Calculated members can be included in the local cubes that you create.

FOR SQL Server 2005, you can always select which calculated members you want to include.

FOR SQL SERVER 2000, when you use the Local Cube Syntax you can select which calculated members are included. When you use the Global Cube Syntax, all of the calculated members are automatically included.

If there is a grammatical error in the calculated member it will be automatically excluded when the local cube is created, without an error being displayed. See here for more information.

►Has a Parent-Child Dimension?

Yes. Parent-Child dimensions can be included in local cubes, but there can be some issues with them:

1. If you are using SQL Server 2000 and have unary operators in parent-child dimensions, see the next question.

2. SQL SERVER 2000 - It’s better to use Global Cube Syntax when creating parent-child dimensions. They will appear in exactly the same way as they appear in server cubes, unless they use unary operators.

3. SQL SERVER 2000 - When using Local Cube Syntax, the parent-child dimension is converted into a regular dimension that looks similar to the parent-child dimension in the server cube.

►Has a Parent-Child Dimension that Uses Unary Operators?

SQL SERVER 2000 ONLY - Unary operators do not work in parent-child dimensions in local cubes. All lower levels are automatically rolled into higher levels, no matter what the value of the unary operator. There are two ways to fix this problem:

1. Convert the dimension in the source cube from a parent-child dimension into a regular dimension. This is usually possible, but often not the preferred solution. If you use this solution, you must use the Global Cube Syntax to create the local cube.

2. Replace the measures in the local cube with calculated members that properly display the unary operator values.

►Uses Custom Member or Custom Rollup Formulas?

SQL SERVER 2005 - Yes. Custom members and Custom Rollup Formulas are supported.

SQL SERVER 2000 - Custom member formulas and custom rollup formulas are not supported when using Local Cube Syntax, but they are supported when using Global Cube Syntax. It may be possible to replace the formulas with calculated members which can be used with the Local Cube Syntax.

►Has a Distinct Count Measure?

Distinct Count measures are not supported by Microsoft in SQL Server 2000 local cubes and only partially in SQL Server 2005 local cubes.. However, CubeSlice automatically converts a Distinct Count measure into an equivalent calculated member, so that it can be displayed. AS2005 details. AS2000 details.

Does not apply for SQL Server 2005.

You can create local cube files from virtual cubes. Unfortunately, though, there can be many problems. See here for details.

►Uses the HideMemberIf Property?

SQL Server 2000 - The Level property HideMemberIf is used to create ragged dimensions. It is not supported at all when using the Global Cube Syntax. When using Local Cube Syntax it is supported for the following options:

Parent’s name
Only child with parent’s name

It is not supported for these options:

No name
Only child with no name

►Has a Default Member?

Fully supported in SQL 2005.

SQL Server 2000 - Default members are not supported when using Local Cube Syntax, but are fully supported when using Global Cube Syntax.

►What features in Analysis Server cubes are not included in local cubes?

SQL Server 2005 supports nearly all the features of server cubes. The main exception is that drillthrough is not supported.

The following SQL Server 2000 Analysis Server cube features do not cause the creation of local cubes to fail, but they are not included in the local cube:

• Drillthrough
• Linked cubes
• Write-enabled dimensions
• Actions
• Calculated cells
• Any formula using the LookupCube function

►When I create local cubes members that don’t have any values are disappearing. I need those members to be displayed. How can I make them appear?

One of the characteristics of local cube technology in SQL Server 2000, as developed by Microsoft, is that any members that have no keys in the fact table are omitted from the local cube. I suspect this is done primarily to save space. This behavior is fine in most situations, but some users want all the members displayed and missing members can change the results of time-series functions.

There is a way to work around this problem in certain situations. The solution requires adding a record to the fact table for each missing member. This record can have a zero value for all measures, so that it doesn't change any values. See here for more details.

►Some of my calculated members are not appearing in the local cube. Why is that happening?

Calculated members usually work fine in local cubes. However, if there is some error in the calculation, the calculated member will not be included, and there will be no error message explaining why that happened. That makes it difficult to determine what the problem is. Here are some suggestions that we use in debugging:

1. Verify that the calculated member appears without error when browsing the server cube. If it displays an error in the server cube, it may not appear at all in the local cube.

2. Check to see if you have included all the objects needed for the calculated member in the local cube - measures, other calculated members, dimensions, levels, etc.

3. Members are automatically excluded in local cubes if they are not referenced in the fact table. Some calculated members (especially time period functions) depend on particular members being present. They may be there in the source cube, but excluded in the local cube. When you are verifying that all the objects are needed for the calculation to be valid, verify this by browsing the local cube itself. Make sure all the required objects are there.

4. Some calculations may have a specific reference to the cube name. If they do and a different cube name is used for the local cube, the calculated member will not appear.

5. There is at least one function (LookupCube) that is not supported in local cubes.

►How large can local cube files be?

You may have problems when creating very large local cubes. The cubes may take a long time to create and the cubes may be slow to browse. We have used the Local Cube Task to successfully create local cube files of around 200 MB, but we usually try to limit the size of our local cubes to 50 MB. Some OLAP client applications may be able to browse larger local cubes than others.

►Can I put more than one cube in a single local cube file?

Microsoft allows more than one local cube to be put into a single .cub file, but CubeSlice does not support this option. Every cube created by CubeSlice is placed in a separate .cub file.

►What can cause the creation of a local cube file to fail?

The explanation for failed local cube creation is stored in the File Creation Log. Some of the most common reasons are:

1. SQL Server 2000 Only - Problems with a virtual cube. See here for more information.
2. SQL Server 2000 Only - The source cube has a disabled level.
3. The source cube is too large.

►Why is the creation of some local cube files skipped?

The reason for the skip is recorded in the File Creation Log. The most common reasons are:

1. SQL Server 2000 Only - The selected combination of members you are using for slicing has no records in the fact table.
2. SQL Server 2000 Only - You are creating local cubes from several source cubes and you have selections on the Cube Props tab that specify skipping of large source cubes and/or virtual cubes.
3. You have selected one of the options on the Cube Props tab for not overwriting existing local cube files.

►Which versions of Excel and the Office Web Components are supported?

Excel XP, Excel 2003, and Excel 2007 are supported.

The Office Web Components are supported for Excel XP and Excel 2003. Microsoft did not include the Office Web Components in Excel 2007. However, if you are using Excel 2007, you can download and use the Excel 2003 Office Web Components from Microsoft's web site.

►Microsoft Excel appears to lock up when I try to refresh the connection. Why is this happening?

When Microsoft Excel is connected to a local cube and the connection is refreshed, Excel attempts to delete and rebuild the local cube from the source of that cube. If Excel is able to connect to the data source there is no problem. But if Excel is not able to find the source, it will ask where the data source is located. If the local cube file is selected, Excel locks up and usually needs to be terminated.

There is no complete solution to this problem. Users need to be instructed that, when using local cubes, they should never select the Refresh option.

►What are the differences between CREATE LOCAL CUBE and CREATE GLOBAL CUBE syntax in SQL Server 2000?

Microsoft provides two different types of syntax to create local cube files. CubeSlice supports both the CREATE LOCAL CUBE syntax and the CREATE GLOBAL CUBE syntax. This choice is made in the Cube Syntax option box on the General tab of the Local Cube Options form. In many cases it doesn’t matter which cube creation syntax you use. Local cubes created by the two commands will appear to be the same. But there are limitations when creating each one with CubeSlice. There are times that you will have to pick one or the other in order to create local cubes that contain specific characteristics. See here for details.

►What are the differences between the two forms of ASSL (local cube creation syntax) in SQL Server 2005?

Local cubes created from the two forms of ASSL used by CubeSlice are very similar, though local cubes created with CubeSlice Relational ASSL can be superior. In certain situations they will be smaller and will have faster browsing performance. More details.

►Can I connect to a local cube which resides on a server rather than the user’s hard disk? For example, can we connect to a sales .cub file using the Microsoft Office Web Components from an ASP page?

No. The advantage of local cubes is that you are getting the data out to each user, so that it is available when a good connection to the server is not possible. If you are browsing through the web your ASP page can be hooked up to the Analysis Server directly. You don’t need local cubes.

►When I create a cube using this product, many of my dimensions have an added member "Blank". This member does not exist in the Analysis Server Cube, or on the Excel Pivot table when "Online" to the Analysis Server Cube. Any idea why this new member is created, and how it can be suppressed?

SQL Server 2000 Only - When you create a local cube from a virtual cube and you include dimensions that exist in one but not all of the source cubes for that virtual cube, you will see members marked (Blank). These cannot be suppressed in the creation of the local cube, though you may be able to suppress them in your client applications as you browse the local cube.

Members marked (Blank) are also displayed if you have members in the source cube that do not have a name.

►Can I create local cubes from a relational data source or just from Analysis Server cubes?

In SQL Server 2005 you can always create the local cube from the Analysis Server cube or from the relational data source.

In SQL Server 2000, you can use a relational data source to create local cubes, but it takes more effort. CubeSlice provides an interface to create local cubes from Analysis Server cubes is designed to create AS2000 local cubes from Analysis Services cubes, but it is also possible to create AS2000 local cubes from relational data sources.

►When I try to create a local cube I am receiving an error stating that a dimension name is too long and that its maximum length is 24 characters. Can I include this dimension in a local cube?

SQL Server 2000 - The maximum length for a dimension name in a local cube is 24 characters. If you have dimensions in a server cube that are longer than this, it's possible to include them in the local cube, but you have to modify them.

►From a user perspective, what is the difference between sending a "CubeSlice Data file" (cud) and sending a cuv and cub file?

There really are three ways of distributing the 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.

Our preferred, or recommended, option is to use CubeSlice Share files. 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 Viewer so it can be used on machines that do not have CubeSlice Viewer installed. In our opinion, it's also the best thing to use on computers that do have CubeSlice Viewer installed. Obviously, opening the file requires running the executable.

When users are not given permissions to run new executables, then there are two other options, both of which require that the CubeSlice Viewer be installed on the user's machine. The files can be opened by opening the viewer and then choosing to open a particular cuv or cud file, or the cuv or cud files can be opened directly. Opening either of them directly automatically opens the CubeSlice Viewer.

The second option is to use a cud file. A disadvantage of the cud as compared to the CubeSlice Share file is that it is much larger because we do not use file compression in creating it.

The third option is to distribute cuv and cub files together. If you are doing this they need to be put in the same directory on the user's machine. They work just the same as the previous two methods except that you have multiple files instead of just one. The total size of the files will be approximately the same as the size of the cud file.

►How can I create local cubes for specific individuals and store each file in a separate subdirectory?

There are three ways of doing what you want to do:

It is easiest if you want to create the same cube for each individual, only differing by the slice of data that you give each one. Here's what you do in this situation:

1. The main Storage Directory for CubeSlice files is selected on the second tab of the main CubeSlice Creator form.

1. Go to the third tab of the main CubeSlice Creator form.

2. Add a new connection, picking the appropriate source database and cube.

3. Make all the desired selections on the Local Cube Options form. Please Note - On the first tab is a place to choose whether to make a local cube from the relational database or from the Analysis Server cube. The default choice is to use the relational database. However, if you are using linked dimensions or linked measure groups, you will have to use the Analysis Server choice.

4. On the Members tab add the attribute or level you want to use for slicing.

5. Choose the option to create separate cubes either for every member or for selected members. If the latter, make the selections from the list.

6. By default, all the cubes will be put in the same directory, with each member name used as a suffix on the file name. If you want each local cube to be put in a separate directory, select the checkbox Use member names for subdirectories. Then the local cube files will all have the same name, but will be put in separate subdirectories, using each of the member names.

7. Choose File and Save from the menu, and give the CUS file an appropriate name. This CUS file can be opened later to create the local cubes or it can be called from a command-line to schedule the creation of local cubes. See the section in the CubeSlice Creator help file on Automating the Creation of Local Cube Files.

Sometimes users want to create similar cubes for different users, but they want to provide greater customization than just making different slices in a particular level or attribute. Here's the easiest way to do that:

1. Design the first local cube in the Local Cube Options form. Save it.

2. On the third tab of the main CubeSlice Creator form push the button for copying a connection.

3. Double-click on the new connection. Change it's name to what you want and make whatever other changes you want. Save it.

4. Unfortunately, when creating cubes in this way, we do not provide a way to put the resulting cubes in separate directories.

If you need to use greater customization for each user or you want more flexibility on the directory where you put each cube, here's what you would do:

1. Design the first local cube. Save it.

2. Set the desired Storage Directory on the second tab. Save the CUS file.

3. Using this first CUS file, modify the design of the local cube for the next user. Change the Storage Directory as desired. Save the CUS file with a different name.

4. Create a batch file with separate command lines to create local cubes using each of the CUS files.

5. Schedule the batch file to be run when you want to re-create the local cubes.

►What software do I need to use local cubes created with Analysis Services 2005?

Yes, a lot of information is available from CubeSlice about local cube creation. See this article in our Local Cube Information Center for details.

►Can you save Views permanently with the Viewer so you can use them as reports you run all the time?

Yes, you can save views in the Viewer. Here's how it works:

1. The Cubeslice Creator is needed to create local cube files. The CubeSlice Creator also creates CubeSlice View (CUV) files which give the CubeSlice Viewer users a starting place for cube browsing.

2. When using the CubeSlice Viewer with a CUV file, all changes made by the users to the views are automatically saved, so that every time users open up the Viewer, they automatically start where they were browsing when they closed the Viewer.

3. We provide many ways to return to the previous form of a view, in case the users don't like the way they've modified it:

The Back button undoes changes one at a time.

The Restore View to When Current View Selected returns the view to the way it was when the user last selected it.

The Restore View to When File Opened returns the view to the way it was when the user opened the CUV file.

The Restore View to Base View returns the view to the way it was when the CUV file was first created.

The purpose of this functionality is to allow users to freely change the views, but still always be able to undo what they have done. It takes a little training for the users to understand how to do all this, but, we believe, it greatly helps them feel comfortable with doing OLAP browsing. They can always change things, but always get back to the way it was in the past.

4. New views can be created with the CubeSlice Viewer. New views in the CubeSlice Viewer are always created by copying existing views.

When using the CubeSlice Creator you are able to create new views based on new cubes. You can not do this in the CubeSlice Viewer. You can only base new views on cubes that are already being referenced. So, when creating CUV files in the CubeSlice Creator, you have to add all the cube connections you want your CubeSlice Viewer users to be able to use.

5. Users can use existing CubeSlice View (CUV) files with new local cube files. When doing this, they see the new data in exactly the same way they were viewing the older data.