XFOC Databases: If You Build Them, They will Come

1. XFOC Databases:If You Build Them, They will Come Noreen Redden FOCWizard June, 2008

2. Agenda • XFOC Databases • Sizing Considerations • Number of Instances / page • 4 Times Bigger ? • Efficiencies • Multiple Partitions • Horizontal and Vertical • Intelligent Partitioning • MDI (Multi-Dimensional Index) • Why use it vs. regular External Index • Components • ACCESS File • Building/Adding to MDI

3. XFOC Databases XFOCUS The Data Warehouse

4. XFOC Databases – Sizing Considerations • General: • Pages contain instances of a single SEGMENT, specific internal index, or values of text fields. • Page 1 contains ONLY the FDT • Page is 16K, with a maximum of 1K pages (maximum size of partition is 16gig. • Segment instances contain: • Pointer (at beginning of page) to point to specific instance on page • Pointer from instance to instance (chain, Parent to child, Child to parent) • Data Storage

5. XFOC Databases – Sizing Considerations • Segment Instances – Data Storage • Alpha (An) n bytes • Variable Alpha (AnV) n bytes + 2 for length • Integer 4 bytes • Packed 8 or 16 packed bytes • Float Single Precision 4 • Float Double Precision 8 • Text 8 byte pointer on Seg + actual length on text pages)

6. XFOC Databases – EX CALCFILE

7. XFOC Database - Efficiencies • For maximum efficiency, a chain of child instances should be able to be accessed in a single I/O. • Use Unique Segments to isolate infrequently used fields. • Partition historical data to reduce I/O needed for UPDATE. • Build/rebuild internal indexes AFTER large updates/loads • Increase XFBINs to increase page re-utilization. • Use Partitions to reduce the need to REBUILD external indexes/MDI’s

8. Vertical Partitioning

9. Intelligent PartitioningGGSALES FIELDNAME ALIAS FORMAT --------- ----- ------ SEQ_NO SEQ I6 CATEGORY E02 A11 PCD E03 A04 PRODUCT E04 A16 REGION E05 A11 ST E06 A02 CITY E07 A20 STCD E08 A05 DATE E09 I8YYMD UNITS E10 I08 DOLLARS E11 I08 BUDUNITS E12 I08 BUDDOLLARS E13 I08

10. Intelligent PartitioningUSE • DYNAM ALLOC F GGSALES1 DA PMSNMR.GGSLS2K.FOCUS SHR REU • DYNAM ALLOC F GGSALES2 DA PMSNMR.GGSALES.FOCUS SHR REU • USE • -IF &MDATE.ENTER LOWEST DATE FOR REPORT. GT 19991231 • GOTO NOT1; GGSALES2 AS GGSALES -NOT1 -IF &MDATE.ENTER HIGHEST DATE FOR REPORT. LT 20010101 -GOTO ENDIT GGSALES1 AS GGSALES -ENDIT END

11. Intelligent PartitioningACCESS File Points to the FOCUS Files FILENAME=GGSALES, SUFFIX=XFOC, ACCESS=GGSALES,$ SEGNAME=SALES01, SEGTYPE=S1 FIELD=SEQ_NO, ALIAS=SEQ, FORMAT=I6, TITLE='SEQUENCE#', $ FIELD=CATEGORY, ALIAS=E02, FORMAT=A11, INDEX=I, TITLE='Category', $ FIELD=PCD, ALIAS=E03, FORMAT=A04, INDEX=I, TITLE='Product ID', $ FIELD=PRODUCT, ALIAS=E04, FORMAT=A16, TITLE='Product',$ FIELD=REGION, ALIAS=E05, FORMAT=A11, INDEX=I, TITLE='Region', $ FIELD=ST, ALIAS=E06, FORMAT=A02, INDEX=I, TITLE='State', DESC='State',$ FIELD=CITY, ALIAS=E07, FORMAT=A20, TITLE='City', DESC='City',$ FIELD=STCD, ALIAS=E08, FORMAT=A05, INDEX=I, TITLE='Store ID', $ FIELD=DATE, ALIAS=E09, FORMAT=I8YYMD, TITLE='Date', $ MASTER=GGSALES ,$ DATA = PMSNMR.GGSLS2K.FOCUS , WHERE = DATE FROM 20000101 TO 20091231,$ DATA = PMSNMR.GGSALES.FOCUS , WHERE = DATE FROM 19900101 TO 19991231,$

12. Tip with Partitions • MODIFY/MAINTAIN only use the first partition. • External indexes must be rebuilt if the file changes. • Keep 2 MFDs: GGSALES (with ACCESS) ; GGSALE2 MODIFY FILE GGSALE2 … END TABLE FILE GGSALE2 … WHERE … MORE FILE GGSALES END

13. XFOC Databases MDI

14. MDI – Multi-Dimensional Index • Dimensions categorize data – • Sales may be categorized by REGION, BRANCH and PRODUCT Type, • Products may be categorized by PRODUCT CATEGORY, PRODUCT CODE, SIZE, COLOR • Employees may be categorized by Job code, Hire_date, Branch, etc. • A multi-dimensional index uses dimensions to point to specific facts. • MDI is a multi-field index to behave like a virtual “cube” of values that intersect at measures of interest. The more dimensions used, the better the retrieval performance. • MDIs may be used for Selection, JOIN, or reported from directly.

15. MDI – Multi-Dimensional Index • Know Your Data, and queries to choose index dimensions: • Fields used frequently as filters (Multiple fields used as filters within a single report/query should be in the same MDI. • Fields used as basis for vertical portioning • DEFINE fields • Fields with many unique values • Multiple MDIs may be created for separate subsets of data. • In a query, AUTOINDEX will choose the MDI to use based on • Segment most involved in the query • MDI with the most filtering conditions • Percent of index dimensions involved in the request from each MDI • Size of each MDI.

16. Intelligent PartitioningGGSALES FIELDNAME ALIAS FORMAT --------- ----- ------ SEQ_NO SEQ I6 CATEGORY E02 A11 PCD E03 A04 PRODUCT E04 A16 REGION E05 A11 ST E06 A02 CITY E07 A20 STCD E08 A05 DATE E09 I8YYMD UNITS E10 I08 DOLLARS E11 I08 BUDUNITS E12 I08 BUDDOLLARS E13 I08

17. ACCESS File Points to the MDIS MASTER=GGSALES ,$ DATA = PMSNMR.GGSLS2K.FOCUS , WHERE = DATE FROM 20000101 TO 20091231,$ DATA = PMSNMR.GGSALES.FOCUS , WHERE = DATE FROM 19900101 TO 19991231,$ MDI=GGMDI, TARGET_OF=SALES01 ,$ DIM=CATEGORY ,MAXVALUE = 100,$ DIM=PCD ,MAXVALUE = 50, WITHIN=CATEGORY,$ DIM=REGION ,MAXVALUE = 20,$ DIM=ST ,MAXVALUE = 20, WITHIN=REGION ,$ DIM=CITY ,MAXVALUE = 20, WITHIN = ST,$ DIM=STCD ,MAXVALUE = 20, WITHIN=CITY ,$ DIM=DATE ,MAXVALUE = 240,$ MDIDATA= PMSNMR.GGMDI1.FOCUS ,$ MDIDATA= PMSNMR.GGMDI2.FOCUS ,$

18. ACCESS File Points to the MDIS MDI=GGLOC, TARGET_OF=SALES01 ,$ DIM=REGION ,MAXVALUE = 20,$ DIM=ST ,MAXVALUE = 20, WITHIN=REGION ,$ DIM=CITY ,MAXVALUE = 20, WITHIN = ST,$ DIM=STCD ,MAXVALUE = 20, WITHIN=CITY ,$ DIM=DATE ,MAXVALUE = 240,$ MDIDATA= PMSNMR.GGLOC1.FOCUS ,$ MDIDATA= PMSNMR.GGLOC2.FOCUS ,$

19. Creating the MDI - REBUILD Enter option 1. REBUILD (Optimize the database structure) 2. REORG (Alter the database structure) 3. INDEX (Build/modify the database index) 4. EXTERNAL INDEX (Build/modify an external index database) 5. CHECK (Check the database structure) 6. TIMESTAMP (Change the database timestamp) 7. DATE NEW (Convert old date formats to smartdate formats) 8. MDINDEX (Build/modify a multidimensional index) MDINDEX NEW / ADD ? NEW ENTER MASTER FILE NAME GGSALES ENTER MD_INDEX LOCATION FILE NAME GGMDI ANY RECORD SELECTION TESTS? (YES/NO) NO NEW FILE PMSNMR.GGMDI1.FOCUS ON 04/09/2008 AT NEW FILE PMSNMR.GGMDI2.FOCUS ON 04/09/2008 AT Accumulating MDI Data ....

20. Adding a Partition - REBUILD Enter option 1. REBUILD (Optimize the database structure) 2. REORG (Alter the database structure) 3. INDEX (Build/modify the database index) 4. EXTERNAL INDEX (Build/modify an external index database) 5. CHECK (Check the database structure) 6. TIMESTAMP (Change the database timestamp) 7. DATE NEW (Convert old date formats to smartdate formats) 8. MDINDEX (Build/modify a multidimensional index) MDINDEX NEW / ADD ? ADD ENTER MASTER FILE NAME GGSALES ENTER MD_INDEX LOCATION FILE NAME GGMDI ANY RECORD SELECTION TESTS? (YES/NO) NO NEW FILE PMSNMR.GGMDI1.FOCUS ON 04/09/2008 AT NEW FILE PMSNMR.GGMDI2.FOCUS ON 04/09/2008 AT Accumulating MDI Data ....

21. Comparing TABLE FILE GGSALES PRINT * BY REGION BY ST WHERE REGION EQ 'Northeast' WHERE DATE EQ '19920501' WHERE ST EQ 'NY' WHERE PRODUCT EQ 'Capuccino‘ END 270,000 RECORDS

22. MDI – Other Facilities • JOIN field1 [AND field2…] IN file1 TO ALL mdiname IN file2 AS joinname • JOIN field1 [AND field2 …] IN file1 TO ALL dim1 [AND dim2 …] IN file2 AS joinname • Read Just the MDI • Must have MAXVALUES in the ACCESS. • Uses WITHIN in ACCESS to define hierarchies • SET MDIOENCODING = ON • TABLE FILE filename.mdiname request ON TABLE SET MDIENCODING ON END

23. Working with New Structures:Questions? Questions?