Views in Microstrategy

[MSTRJD]

Hi all,

I have a huge table with 100's of thousands of rows.

I have to create a report on this table with prompts.For example allowing users to select customer names,state,city...

Now if i use this table itself for the prompt and since the customer names,state,city repeats many times, i assume microstrategy will do distinct on each of these to display in the prompts and there will be performance issue like time outs and even hang up.

so i am planning to create a separate look up tables for each of these fields and use prompts on these so that prompt response time is increased.

Also i can create a view from this table which acts as a look up tables for customer,state,city... which will be very easy both creating and maintaining.

Can any one help me understand which is the better way? creating a table or view from microstrategy point of view in particular and from database design point of view in general.

Thanks.

 

[Z3]

Either way would be fine. You need to decide which way would be better for you. a View has fewer maintenance but cost you more (not much performance gain, if any). Your decision. 100,000s rows really is not that huge - depends on how wide it is.

 

[MSTRJD]

Hi Z3,

Thanks for your prompt response.

When I say '100's of thousands of rows' I didn't exactly mean 100,000 rows.It is multiples of 100,000.so it will be in several millions.Sorry about that.

I created prompts on this table itself but it took long time to load all the elements in the prompt and some times even hung up.

Please help me understand how creating the element prompts on the views created from this table will improve performance then creating the prompts on this huge table itself.

I am actually trying to understand some basics of data warehousing.

Once again thanks.

 

[reisw]

If you index your table and create a view on that table the response time should be that bad. It will all depend on how many unique values you have in your table. The more common tha values are the faster your view should be as long as that column is indexed. Remeber also that if you have caching enabled that after the first time you pull up the prompt any subsequent time should be faster. The choice between a table or view will depend on performance and maintainability.

 

[nlim]

Even with your lookup table or view, loading up many 100,000s of prompt elements to pick from might be relatively slow.

Do you users really want to pick from the entire prompt list? How about using element qualification instead?

 

[mstrNate]

All good suggestions. One other thing to consider is that when you model these fields into MicroStrategy, if you haven't done it already, you will probably want to set up hierarchies, and these will allow you to pull gradually larger sets of data, starting with smaller ones initially (usually).

For example, you were talking about some customer type data. You could set up a hierarchy called Customer Hierarchy (if you haven't done so already), which had state as the top level attribute, then a child of that attribute would be city, and then customer.

What that might give you the ability to do is a hierarchy prompt (Customer Hierarchy). So then, if you wanted to drill down from state to customer, MicroStrategy would do something like the following:

SELECT DISTINCT State
FROM TableWithAllThatData

Then you drill to city from a particular state, like Texas...

SELECT DISTINCT City
FROM TableWithAllThatData
WHERE State = "Texas"

Then you drill to customer from the city, say, Dallas...

SELECT DISTINCT Customer
FROM TableWithAllThatData
WHERE City = 'Dallas'

In that manner you could avoid getting too much data for some of your attributes. As I said above, I like the other suggestions also.

Nate

 

[MSTRJD]

Hi all,

Thanks a lot for all your suggestions.It's really giving me a very good understanding on the optimizing the query.

 

[entaroadun]

I'd like to second nlim's concerns. The speed of the query is important, but less important than the configuration on the app server. When you load a list of elements, the list isn't loaded directly from the table to the client. It enters the attribute element cache on the app server; memory has to be allocated to hold a subset of the elements, and as you scroll through the element list, elements are constantly being cycled into and out of the cache.

It's slow and hard on the box. Think of opening a 2 mil record rowset and pointing to it with a Microsoft Jet cursor.

Therefore it's critical that you set up a browse hierarchy that efficiently and gently guides the user to the address.


I believe there is a VLDB setting that allows you to turn off the DISTINCT option. This will speed up the SQL. I would recommend using tables or materialized views as well.