Systems and methods for providing custom or calculated data members in queries of a business intelligence server
A business intelligence (BI) server and repository are described which support a set of customized and/or calculated data members. In accordance with an embodiment, the BI server maintains a connection to a plurality of data sources which may store a set of dimension members. The data source can be relational, file storage based, multidimensional and other types. In accordance with an embodiment, the BI server can accept queries from the user that contain calculated members as a parameter. The calculated member is defined by an expression including multiple dimension members and one or more arithmetic operators. The BI server can parse and validate the query and rewrite the query for the data source. After the query is rewritten and optimized, it is executed against the data source and a set of results is received.
1. A method for enabling calculated members within a query, said method comprising:
storing a set of dimension members in a data source, said data source having a connection to a business intelligence (BI) server;
receiving, by said BI server, a query that contains a calculated member as a parameter, said calculated member being defined by a calculated member expression including at least two of said dimension members and an arithmetic operator;
parsing and validating said query by the BI server;
rewriting the query by the BI server to retrieve the data for the calculated member using an interface of said data source, wherein the rewritten query uses a replacement expression derived from the calculated member expression; and
executing the query against the data source.
2. The method of claim 1 , wherein the calculated member further includes a label and a member value that uniquely identifies the member.
3. The method of claim 1 , wherein syntax for the calculated member comprises a dimension identifier, a member value expression, a formula and a solving order.
4. The method of claim 3 , wherein if there are multiple calculated member expressions in a same query block, then the formula of said multiple calculated member expressions are applied in the order defined by the solving order parameter.
5. The method of claim 3 , wherein upon executing the query, the BI server replaces the calculated member with the unique member value to distinguish the calculated member from other members of the dimension.
6. The method of claim 1 , wherein the calculated member is created ad-hoc and includes dimension members from a plurality of levels, items and/or attributes.
7. The method of claim 1 , wherein the query further includes a custom aggregate member defined ad-hoc as a composition of a plurality of existing dimension members in the data source.
8. The method of claim 1 , wherein the calculated member is specified using a selection clause.
9. The method of claim 1 , wherein the data source includes one or more of the following: a relational database and a multidimensional database.
10. The method of claim 1 , wherein the calculated member is associated with a solve order parameter used to determine an order of evaluation when there are multiple calculated members from different dimensions in the query.
11. A system for enabling calculated members within a query, said system comprising:
a data source storing a set of dimension members; and
a business intelligence (BI) server that receives a query containing a calculated member as a parameter, said calculated member being defined by a calculated member expression including at least two of said dimension members and an arithmetic operator;
wherein the BI server
parses and validates the query;
rewrites the query to retrieve the data for the calculated member from said data source, wherein the rewritten query uses a replacement expression derived from the calculated member expression; and
executes the query against the data source.
12. The system of claim 11 , wherein the calculated member further includes a label and a member value that uniquely identifies the member.
13. The system of claim 11 , wherein syntax for the calculated member comprises a dimension identifier, a member value expression, a formula and a solving order.
14. The system of claim 13 , wherein if there are multiple calculated member expressions in a same query block, then the formula of said multiple calculated member expressions are applied in the order defined by the solving order parameter.
15. The system of claim 13 , wherein upon executing the query, the BI server replaces the calculated member with the unique member value to distinguish the calculated member from other members of the dimension.
16. The system of claim 13 , wherein the calculated member is created ad-hoc and includes dimension members from a plurality of levels, items and/or attributes.
17. The system of claim 11 , wherein the query further includes a custom aggregate member defined ad-hoc as a composition of a plurality of existing dimension members in the data source.
18. The system of claim 11 , wherein the calculated member is specified using a selection clause.
19. The system of claim 11 , wherein the data source includes one or more of the following: a relational database and a multidimensional database.
20. A computer-readable storage medium storing a set of instructions, said instructions, when executed by one or more processors, causing the one or more processors to perform the steps comprising:
storing a set of dimension members in a data source, said data source having a connection to a business intelligence (BI) server;
receiving, by said BI server, a query that contains a calculated member as a parameter, said calculated member being defined by a calculated member expression including at least two of said dimension members and an arithmetic operator;
parsing and validating said query by the BI server;
rewriting the query by the BI server to retrieve the data for the calculated member using an interface of said data source, wherein the rewritten query uses a replacement expression derived from the calculated member expression; and
executing the query against the data source.