Table 7‑1 lists import methods that Metrics Management supports for typical database connection types.
Table 7‑1 Supported import methods and database connection types
Import method
Access
SQL Server
OLAP
Oracle
MDX Query
No
No
Yes
No
SQL Query
Yes
Yes
No
Yes
Stored Procedure
N/A
Yes
No
Yes
Table
Yes
Yes
No
Yes
View
Yes
Yes
No
Yes
To help you choose an import method, read each description in the following list:
MDX
To pass an MDX query to a multi-dimensional database, or OLAP server, select SQL. Then, provide the MDX query text in the SQL editor.
For MDX queries generated using a third party editor, save the generated query to a file. Then, in the Metrics Management SQL editor, choose Load to transfer the saved query into Metrics Management.
SQL
To pass a SQL query to a database server, select SQL. Choose Edit. Provide query text in the editor. Then, choose OK. Alternatively, save the SQL query to a file. In the editor, choose Load to transfer the saved query text into Metrics Management.
To send a query to a database server, use a SQL statement. Alternatively, call a stored procedure that uses parameters values. For example, to choose a particular date range or specific location, use a SQL statement similar to one of the following examples:
For SQL Server
select * from Sample
or
select MeasureName, LocationName, ComparativeName, PeriodName, UserData from Sample
For Oracle
select * from Sample
or
select MeasureName, LocationName, ComparativeName, PeriodName, UserData from Sample
Stored procedure
Passes a call from a stored procedure to your database server. The stored procedure definition must return mandatory columns.
To successfully import data using a stored procedure, the stored procedure specified in Data Import Options must create a record set. A stored procedure returns a record set as a result. Two examples of stored procedures that return a record set to Metrics Management without additional changes appear in the following code examples:
SQL Server
CREATE PROCEDURE sp_GetSampleData AS
select * from Sample
Oracle
Create a new package named TYPES with the following text as the body:
AS
TYPE TCUR IS REF CURSOR;
END;
Then create a procedure named SP_GETSAMPLEDATA with the following text as the body:
(aCur OUT TYPES.TCUR) AS
begin
OPEN aCur FOR SELECT * FROM Sample;
end;
A stored procedure that uses more sophisticated conditions must return a record set as a result.
For example, to call a stored procedure, type sp_GetSampleData in Stored Procedure in Import.
Stored Procedure with parameters
The following examples run a stored procedure that uses a parameter value to retrieve only location data for Mexico. Oracle stored procedures do not support passing parameter values.
MDX