OLAP Project Wizard
Note If the same OLAP level appears in more than one dimension or hierarchy, or if more than one OLAP level uses the same database table and fields, entities will not be created for subsequent levels using the same table or fields.
When creating a project using the OLAP Project Wizard, you enable OLAP only. Therefore, questions generate only Multidimensional Expressions (MDX), not SQL statements. Also, some dialog box options will not be available. For example, you cannot create relationships that associate entities with fields so there is no Database tab available in the Relationship/New Relationship dialog box.
However, after the project is created, you can enable SQL using the Data Connection tab of the Project Properties dialog box. The resultant mixed model uses data from the OLAP cube or the SQL database tables and fields that underlie the OLAP objects. Enable SQL when questions:
- Cannot be answered using OLAP cubes only because the data is not in the cube but in the underlying SQL database.
For example, "What is the SKU of the product that sold the most" produces no results using only OLAP cubes because the SKU data is not in an OLAP cube. It is in the underlying SQL database.
- Are not about the aggregation of data.
Although using OLAP cubes allows the application to answer this type of question, OLAP performance may be slower than using SQL tables. In a mixed model, English Query uses the data that produces the results in the quickest manner. For example, for questions such as "What products do people buy," enabling SQL may speed up retrieval of the data. In mixed models, English Query initially tries to answer the question using MDX. If MDX is insufficient, English Query will use SQL statements.
When using mixed models, make sure that the OLAP cube is current with the underlying SQL database. Each time that a question is asked, English Query creates a new query using the appropriate database. If the OLAP cube is not current, the user may get different results with an MDX query than with an SQL query.