Skip to main content

Accessing Other Classes

Accessing Other Classes

The Architect provides easy access to most of the properties within the base class, but we can use other properties, as well, including properties of classes that you can access only via SQL. In this part of the tutorial, we use data in the BI.Study.PatientDetails class as levels in our cube.

The BI.Study.Patient and BI.Study.PatientDetails classes are not connected by a class property and do not have any formal connection. Instead, both tables have a PatientID property, which connects them by convention. That is, to find information for a given patient, you must find the records that have the same PatientID in these two tables.

In this exercise, we examine the data in BI.Study.PatientDetails, try various SQL queries, and wrap a query in a method for use in defining a level. If you are more adept with SQL, you might want to skip some of the earlier steps.

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

  2. Click System Explorer > SQL.

  3. Click the Execute Query tab.

  4. Execute the following query:

    SELECT PatientID FROM BI_Study.Patient
  5. Make a note of one of the PatientID values, for future reference.

  6. Execute the following query:

    SELECT * FROM BI_Study.PatientDetails WHERE PatientID='SUBJ_100301'

    The system displays something like the following:

    generated description: patientdetail fields

  7. Execute the following query:

    SELECT FavoriteColor FROM BI_Study.PatientDetails WHERE PatientID='SUBJ_100301'

    The system displays something like the following:

    generated description: patientdetail fields fav color

    Now we need to write a class method that runs a similar query and returns the value obtained by the query.

    This method will contain a query wrapped in &sql(). We need to make the following changes to the query:

    • Instead of FavoriteColor, we must use FavoriteColor INTO :ReturnValue so that the returned value is written to a host variable named ReturnValue.

    • Instead of using 'SUBJ_100301', we must pass in the PatientID field of the base class.

    After executing the embedded SQL, the method should check the variable SQLCODE, which is 0 only for a successful query. The query would be unsuccessful if no record was found. In such a case, it would be appropriate to return an empty string.

  8. In Studio, add the following method to your cube class, Tutorial.Cube:

    ClassMethod GetFavoriteColor(patientID As %String) As %String
    {
     &sql(SELECT FavoriteColor INTO :ReturnValue FROM BI_Study.PatientDetails WHERE PatientID=:patientID)
     If (SQLCODE'=0) {
      Set ReturnValue=""
      }
     Quit ReturnValue
    }
    Note:

    There is an index on the PatientID field in BI.Study.PatientDetails. This enables the query to run more quickly than it would otherwise.

    If an application does include tables that can be related most easily through SQL queries, as in this example, it probably already has indexes on the relevant fields. Whenever you write a method like this, however, you should make sure that the appropriate indexes exist.

  9. Save and compile the class.

  10. In the Terminal, test the method as follows:

    write ##class(Tutorial.Cube).GetFavoriteColor("SUBJ_100301")
    
    
  11. Access the Architect.

  12. Create a new dimension, hierarchy, and level, as follows:

    1. Click Add Element.

    2. For Enter New Element Name, type ColorD.

    3. Click Data Dimension.

    4. Click OK.

      The system creates a dimension, hierarchy, and level.

    5. Rename the level to Favorite Color.

    6. For the level, type the following into Expression:

      ##class(Tutorial.Cube).GetFavoriteColor(%source.PatientID)
      

      This expression is executed when you build the indexes; see the notes about performance in the previous step.

    7. For Field name in fact table, specify DxFavColor

      This will make it easier for us to see how the level definition affects the generated tables.

  13. Save the cube.

    Because you have edited the class in Studio, the Architect displays a dialog box that asks whether you want to override the stored definition. Click OK. The Architect overrides only the parts of the class definition that you can edit in the Architect; that is, it does not override any methods you have added to the class.

  14. Compile the cube.

  15. Rebuild the cube.

    The system executes your method and its embedded SQL once for each record in the base table.

  16. Open the Analyzer and display the new level as rows. Now you should see something like the following:

    generated description: favorite colors as rows

  17. Optionally open the level table for this level:

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

    2. Click System Explorer > SQL.

    3. In the left area, navigate to and open the table Tutorial_Cube.DxFavColor.

      The system displays something like the following:

      generated description: level tables fav color

FeedbackOpens in a new tab