Once you have them all selected, click on the Insert Context button.Įnter a user friendly name for the context, and a user friendly description. To create the context manually, Ctrl-Click on the joins connected to the fact table, as well as all joins to dimension tables that contain data related to that fact. If you choose to create them automatically, you will still need to manually check to make sure that the context is correct. The most accurate method is to create them manually. They can be created manually, or automatically. There are several methods for creating contexts. So the JOIN_BY_SQL parameter will cause Crystal Reports to generate multiple select statements in a single SQL sentence with a FULL OUTER JOIN between them. This is because Crystal Reports doesn’t support the generation of separate SQL statements like Web Intelligence. If you are using universes with Crystal Reports, you will need to change the value of the JOIN_BY_SQL parameter to Yes. If a user runs a query which includes objects from two different contexts, Web Intelligence will generate two separate SQL statement, and join the results on the common dimensions after results have been retrieved. When properly implemented, contexts will prevent Fan Traps and Chasm Traps from returning incorrect results. For example, in the image above, the join between the CLIENT and REGION tables would be used for both Sales and Rentals, so it would be a member of both contexts. (Note: This rule will go away in BI 4.0.)Īs you can imagine, joins that do not connect directly to a fact table, can be in multiple contexts. Isolated joins will not be recognized by the reporting tools. Joins that are not members of a context are called isolated joins. IMPORTANT: Once you add contexts to a universe, all joins must be a member of at least one context (Shortcut joins are an exception to this rule). The joins highlighted in blue, are all members of the Sales Context.Īs you can see, the highlighted joins include not only the joins that touch the SALE_MODEL table, but also joins that extend to distant dimension tables, such as REGION, that contain data related to the fact table. In the picture below, the SALE_MODEL table is the fact. The joins that belong to a context are the joins from the fact table, leading to any and all dimension tables that might be needed for that fact. Contexts are used to resolve loops that are caused by multiple fact tables accessing common dimension tables. But if you have multiple facts, you will need contexts.Ī context is a list of joins that define a logical path through a universe. So, if your universe has only one fact table, you don’t need any contexts. In this case, the Invoice Detail table would be considered the lowest level of transactional data. There may be multiple tables that belong to one fact, such as Invoice Header and Invoice Detail. And just to be clear, for this purpose, I am defining a fact table as the lowest level of transactional data. If your universe has multiple fact (transaction) tables, you will need one context for each fact. Of course, I look back at that universe and think, “If only I knew then what I know now.” You see, once I truly understood universe contexts, and their proper use, I found them easy to implement, and easy to maintain. Here’s a frightening picture of that universe. Instead, I created aliases of nearly every dimension table, so as to avoid any loops. My first production universe was developed without contexts, even though contexts were really needed, and would have made the universe much more user friendly. I did anything, and everything, to avoid using contexts in a universe. Way back in the second millennium, when I started doing universe development, one of the things that scared me most about the process, was Contexts.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |