Create Local Cubes with Microsoft SQL Server 2000 and Microsoft Office

As the number of people who are conducting business from outside the corporate workplace increases, the need to access corporate databases using devices such as mobile phones and portable computers also increases.

The corporate world invests considerable resources in capturing volumes of data. Online Analytical Processing (OLAP) is a popular technology used to collate and interpret this data. With OLAP you can sift through large volumes of data and make them meaningful in context to your requirement. So how do we make OLAP services available to users who are not wired in to the corporate database because they are on the move? One way is to put various combinations of data in separate modules that do not require a connection to SQL Server analysis services.

The local cube is the first automated option developed to cater to the specific needs of a customer. It is portable, customizable and supports repeated creation of various combinations of data. As more and more data access begins to take place from the Internet and across networks, local cubes are the perfect way to provide consistent, updated Business Intelligence reports that suit each user’s specific needs without their ever having to connect to the analysis server. If you are a sales manager on the road or an engineer at a remote project site, this instant ability to retrieve and use data intelligently will go a long way to help you make effective, timely decisions.

Defining Cubes

In OLAP, data is modeled into cubes. A cube is a kind of multidimensional framework containing both descriptive data called dimensions and quantitative values known as measures. In a cube, you can not only filtrate and rearrange data, but also shrink or expand the details.

In a typical cube about sales data, there may be dimensions that include product, region and salespeople details, and measures that consist of sales volume, delivery status, datewise sales and so on.

Each dimension in a cube can expand further into levels or details in a hierarchical manner. For example, regional distribution could include levels such as sales achieved citywise, productwise or timewise. The more details you want, the deeper you can go into the levels.

All measures in a cube have the same set of dimensions and there may be multiple measures residing in a cube. You can reach the data stored in a measure by specifying the value of its dimension. The structure of an OLAP cube enables you to ask simple or complex questions and ensures that the answers you get are quick and accurate.

Local Cubes

Cubes with a Specialty

A local cube is a cube with a unique and special capability. (They are called offline cubes when created with Microsoft Excel.) The significance of a local cube lies in its ability to let end-users browse through data without connecting to an SQL Server 2000 analysis service. It is a single portable file that can reside on both server and non-server computers. User applications make use of cubes instead of the relational database. Because the analysis service handles query interaction, it becomes possible to keep the relational data structure very simple.

You can download local cubes form the Web, e-mail them and load them on to your laptop. Different functions of an organization can have different local cubes specifically designed and distributed for use among its employees. That would enable the production team to look at the workshop details, the sales manager to look at turnover figures and the administration to view the operational data all without the distraction of other unrelated data cluttering the screen. You can also deny access to data categorically among users to prevent its misuse.

As an example, let us consider a fast food retailer with a chain of stores existing all across the US. Daily transactions from each of the stores are transmitted to a central database, which is maintained at the company’s corporate headquarters. The data is categorized into different segments like inventory, personnel and finance, and stored in a relational data store. An automated data transfer system takes care of uploading and updating the data daily.

This relational data store is a big pool of information. Now if you are the Inventory manager in the company and want to know the reorder levels for placing orders for your products, you will want data like sales volume, minimum stock level, etc. The company will create an inventory cube containing dimensions like product, order quantities and time. Similarly, if the finance department wants to know which products are generating a low margin, it will have a profit cube designed for itself, which may contain dimensions that it shares with the inventory cube, like the product dimension. Companies do not have to redesign their relational data store every time there is a need for some piece of new information. It simply builds a local cube that can support such a requirement.

How Do They Differ from Server Cubes?

Server cubes are more expansive and have added features that are missing in a local cube.

Features like aggregations, partitions, member properties, shared dimensions, virtual dimensions, permanent write-back storage capacity and structures, and the capability to create parent-child dimensions are not part of the local cube lingo.

There are certain advantages to using a local cube. Used in conjunction with a data analyzer, you can filter, select and sort data subject to any criteria. You can study trends by identifying the parameters that reflect performance. Standardized queries can help make more sense of the available data. Local cubes support flexible deployment of data regardless of location. You can connect to an Internet-based cube made available through Hypertext Transfer Protocol (HTTP) or to an SQL Server 2000 Analysis Server on a network, to a local cube (.cub) file on the client computer. It allows retrieval and easy use of both data and metadata from within a client application. Analysis services can help manipulate the way the data will appear to the users by restricting them selectively.

Local Cubes can be very convenient when you want to provide role-based access to business data. For instance, you may have 100 agencies selling your products for you and you want each of them to have an individual cube to enable them to work on their respective data. You can achieve this by putting up your agents as members of a dimension in your server cube. Each agent will be a member of one level of one dimension. Individual cubes can then be created for each member of that level, with their member names included in the file name so that every individual agent can get his own local cube to work on.

For each role, a different default member of a dimension can be set. There can be one or more than one set of local cubes for one or more than one role. There are many possibilities. You can scale your cubes according to the need and resources available to you. You can create local cubes that are sliced on members from several dimensions.

In other words, you can get smart with local cubes in managing your team. You can collaborate, interact and process data faster, and help in the smooth conduct of your business.


Leave a comment

Your email address will not be published.