Transaction Services is a new feature in MicroStrategy starting with version 9.2.1 that supports write-back (Insert, Update and Delete) from a Report Services Document to a Data Warehouse or Transaction Database.
This technical paper discusses direct transactions with the database using the Freeform SQL. It describes the different ways of inserting your inputs into the warehouse through a mobile interface, from start (creating a transaction report) to finish.Process Of Implementing A Transactional Dashboard (For Guest Feedback Form)Create a Transactional Table (‘Transac’ in this case) in the Database with the columns that have to be inserted, updated, or deleted during the Transaction.
Import the table into the Catalog and update the schema.Create a Transaction report.
- To create the Transaction report, open the Report editor in MSTR Desktop.
- From the Grid window, select Freeform Sources.
- Select Create Transaction report and click OK.
- Select the proper Database Instance from the list of Data sources and click OK.
- In the Freeform SQL Editor, define the transaction. To define the transaction in the SQL statement pane, right-click an empty area and select the option ‘Insert Transaction’.
- The Transactions options window will open.
- In that window, keep the “Insert only distinct records” check box cleared if you want all records to be inserted when a transaction triggers an update. This action is intended for using Transaction services to update fact tables that store fact data.
- Select the “Insert only distinct records” check box if you want all records to be inserted when a transaction triggers an update. This action is intended for using Transaction services to update lookup tables that store attribute information.
- Click OK.
- In the SQL statement pane, between the Begin Transaction and End Transaction placeholders, type the SQL statement to update, insert, or delete values in the data source.
- In the SQL Statement pane, right-click in the area and select the Define New Input option to define the Input object and choose the object form (Attribute Form/ Metric form).
- In the object field of the Mapping pane, type the name of the input object and press ENTER.
- Set any dummy metric as Output object, as it is mandatory, and click OK.
- Save and close the report.
- Follow the same process for ‘Update’ and ‘Delete’ statements.
Create a Query report, which is a Freeform SQL report (with Select statement to retrieve data from the Transaction Table) or MSTR Grid report (with Attributes and Metrics to retrieve data from the Transaction Table) that helps to display the Transaction data in the document interface.Create a Transaction dashboard that fits the iPad screen in the MicroStrategy desktop with required text fields and labels requested by the customer. Save the document.
- Open the document on the web in design mode. Right-click on any text box and select the ‘configure transaction’ option. The configuration editor opens as shown below.
- Choose the transaction report (Transac_Report1) that you want to configure with the dashboard. This is where the transaction report is associated with the document.
- The Input Properties section of the editor gets auto-populated with Inputs available on the Transaction report.
- Select the appropriate fields for each transaction input and select the control style from the drop-down list (Text, Slider, Switch, Toggle, and List), click OK and save the document.
- Insert an Action Selector Button (new in MicroStrategy 9.2.1) and set the grid associated with the Transaction report as its target. In the selector properties editor, set the name and action type as ‘Submit’.
To display the transaction data on the document interface, choose the Query report created on the Transaction Table as dataset, select the option Run a new report or document in action selector button properties and formatting and choose the Query report.
Open the document in iPad and fill in the details per the instructions provided. Different selectors are used to provide different inputs. Some of the selectors are listed below.Enter the input text directly by typing it in (e.g., Guest Name, Email address, etc.)
Select the input value from the list of options provided.
Select the input values from the range of values provided by sliding.
Choose an existing or take a new photo to upload it to the server on which the MicroStrategy I-Server has been installed. Refer to TN Key: 38007 in the MicroStrategy knowledge base for the procedure to implement this widget.
After entering all the details, click on the ‘action selector button’ (In this case, Submit). A message will appear after the submission confirming the transaction. The results will be written back to the ‘Transac’ table in the warehouse and the data will be displayed.
- Transaction services is supported only in MicroStrategy versions 9.2.1 and above.
- Users must have the latest version of the MicroStrategy App installed on iPad.
- It is always better to have a dedicated transactional table created in the database instead of performing transactions on the existing tables.
- Users must have Read/Write access to transactional table in the database or warehouse.
