So, we have the sales amounts expressed in different currencies and we must address this issue before going too far. The CFO can't get any sleep until we fix the exchange rate adjustment, which is actually generating a distortion in the bottom line and undermining our CV.
The exchange rate is stored in a different fact table and is related many-to-many to both FactInternetSales and FactResellerSales tables. How to deal with that?
Well, I beg your pardon, I consider myself a practical person and sometimes lazy; as usual, different DBAs will have different solutions, more or less elegant. I personally prefer modifying the fact tables in this situation. Given that the exchange rate of a currency in the past doesn't change over time, and that multiplying one number by another is relatively easy in the ETL phase, I tend to pass this on the DBMS side. So, we will create an additional column in the two fact tables, named USDSalesAmount and populate them with SalesAmount * EndOfDayRate.
For those of you that have no free access to the data warehouse, may I suggest a logical table?
In this recipe, we introduce another type of MicroStrategy objects: the prompts. They serve a particular function asking the user for an input so that MicroStrategy can generate a customized SQL adding flexibility to the user's reporting needs.
To modify the fact tables in the sqlcmd utility, run these commands (find them in the companion code file):
ALTER TABLE FactResellerSales ADD USDSalesAmount money go update F set F.USDSalesAmount = SalesAmount * EndOfDayRate from FactResellerSales F join FactCurrencyRate R on F.OrderDateKey = R.DateKey and F.CurrencyKey = R.CurrencyKey go ALTER TABLE FactInternetSales ADD USDSalesAmount money go update F set F.USDSalesAmount = SalesAmount * EndOfDayRate from FactInternetSales F join FactCurrencyRate R on F.OrderDateKey = R.DateKey and F.CurrencyKey = R.CurrencyKey go
Now the two fact tables include an extra column with the amount converted to US dollars. If you're a SQL rock star, feel free to add a column in your local currency also.
Next, create two new facts based on those columns and two metrics named, Sum USD SalesAmount from FactResellerSales and Sum USD SalesAmount from FactInternetSales respectively.
We want to create a report where the user is prompted for one or more products before selecting data from the database:
- Start by creating a blank report with Product on rows, Year on columns, and Sum USD SalesAmount from FactResellerSales on columns.
- Run the report with no filter and see the result. Now minimize this window and switch back to the Desktop application.
- Go to Public Objects | Prompts and create a new prompt. You'll see a window, like the following screen capture:
- Select Choose from an attribute element list and click on Next.
- Click on the ellipses button, pick the Product attribute, and click on OK.
- Click on Next three times and then on Finish, save it with the name
Select one or more Products. - Go to Public Objects | Filters, create a new empty filter.
- In the Filter Editor, click on the Public Objects shortcut, browse to Prompts, click on the newly created Select one or more Products prompt and drag it onto the right filter definition part.
- When you release the mouse button MicroStrategy creates a shortcut to the prompt inside this. Click on Save and Close, name it
Prompted Product Filter. - Restore the Report Grid window and change it to Design View.
- Drag the Prompted Product Filter object to the report filter, MicroStrategy creates a shortcut to the filter inside the report.
- Execute the report. Before running the query, you are prompted to select one or more products from the left list. Choose some and move them to the right side of the cart, click on Next and then on Finish.
- When the grid appears only the selected products are displayed.
- Save this report as
38 Prompted Filter on Products.
Prompts allow the BI developer to create generic reports and let the user decide how to filter the results. There are several options when creating prompts, for example, the number of permitted answers, or if an answer is required. You may also set a default answer or allow the user to save their personal selection for the next executions. Please refer to the product manuals for more details.
Pay attention when saving a report with a prompt. You'll see a dialog like the one shown in the following screen capture:
If you want the user to be prompted every time the report runs, select the second radio button (Prompted). Otherwise, your current prompt selection will be saved within the report definition and the prompt will never appear again.
When looking at a prompted report, the user may want to change the current filter settings and select, for example, different products.
To do this, one must click on the Re-prompt button, which is almost invisible in the toolbar, and looks like a question mark between parentheses, as shown in the following icon:
