CData SSIS Components for SAP BusinessObjects BI はSAP BusinessObjects BI のユニバースをビューとしてモデル化します。このAPI では、ユニバースの設定が変更されない限り、デフォルトで5000レコードしか取得できません。

ほとんどのデータベースレポートのスキーマは、シンプルなテキストベースのコンフィギュレーションファイルで定義されます。

ビュー

SAP BusinessObjects BI インスタンスのユニバースは、クエリ可能な個別のビューとしてリストされます。1つのユニバースは複数の'ビジネスビュー' を持つことができます。その場合、各'ビジネスビュー' は、クエリ可能な個別のビューとしてリストされます。 ユニバースがビジネスビューを持たない場合は、すべてのユニバースのオブジェクトはカラムとしてリストされます。

例えば、EFashion という名前のユニバースがあるとしましょう。そして、それは次のオブジェクトを持っているとします：Year（ディメンション）、StoreName（ディメンション）、SalesRevenue（メジャー）、ThisYear（フィルタ）。 CData SSIS Components for SAP BusinessObjects BI は、以下のカラムを持つEFashion ビューを公開します：Year、StoreName、SalesRevenue、ThisYear（入力のみのカラム）。実行できるいくつかのクエリ：

SELECT Year, StoreName, SalesRevenue FROM EFashion WHERE ThisYear = true
SELECT Year, StoreName, SalesRevenue FROM EFashion WHERE StoreName LIKE '%boston%'
SELECT Year, StoreName, SalesRevenue FROM EFashion WHERE Year BETWEEN 2016 AND 2018 AND StoreName LIKE '%austin%'

Note: On dynamically listed views, when you run a 'SELECT *' query, only one default dimension will be selected by default. This is done to improve performance and to make the report more readable. Since a universe can have many dimensions, the report can be unreadable.

For example, in the above example, if we'd run a 'SELECT *' query, only one of the dimensions, Year or StoreName would be selected. If you want to select all dimensions, you must explicitly write them in the query.

Query with multiple flows: If you run queries that include objects that are not related to each other, even though they are in the same universe, the SAP BusinessObjects BI will generate two query flows in the background. In that case, the CData SSIS Components for SAP BusinessObjects BI will raise an error stating that the query is ambiguous.

Custom Schemas

You can create your own views (reports), using the CreateSchema, by providing a list of dimensions IDs and measures IDs, that will be the columns of your custom schema.

Note: If the dimension and measures you have provided generates multiple query flows, then the respective schemas will be created for each query flow. The naming in this case will be, MyReport_Flow0, MyReport_Flow1 etc.

Supported Operators

The CData SSIS Components for SAP BusinessObjects BI does not support client side filtering, which means that all the queries you run, must be supported by the SAP BusinessObjects BI API. Otherwise an error will be raised, stating that the query is not supported. The supported operators will depend on the universe. The CData SSIS Components for SAP BusinessObjects BI will dynamically retrieve the supported operators of each universe. Some of these can be: AND, OR, NOT, =, !=, <=, <, >=, >, IS, IS NOT, LIKE, NOT IN, IN.

Some examples:

SELECT StoreName, City, NameOfManager FROM EFashion WHERE SalesRevenue > 1000 AND (City = 'Houston' OR City = 'Boston') 
SELECT StoreName, City, NameOfManager FROM EFashion WHERE SalesRevenue <= 1000 AND City NOT IN ('Houston', 'Boston')
SELECT StoreName, City, NameOfManager FROM EFashion WHERE City LIKE ('%ston') AND NameOfManager IS NOT NULL