In my last post, I warned you about hooking up your huge dataset to a dynamic prompt. Impending disaster, user unhappiness and worst of all – rework! Ahhhh the horror of it all!!!
I wish I knew a way to tell you when the dataset was too large for the dynamic prompt, but it is really dependant on your system. There are a lot of factors that influence stored process speed such as hardware, network, and even if the data lives in a database. Here’s a some alternate strategies for working with larger datasets in your dynamic prompt. [Quentin provided a tip for working with selection list in this post.]
For these examples I’m using the Consumer Complaints dataset that I downloaded from the Consumer Protection Bureau to use as example data. I like this dataset because it has its own built-in drama. What were people complaining about? Kinda like when you see a car accident and you know it might be gross but you can’t stop yourself from looking? Yeah that.
So far in 2014, there are two credit reporting agencies in the top 3 for the most complaints logged. Congratulations!
Use a Pre-Built Tiny Dataset
You already know that the dynamic prompt runs code to get the list as shown in the last post. One way around the larger dataset is to create a smaller pre-filtered dataset for the prompt to use based on the larger dataset. Then use that dataset to populate the prompt, thus eliminating most of the work the prompt needs to do at run time.
In this example, I have a CONSUMER_COMPLAINTS2 dataset with a gazillion company names listed. So I created a second dataset called CONSUMER_COMPLAINTS_TINY that just contained a distinct list of the companies. When you build your prompt, just use the smaller dataset as the data source. Since both datasets contain the same values, it shouldn’t create an issue for the stored process.
Use a Dependant Prompt with ABCs
Another problem with a long list is that is takes the user forever to sift through it to find the company they want. Probably if the company name starts with an A or Z it’s not that bad – but if it’s in the middle of the alphabet it takes forever. You can use a dependant prompt to remedy the situation.
In this example the first prompt contains the letters of the alphabet and the second prompt only pulls the names of the values that start with that letter (see below). I used a static prompt and a dynamic prompt.
Preparing the data
In the base dataset I added a second column that just contained the first letter of the company name. I also did a special column for those companies that contained a digit.
Adding a Static Prompt
Use a static prompt populated with the first alphabet character. I just typed the letters myself as shown in the example below. Notice I also added the Number value for those companies whose names start with a digit.
Adding the Dynamic and Dependant Prompt
I created a second prompt that is dynamic. It can contain the smaller list shown in the first tip or just be an ordinary dynamic prompt. However the trick is to add the dependant prompt. Here’s an example of how I did that. [Check this post for more specific steps.]
Never miss a BI Notes post!
Click here for free subscription. Once you subscribe you'll be asked to confirm your subscription through your email account. You email address is kept private and you can unsubscribe anytime.