Hello forum!

I'm working on a project with our database engineers to create our newest requested datamart, warehoused in a star schema / dimensional model approach. Our warehouse, up until now, has generally been a wide array of tables, flattened into different level views. Our master files would at a single view, and that would be our datamart. For the next iteration, our database engineers have organized the data into fact tables and dimension tables. The fact table contains measurable information about the level of data you're looking at, and the dimension tables can be joined to to filter and/or group this data.

For the master files, we want to have a single fact master file per datamart housing those measures (in this case, item information - costs, weights, etc), and all of the dimensions (location information, business divisions, etc) will be held in their own master files in a folder that isn't visible to the user. The user will select the fact table master file when creating a new report, and the dimensions come with it. We want to utilize the logical view for our more advanced users, while still allowing the structured view that would generally look the same as our existing datamarts. We also want to be able to reuse dimensions in other fact tables, and ideally not have to reference each field within the main fact table master file. That way, if we add a new field to a dimension, every datamart that references that dimension automatically gets the new field added in the appropriate sections.

We're about 90% of the way there, so that's a long story for 2 simple questions. The dimension and hierarchy sections create the logical view in the way that we want it to, but the structured view pretty much throws the ordering of the segments out the window. We're not using a business view at the moment. So, my two questions, that are completely unrelated:

1. Can we use a business view who's folders specifically reference segments and automatically include all the fields of that segment?
2. In the logical view, inside a dimension, can the "values" drop down be turned off? Searching for distinct values will cause unnecessary load on the database.

Trimmed down examples of my master files are below. Let me know if anyone has any thoughts or needs more information. Thanks!
-Scott Serr

Fact:

FILENAME=order_item_charge, SUFFIX=SQLORA  , $
  SEGMENT=ORDER_ITEM_CHARGE, SEGTYPE=S0, DESCRIPTION='Order Item Charge', $
    FIELDNAME=ORDER_ITEM_CHARGE_ID, ALIAS=ORDER_ITEM_CHARGE_ID, USAGE=P13, ACTUAL=P7, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=ORIG_LOCATION_DIM_ID, ALIAS=ORIG_LOCATION_DIM_ID, USAGE=P11, ACTUAL=P6, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=DEST_LOCATION_DIM_ID, ALIAS=DEST_LOCATION_DIM_ID, USAGE=P11, ACTUAL=P6, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=PRODUCT_DIM_ID, ALIAS=PRODUCT_DIM_ID, USAGE=P11, ACTUAL=P6, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=ORDER_UNIQUE_REF_NUMBER, ALIAS=ORDER_UNIQUE_REF_NUMBER, USAGE=A30V, ACTUAL=A30V, TITLE='Order Unique Reference Number',
      MISSING=ON, MEASURE_GROUP=ORDER_ITEM_CHARGE,
      PROPERTY=MEASURE,  $
    FIELDNAME=COST_CURRENCY, ALIAS=COST_CURRENCY, USAGE=A3V, ACTUAL=A3V, TITLE='Currency Code',
      MISSING=ON, MEASURE_GROUP=ORDER_ITEM_CHARGE,
      PROPERTY=MEASURE,  $
    FIELDNAME=COST_PER_PIECE, ALIAS=COST_PER_PIECE, USAGE=P12.2, ACTUAL=P8, TITLE='Cost Per Piece',
      MISSING=ON, MEASURE_GROUP=ORDER_ITEM_CHARGE,
      PROPERTY=MEASURE,  $
    FIELDNAME=ITEM_WEIGHT_POUNDS, ALIAS=ITEM_WEIGHT_POUNDS, USAGE=P16.4, ACTUAL=P8, TITLE='Item Weight',
      MISSING=ON, MEASURE_GROUP=ORDER_ITEM_CHARGE,
      PROPERTY=MEASURE,  $
    FIELDNAME=ITEM_PIECES, ALIAS=ITEM_PIECES, USAGE=P11, ACTUAL=P8, TITLE='Item Pieces',
      MISSING=ON, MEASURE_GROUP=ORDER_ITEM_CHARGE,
      PROPERTY=MEASURE,  $
  SEGMENT=PRODUCT_DIM, SEGTYPE=KU, PARENT=ORDER_ITEM_CHARGE, CRFILE=DIMENSIONS/PRODUCT_DIM, CRINCLUDE=ALL, DESCRIPTION='Product',
    JOIN_WHERE=ORDER_ITEM_CHARGE.PRODUCT_DIM_ID EQ PRODUCT_DIM.PRODUCT_DIM_ID;, $
  SEGMENT=ORDER_PICK_LOCATION_VW, SEGTYPE=KU, PARENT=ORDER_ITEM_CHARGE, CRFILE=DIMENSIONS/ORDER_PICK_LOCATION_VW, CRINCLUDE=ALL, DESCRIPTION='Order Pick Location',
    JOIN_WHERE=ORDER_ITEM_CHARGE.ORIG_LOCATION_DIM_ID EQ ORDER_PICK_LOCATION_VW.ORDERPICKLOC_LOCATIONDIMID;, $
  SEGMENT=ORDER_DEST_LOCATION_VW, SEGTYPE=KU, PARENT=ORDER_ITEM_CHARGE, CRFILE=DIMENSIONS/ORDER_DEST_LOCATION_VW, CRINCLUDE=ALL, DESCRIPTION='Order Destination Location',
    JOIN_WHERE=ORDER_ITEM_CHARGE.DEST_LOCATION_DIM_ID EQ ORDER_DEST_LOCATION_VW.ORDERDESTLOC_LOCATIONDIMID;, $
MEASUREGROUP=ORDER_ITEM_CHARGE, CAPTION='Order Item Charge', $
DIMENSION=PRODUCT_DIM, CAPTION='Product', $
  HIERARCHY=PRODUCT_DIM, CAPTION='Product', HRY_DIMENSION=PRODUCT_DIM, HRY_STRUCTURE=STANDARD, $
DIMENSION=LOCATION_DIM, CAPTION='Order Locations', $
  HIERARCHY=ORDER_PICK_LOCATION_VW_DIM, CAPTION='Order Pick Location', HRY_DIMENSION=LOCATION_DIM, HRY_STRUCTURE=STANDARD, $
  HIERARCHY=ORDER_DEST_LOCATION_VW_DIM, CAPTION='Order Destination Location', HRY_DIMENSION=LOCATION_DIM, HRY_STRUCTURE=STANDARD, $

Product Dimension:

FILENAME=PRODUCT_DIM, SUFFIX=SQLORA  , $
  SEGMENT=PRODUCT_DIM, SEGTYPE=S0, $
    FIELDNAME=PRODUCT_DIM_ID, ALIAS=PRODUCT_DIM_ID, USAGE=P11, ACTUAL=P6, ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=PRODUCT_CODE, ALIAS=PRODUCT_CODE, USAGE=A20V, ACTUAL=A20V, TITLE='Product Code',
      MISSING=ON, WITHIN='*PRODUCT_DIM', $
    FIELDNAME=PRODUCT_SHORT_DESCRIPTION, ALIAS=PRODUCT_SHORT_DESCRIPTION, USAGE=A20V, ACTUAL=A20V, TITLE='Product Short Description',
      MISSING=ON, WITHIN='*PRODUCT_DIM', $
    FIELDNAME=PRODUCT_LONG_DESCRIPTION, ALIAS=PRODUCT_LONG_DESCRIPTION, USAGE=A60V, ACTUAL=A60V, TITLE='Product Long Description',
      MISSING=ON, WITHIN='*PRODUCT_DIM', $

This message has been edited. Last edited by: Tamra,

To clarify... When I view the fact master file in infoassist, in the logical view, my dimensions are listed in the proper order as listed in segment order or dimension/hierarchy order (Product, Location/Pick, Location/Drop). When I view it in the structured view, the ordering of the folders is arbitrary. It will list all the measures, then will have 3 folders in random order containing the dimensions. This is what we want to fix.

-Scott

Hi smserr1,

Welcome to the Focal Point Forum!

I noticed that you have posted a topic in the WebFOCUS Forum referencing this topic. A link to the Business View section of the Using the Synonym Editor documentation was provided.


Dimension View and Business View

I understand that you have reviewed some of this information already.

The case that you opened can be used to address your issues.

Once you have a solution for your questions can you please post an update here or on the WebFOCUS forum so that others can benefit from the answers.

Thank you for participating in the Focal Point Forum.

Kind Regards,
Tamra Colangelo
IBI Focal Point Moderator