Oct 26 2007
Index Awareness Part I: The Basics
What is Index Awareness?
The first topic that I covered in my “Tales From a Universe Ninja” presentation at the 2007 Insight Americas conference was Index Awareness. I have to admit, this would probably not have been my first choice for a topic. I covered it on request from a member of the Universe Designer product team. As I went through this feature I found that much of what I remembered was still true even in XI R2. But I also found that in the right environment this feature could really help.
If you have downloaded a copy of my presentation then I am going to cover slides 10 through 16 in this blog post. Future blog posts will cover the rest of the material from the presentation. I believe that this feature was introduced in version 6.5 which means you must be at least at that version to be able to consider using it.
This feature is defined in the Universe Designer help file as follows:
Object keys allow Universe Designer to generate more efficient SQL by filtering on primary key values and eliminating unnecessary joins.
That’s great, but what does it mean?
Setting Up Index Awareness
When a designer builds an object there is a new tab available on the object properties screen called Keys as shown here:
This tab is where you can define the primary and foreign keys for the table in question. In this case, I am showing keys for the Country table from the Island Resorts Marketing universe. The table is referenced via an alias in order to break up a loop, and the keys are identified here:
If a table has foreign keys in more than one place that is not a problem. The designer can include as many key values as are appropriate. Here is another example from the Island Resorts universe, showing the Service table being related to two “fact” tables; one is for reservations and the other is for invoices.
Once the keys are defined, what does it do?
Index Awareness In Action
One of the main drawbacks to this feature is that it does not always work. There are certain things that have to be done in order to reap the benefits. The easiest way to start is to say that in order to get the key benefits the report user has to pick from a list of values. If the user enters a value manually, it does not work. If the user respond to a prompt, it does not work. (More on the prompt thing in a later post.)
So here is a screen shot showing a user selecting the Resort name (which has been defined as Index Aware) on a query panel:
And here is the SQL code generated by the query shown above:
SELECT
max( RESORT.resort ),
sum(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)
FROM RESORT, INVOICE_LINE, SERVICE, SERVICE_LINE
WHERE ( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND ( SERVICE.SERVICE_ID=INVOICE_LINE.SERVICE_ID )
AND RESORT.RESORT_ID = 2
GROUP BY
RESORT.RESORT_ID
The important line is this one:
AND RESORT.RESORT_ID = 2
Without the index keys the condition would have been based off of the resort name instead of the resort key. And it gets even better. 🙂
If the Resort result object is removed from the query then the query panel would look like this:
And here is what the SQL code looks like:
SELECT
sum(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)
FROM INVOICE_LINE, SERVICE, SERVICE_LINE
WHERE ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND ( SERVICE.SERVICE_ID=INVOICE_LINE.SERVICE_ID )
AND SERVICE_LINE.RESORT_ID = 2
What has changed? Two very important things. First, the RESORT table has been removed from the query altogether. Second, the condition has switched to the foreign key defined in the SERVICE_LINE table instead. The query is much more efficient.
When I first read about this feature one of the touted features was a “Fact Only” query. That is certainly possible. I created a query that uses three conditions, all of them with Index Awareness defined, and the following SQL was generated:
SELECT
sum(SHOP_FACTS.Amount_sold)
FROM
SHOP_FACTS
WHERE
(SHOP_FACTS.WEEK_KEY = 233
AND
SHOP_FACTS.ARTICLE_CODE = 177264
AND
SHOP_FACTS.SHOP_CODE = 203)
This is really efficient SQL! However, the results are not very interesting. Without the key values as results the fact table is the only table referenced in the SQL code. But without those key values as output, the resulting report is not very interesting. (Please see slide 16 for more details, if you are interested.)
Summary
In this post I defined what Index Awareness was, showed how to set it up, and showed how it can help create more efficient SQL. The next post will start to talk about some of the challenges that you might encounter when using this feature.
Technical Notes
The universes used in my presentation were based on Island Resorts Marketing and eFashion. Both databases were converted to Oracle 10g in order to use some of the more advanced features that do not work with Microsoft Access. These universes and databases are not available for download at this time but I plan to make them available at some point in the future.
Hi Dave,
Thanks for the wonderful information.
Regards,
Sudheendra
I see the value for OLTP systems, but for a properly indexed data mart I doubt it?
Andreas, I will mention that fact in a few posts. From my observations (and I will admit I have not used this feature much) a star schema is one of the less useful configurations for this feature. A snowflake or – as you suggest – a transactional model seem to work better just because there are more opportunities for indexes.
Stay tuned for more in this series. 🙂
Dave, I have come across the situation that I cannot enter keys in the Keys tab of the object definition window. After entering it just does not show my input, like nothing ever happened. It seems to be the software since all universes have that problem (BO XIr2, Designer version 11.5.0.0). I heard about problems with outer joins on tables and there are some in my main universe but as I said also other universes show this problem.
You ever come across this situation?
Any help is appreciated.
Yes, in fact I ran into that when I built my presentation. What I had to do was hand-type (or paste) the information directly into the keys screen rather than using the […] button. Try this… when you get to the keys screen, click the […] button and select the appropriate key value. But then copy that SQL code using Ctrl-C. When you get back to the keys page, it will not be set. Paste the code you just copied with Ctrl-V and see if it “takes” then.
Hi Dave, i saw your presentation in Berlin earlier this year, and was fascinated by it. Intrigued as well, especially by the index awareness bit. I try to use it in my work, but currently i’m designing on top of a database which has many tables with a multicolumn primary key. And i can’t get it to work.
Do you have any idea of what to do in this case?
Thanks,
Tijl
Did you ever create the followup Blog Post? I can’t seem to locate it in your Search…
I am trying to add a primary key to my object but all the buttons on the keys tab are disabled. In my universe I have object A based on stored proc. and object B on a table. I am trying to add a key to object B so that I can use this object to generate lov with key and value pairs.
I was able to set the key on the same object when I tried that in another universe which does not have sotre proc. based objects. Is it possible to get around this limitation?
Dave, (BO God)
Good Post about IA.
Where is 16th Post. Pls help me out.
(Please see slide 16 for more details, if you are interested.)
Thanks!
Leenu-K
Hi, “Slide 16” refers to a specific slide in the presentation that you can download, not another blog post.
Hi Dave,
Thank you so much. its very informative and helpful.
Could you please let me know if you have posted any thing related to Aggregate awareness.
Thanks,
IssKumar
Hi Dave,
I have a table where the key is composed on two columns (Composite Keys). How do i then create a PK for an Object. Universe allows only one PK. Is there any workaround for the same.
Thanks,
Vignesh
Vignesh, that’s one of the drawbacks of index awareness. If you use surrogate keys you can get around this since your keys will be single-column values. But compound keys do present a problem. The reason is the way the process works… it converts Resort_Name = “Bahamas Beach” into resort_id = 2. It’s not set up for a compound key.
Hi Dave, Can you help me?
It is possible use the index awareness when the filtered object is being used in the panel “Object result” of web intelligence?
Example: Using the example of your query would change the filter table RESORT for SERVICE_LINE
SELECT
max( RESORT.resort ),
sum(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)
FROM RESORT, INVOICE_LINE, SERVICE, SERVICE_LINE
WHERE ( RESORT.RESORT_ID=SERVICE_LINE.RESORT_ID )
AND ( SERVICE_LINE.SL_ID=SERVICE.SL_ID )
AND ( SERVICE.SERVICE_ID=INVOICE_LINE.SERVICE_ID )
AND SERVICE_LINE.RESORT_ID = 2
GROUP BY
RESORT.RESORT_ID
Allan, index awareness only activates when a user picks from a list of values.
Hi
great work!!
Could anyone help with finding the entire presentation?
Thanks
All of the presentations that I have posted for download are on my conference presentations page. If that’s not what you meant, please clarify…
Is there a subsequent part to Index Awareness ?
e.g. Index Awareness Part II – The Not So Basic ?
Hi, Nix, not at this time. There is additional information in my most recent presentation from BI 2012 that goes more into detail if you need something right away.
Hi Dave,
Where can i find the other post related to this topic?
Hi and thanks for your interest. So far there isn’t one. I intend to get around to it at some point.
Hi Dav,
Sub: Issue with index awareness prompt in Hyperlinks
Thanks for the Blog. I learned Index awareness from your blog. It worked well. Right now i have issue when I try to link the Index awareness prompt reports.
I used Index awareness Prompt in a parent report and Child report. When I call Child report using Opendocument I pass key fields in the index awareness Prompt. It is working fine.
but when user refresh on Child report, indexes(Primary keys) are appearing on selected values. Users likes to see the LOV text in the selected values in Child report.
This issue is coming only when index awareness prompt report is called from other report. Kindly please suggest if there is any solution for this issue.
The UserResponse() function won’t work because you’re passing the keys. What I have done in this case in the past is build a small table that shows only the dimension values that match the keys.
For example, let’s say you’re using Resort as the prompt. The resort_id is a number one through five, and the user wants to see the resort names in the report header instead. Build a small table with resort name only, make sure that it rolls up duplicate rows, and you have a way to display the names of the resorts that matched. Unfortunately it won’t really show what was picked, because (for example) the Australian Reef resort might have been selected but it won’t show up on most reports because there aren’t any transactions. To get around that, you could consider making a completely separate data provider that brings back only resort names and uses the same prompt. Either way, displaying the names in a properly formatted block on the top of the report will show the user what was selected.
Hi Dav,
Thanks for your quick response. My issue is not displaying the selected (passed value from parent report) value on the report header. My issue is with prompt selected values(User selection screen)
Child report is working fine when it is called from the Parent report. After child report display if user refresh child report then I am seeing index ids in the Prompt selected value. I want to keep my previous selection, so I have not used Not_persistent option in the prompt syntax. Prompt is keeping last run values in the selected values, but it is keeping index id’s when the report is called from other report. When report runs directly then I don’t have this issue of seeing index ids in the selected values.
Kindly suggest if there is a way to get the LOV values on the selected values box instead of ids. Users don’t understand id’s.
I was not able to paste the screen shot of my prompt here.
Oh! I see your problem now. The user starts on the parent report and clicks through to the child report. The index ID values are passed to the prompt. Now if the user opts to refresh the child report directly, in the prompt dialog box they are seeing the ID values rather than the names.
I had not run across this before. Now that I understand your question properly, I’m afraid I don’t have an immediate suggestion for you.
Hi Dave,
Thank you for the post, it is useful.
I have got a bit about the example you have used. Basically you have set keys for and object called Service which I guess would be the object to be index aware, but I do not see that object in the picture of the Result Objects panel nor in the Query Filter panel ????
Thanks for your reply
Hi Dave,
Very Nice Article.
If we just try with only table and assign Primary key at Database and Universe Level, then can we achieve the same thing?
Ex: Country (Countryid int,Country_Name varchar(10),Revenue int)
Countryid is Primary Key in Database(Sql server) and also at Universe level and this table is not joined to any other table.
If we use the objects Coutnry_Name and Revenue in the report and Country_Name as a filter(ValuesFromList) then will the background sql shows corresponding CountryId for the Country_Name which we select as a Filter?
Could you tell me if declaring keys in universe eats a considerable space or not while using index awareness
Defining keys does not dramatically increase the size of the universe. Remember that a universe contains only definitions, no actual data, so referencing additional structural data (keys) should not make it grow that much.
Dave, I’m currently working on implementing index awareness in one of my universes and noticed that distinct values feature going way when Index Awareness is enabled. And when the objects is used in the query, it show the values redundantly. Is this the known limitation or having any workaround.
Thanks you very much for the article thought.
Hi Dave,
I come in a situation where i have to remove IA to meet some requirements; will my reports affected which are using objects with IA i.e do i need to re-insert those objects in report after i remove keys? Also if reports with IA objects are feeding to Dashboards, will those be affected after removing keys?
1 more thing, you have mentioned that ” If the user enters a value manually, it does not work. If the user respond to a prompt, it does not work.”, what happens if value entered manually or using prompt i.e. SQL will not use id and table joins will be used?
Index Awareness should not have anything to do with the way the object works since it’s only relevant to the list of values and prompts. Removing it will change the where clause from
table.column_id = id_valuetotable.column = text_valuewhich could have an impact on performance.The point of IA is to replace a column value with a column ID. If a user enters a value manually then the IA process does not trigger and the column value is used in the condition instead, which I believe is all covered in the post.
That means the item you are trying to apply the index keys to is not properly unique within the table. It only works for unique items. The post covers the basics, but the full description – including the behavior you have described – is covered in my “Tales From a Universe Ninja” presentation. You can download the file and review it for a further explanation.
Hi Dave,
Sorry for a dumb question here, but would there be any relation between Shortcut Joins and Index Awareness, or would these two working in conjunction help generate a more efficient SQL?
I have a situation wherein I have set some shortcut joins in the universe to get rid of the longer path and have the SQL take the shorter path. However, I am seeing that, in certain scenarios, the SQL is giving preference to the regular joins instead of shorcut joins. Even changing the Shortcut Behaviour from Global to Successive is not helping.
So in this case, would setting the index awareness on the related objects help in any way?
Alec, in the presentation that inspired this blog post I do cover the interaction between shortcut joins and index awareness. Short answer: yes, they can and will work together. But if your shortcut joins are not being selected, there is probably something else going on and index awareness probably won’t fix that. It’s difficult to say without more information.
Thanks , very appropriate your post and clear explanation. There is only 2 things I don’t understand:
1- there is a combobox near the caption “Define primary key and foreign key for this object. What is it’s purpose?
2 – when I do a object integrity check i get this message : “Primary key of field … parse failed. The expression type is not compatible with the object type. But both are character type.
From what I can see, the checkbox next to each key definition allows you to selectively enable / disable that rule. For example, you might want to try out index awareness by setting up the keys and then disable specific rules for testing. The checkbox allows you to do that without deleting the rule and having to recreate it again later if desired. I’m not sure how to answer your second question as I have not experienced that issue myself.
Edit
Now I see that I answered the wrong question. The combo box is used to define the data type for the key columns. If your keys are character, then you should change the combo box to the appropriate data type. That would address your parse issue as well, I believe.
If I am reading your question correctly, then the answer is, “Yes, it will work.” Basically index awareness is responsible for substituting a key value for an attribute value during a query. While I have not specifically tested for an isolated table, I don’t see any reason why it would not work.
What will be situation if we will have complex join?
Hi Dave,
Very nice concept an really informative.
I’ve a similar reporting requirement. We have a table-DimDate. It holds all the dates in different formats. It has 2 columns-MonthName which holds values like- Apr2015, Mar2015 and so on. MonthKey which holds values 201504,201503 and so on.
I’d used LOV’s to display MonthName. The req is that I need to use prompt between 2 months. So, if I use MonthName, it won’t yield proper results.Hence, I used MonthKey to filter out the values while using prompt. But following the same steps doesn’t allow me to filter on monthKey. It is still filtering on MonthName only. Can you pls help 🙁
I am using SAP BO 4.0 IDT multisource unx.