Friday, June 25, 2010

Building a cube without a data source

Sometimes we might have to build a cube without a relational data source. In this scenario, we can build a cube without a datasource focusing on designing the dimensions and measures and then BIDS can generate the relational schema that is needed to populate the cube.

The following are the steps to achieve this.

•Start a new analysis Services Project in BIDS.
•In the Select method to build the cube diablog box -- choose "Build the cube without using a data source".
•In the Define New Measrue dialog box -- define the measures and measure groups.
•In the Define New Dimensions dialog box -- define the dimensions and set the basic properties
•In the Define Time Periods dialog box -- Define the first calendar day, last calendar day, first day of the week and the time periods whether it is year, quarter, month, date etc.
•Specify additional calendars if needed
•In the Define Dimension Usage dialog box -- Specify how the dimensions relate to the measure groups in the cube.
•In the completing the Wizard dialog box -- Enter the cube name and click Generate Schema now tickbox. This generates the schema geneeration wizard.
•In the Schema Generation Wizard dialog box -- Specify the Data Source view name and this will create the new Data Source View.
•In the Connection Manager dialog box -- Fill in the Provider, server name, Logon to the server, Connect to database fields.
•In the Impersonation Information dialog box -- Use the Service account (is recommended)
•In the data source wizard -- Specify the data source name
•In the Subject Area Database Schema Options dialog box -- Specify the options, accept the defaults
•In the Specify Naming Conventions dialog box -- just accept the defaults.
•After the above steps are completed, the data source view is generated in BIDS
•Populate the data source with data so that this data can be used to populate the cube.
•The last step is to right click the relevant cube in the solution explorer and select Process. Accept all the defaults and click Run. This step extracts data from the data source view and populates the cube.
The cube is now ready for viewing.

Here is a link to the original blog I posted on BIDN.

No comments:

Two ways to reset the identity of a table in sql server.

I know of the following two ways to reset the identity of a table in sql server. You often tend to use dummy data during the course of y...