Using Dimension Substitutions in OLAP Cubes

• US 20130238549A1
• Filed: 03/07/2012
• Published: 09/12/2013
• Est. Priority Date: 03/07/2012
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Aski, Vijay, Chen, Danny H., Lauren, Chris
• Granted Patent: US 9,223,847 B2
• Time in Patent Office: Days
• US Class Current: 707/602
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Systems, methods and computer program products that provide a framework for the creation, editing, manipulation and use of model-based, multidimensional analysis services (MAS) cubes and using substitute dimensions in such cubes are disclosed. To permit a user to obtain better and automatic access to business intelligence, a method of generating a model-based MAS cube comprises creating a data source comprising a data warehouse in the memory via the processor, creating a data source view providing a dimension, a fact and an outrigger from the created data source, and creating the MAS cube comprising at least one measure group. Using substitute dimensions comprises finding all relevant substitutions for a measure group, creating a table for the measure group in the data source view, adding a property as the primary key of the substitute dimension and generating a query containing an inner join logical link between the substitute and original dimension.

Claims

1. A computer-implemented framework comprising a programmed processor and memory, the processor declaring a complex definition of a multidimensional analysis services cube, the framework comprising a model stored in processor memory, the multidimensional analysis services cube using substitute dimensions including generating a query containing an inner join between a substitute dimension and an original dimension.

2. The computer-implemented framework of claim 1 comprising a method of using substitute dimensions in the model-based multidimensional analysis services cube, the method comprising the steps of:

(a) finding relevant substitutions for an associated measure group of a management pack file during deployment of the management pack;

(b) creating a table in a data source view for a measure group of the management pack using an analysis management object application programming interface;

(c) adding a property to the data source view comprising a primary key to a substitute dimension; and

(d) generating a query for the table in the data source view containing the inner join, the inner join logically linking an original dimension and the substitute dimension.

3. The computer-implemented framework of claim 2, wherein finding relevant substitutions and a substitute dimension comprises:

identifying an abstract dimension for a work item and a concrete dimension for an incident.

4. The computer-implemented framework of claim 2, wherein adding a property to the data source view comprises:

adding a column for a dimension primary key to the table of the data source view.

5. The computer-implemented framework of claim 2, the query generation comprising:

generating a sequential query language query joining a dimension table and a fact table.

6. The computer-implemented framework of claim 4, wherein a key points to one of a work item dimension and an incident dimension.

7. The computer-implemented framework of claim 5, wherein the dimension table comprises one of a dimension key and an incident key.

8. The computer-implemented framework of claim 2, further comprising the step of:

(e) automatically creating a foreign key relationship between a fact table and a dimension table.

9. A computer-implemented method implemented at least in part on a programmed processor and memory, the processor declaring a complex definition of a multidimensional analysis services cube, the cube comprising a model stored in processor memory, the multidimensional analysis services cube using substitute dimensions including generating a query containing an inner join between a substitute dimension and an original dimension, the method comprising the steps of:

(a) finding relevant substitutions for an associated measure group of a management pack file during deployment of the management pack;

(b) creating a table in a data source view for a measure group of the management pack using an analysis management object application programming interface;

(c) adding a property to the data source view comprising a primary key to a substitute dimension; and

(d) generating a query for the table in the data source view containing the inner join, the inner join logically linking an original dimension and the substitute dimension.

10. The computer-implemented method of claim 9, wherein finding relevant substitutions and a substitute dimension comprises:

identifying an abstract dimension for a work item and a concrete dimension for an incident.

11. A computer-implemented method of claim 9, wherein adding a property to the data source view comprises:

adding a column for a dimension primary key to the table of the data source view.

12. The computer-implemented method of claim 9, the query generation comprising:

generating a sequential query language query joining a dimension table and a fact table.

13. The computer-implemented method of claim 11, wherein a key points to one of a work item dimension and an incident dimension.

14. The computer-implemented method of claim 12, wherein the dimension table comprises one of a dimension key and an incident key.

15. The computer-implemented method of claim 9, further comprising the step of:

(e) automatically creating a foreign key relationship between a fact table and a dimension table.

16. The computer-implemented method of claim 9, further comprising the step of:

(9) querying the multidimensional analysis services cube using a substitute dimension via a business intelligence system.

17. A computer program product comprising computer usable medium encoded in a computer having control logic stored therein including a framework for causing the computer to automatically generate a complex definition of a multidimensional analysis services cube, the cube comprising a model stored in processor memory, the multidimensional analysis services cube using substitute dimensions including generating a query containing an inner join between a substitute dimension and an original dimension, the computer program product comprising:

first computer readable program code means for finding relevant substitutions for an associated measure group of a management pack file during deployment of the management pack;

second computer readable program code means for creating a table in a data source view for a measure group of the management pack using an analysis management object application programming interface;

third computer readable program code means for adding a property to the data source view comprising a primary key to a substitute dimension; and

fourth computer readable program code means for generating a query for the table in the data source view containing the inner join, the inner join logically linking an original dimension and the substitute dimension.

18. The computer program product of claim 17, further comprising:

fifth computer readable program code means for automatically creating a foreign key relationship between a fact table and a dimension table.

19. The computer program product of claim 17, wherein the first computer readable program code means further comprises means for identifying an abstract dimension for a work item and a concrete dimension for an incident.

20. The computer program product of claim 17, further comprising:

fifth computer readable program code means for automatically creating a foreign key relationship between a fact table and a dimension table.