Adding a Listing to the Cube

Adding a Listing to the Cube

A listing enables the users to see selected fields from the lowest-level data, which is useful in many scenarios. This information can help users identify outlier records or any records where follow-up activity might be needed.

1. First, let us examine the available fields in the Patients table.

   1. Access the Management Portal and go to the namespace into which you installed the samples, as described earlier.

      (If this is open on another browser tab, switch to that tab.)

   2. Click System Explorer > SQL.

   3. Click the Execute Query tab.

   4. Execute the following query:

      select * from bi_study.patient

      

      This displays the first 1000 patients and shows the available fields.

   5. Now try a query like the following:

      select patientid, age,testscore,homecity->name as "City",
      primarycarephysician->lastname as "Doctor" from BI_Study.Patient

      

   6. Copy the query to Notepad or to any other convenient temporary location.

   Leave this browser tab or window open for later use.

2. Add a listing that uses the fields in the query we just ran:

   1. Access the Architect.

      (If this is open on another browser tab, switch to that tab.)

   2. Click Add Element.

   3. For Enter New Element Name, type SampleListing.

   4. Click Listing.

   5. Click OK.

      The system adds the listing.

   6. In the Details pane, copy the list of fields from the earlier saved query to the Field list area, removing select as seen below:

      patientid, age,testscore,homecity->name as "City", primarycarephysician->lastname as "Doctor"
      

      

      The system uses this list of fields and builds the SQL query.

   7. Compile the cube.

      When you do so, the Architect saves the cube.

      You do not need to rebuild the cube.

3. Verify that you can access this listing in the Analyzer. To do so:

   1. Access the Analyzer.

      (If this is open on another browser tab, switch to that tab and click the Analytics > Analyzer link to refresh with the most current model.)

   2. Click a cell in the pivot table already displayed in the preview area, or create a simple pivot table and click a cell in it.

   3. Click the Display Listing button .

      The system displays something like the following:

      
      Note:

      The system displays the first 1000 records by default. You can change this within the Analyzer.

   If you instead get a message that listings are not supported, make sure that you saved and recompiled the cube.

4. Modify the listing to sort the records in a different way:

   1. Access the Architect again.

   2. Click the listing in the Model Contents area.

   3. In the Details pane, enter the following into Order By:

      age,homecity->name
      

      

   4. Compile the cube.

      When you do so, the Architect saves the cube.

5. Verify that the listing is now sorted by age, and then by city within age.

   Display a listing as before. You should see something like this:

   

   Scroll down to verify that patients are sorted by city within age.