OLAP ÀÀ¿ëÇÁ·Î±×·¥ ¹× ´ÙÂ÷¿ø µ¥ÀÌÅͺ£À̽º¸¦ ÀÛ¼ºÇϸé DB2 OLAP Server¿¡¼ »õ ÀÀ¿ëÇÁ·Î±×·¥ ¹× µ¥ÀÌÅͺ£À̽º¸¦ īŻ·Î±×ÈÇÏ°í ½ºÅ¸ ½ºÅ°¸¶¶ó ÇÏ´Â °ü°èÇü Å×ÀÌºí ¼¼Æ®¸¦ ÀÛ¼ºÇÕ´Ï´Ù. ¶ÇÇÑ, DB2 OLAP Server´Â ´ÙÂ÷¿ø µ¥ÀÌÅÍ¿¡ ´ëÇÑ SQL ÀÀ¿ëÇÁ·Î±×·¥ ¾×¼¼½º¸¦ °£´ÜÇÏ°Ô ¸¸µå´Â ¿©·¯ °¡ÁöÀÇ ºä¸¦ ÀÛ¼ºÇÏ°í °ü¸®ÇÕ´Ï´Ù. ÀÌ·¯ÇÑ ºä¸¦ »ç¿ëÇÏ¿© ´ÙÂ÷¿ø µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇϱâ À§ÇØ »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥°ú Ç¥ÁØ Á¶È¸ µµ±¸¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀϺΠÀÀ¿ëÇÁ·Î±×·¥Àº DB2 OLAP Server¿¡ ÀÇÇØ ÀÛ¼ºµÈ ½ºÅ¸ ½ºÅ°¸¶¿¡ ÀúÀåµÇ´Â µ¥ÀÌÅ͸¦ ¿ÏÀüÈ÷ ÀÌ¿ëÇϵµ·Ï ¼³°èµË´Ï´Ù.
´ÙÀ½ ¸ñ·ÏÀº DB2 OLAP Server¿¡¼ °ü¸®µÇ´Â Àüü ºä ¼¼Æ®¸¦ º¸¿©ÁÝ´Ï´Ù.
DB2 OLAP Server´Â ÇØ´çµÇ´Â ¸ðµç ±âº» Å×À̺í°ú ºä¸¦ username ½ºÅ°¸¶¿¡ ÀúÀåÇÕ´Ï´Ù. ¿©±â¼ usernameÀº DB2 OLAP Server¿¡ ÁöÁ¤µÇ´Â »ç¿ëÀÚ IDÀÔ´Ï´Ù. ÀÌ Àå¿¡ ÀÖ´Â SQL ¿¹Á¦ÀÇ °æ¿ì, ½ºÅ°¸¶ À̸§ OLAPSERV°¡ »ç¿ëµË´Ï´Ù.
¸ðµç ºä À̸§Àº ´ë¹®ÀÚ·Î µÇ¾î ÀÖ½À´Ï´Ù. ºä À̸§À» µû¿ÈÇ¥·Î ¹Áö ¸¶½Ê½Ã¿À. DB2 OLAP Server´Â ºä À̸§À» ±¸¼ºÇÏ°í ±× À̸§µéÀ» īŻ·Î±× ºä¿¡ ÀúÀåÇÕ´Ï´Ù. SQL ÀÀ¿ëÇÁ·Î±×·¥Àº īŻ·Î±× ºä¿¡¼ ºä À̸§À» Á¶È¸ÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×¸² 9¿¡¼´Â ±âº»ÀûÀÎ DB2 OLAP Server ºä¸¦ º¸¿©ÁÝ´Ï´Ù.
±×¸² 9. DB2 OLAP Server ½ºÅ°¸¶
![]() |
DB2 OLAP Server°¡ ÇØ´çµÇ´Â username ½ºÅ°¸¶¿¡¼ »ç¿ëÇÏ´Â ÇϳªÀÇ Å¥ºê īŻ·Î±× ºä°¡ ÀÖ½À´Ï´Ù. ÀÌ ºä¿¡´Â °¢ Å¥ºê´ç ÇϳªÀÇ ÇàÀÌ µé¾î ÀÖ½À´Ï´Ù. ½ºÅ°¸¶¿¡ ÀúÀåµÈ ¸ðµç OLAP ÀÀ¿ëÇÁ·Î±×·¥ ¹× Å¥ºê¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×À» º¸·Á¸é ÀÌ ºä¸¦ »ç¿ëÇϽʽÿÀ. Å¥ºê īŻ·Î±× ºä´Â DB2 OLAP Server¿¡¼ °ü¸®ÇÏ´Â ¸ðµç OLAP ÀÀ¿ëÇÁ·Î±×·¥ ¹× µ¥ÀÌÅͺ£À̽º¸¦ īŻ·Î±×ÈÇÕ´Ï´Ù.
Å¥ºê īŻ·Î±× ºä À̸§Àº CUBECATALOGVIEWÀÔ´Ï´Ù. ´Ù¸¥ ¸ðµç ºä¿Í ¸¶Âù°¡Áö·Î, ±× ºä´Â DB2 OLAP Server¿¡ ÁöÁ¤µÈ ½ºÅ°¸¶¿¡¼ ¼ÒÀ¯ÇÕ´Ï´Ù.
Ç¥ 14´Â Å¥ºê īŻ·Î±× ºäÀÇ ¿À» º¸¿©ÁÝ´Ï´Ù.
Ç¥ 14. Å¥ºê īŻ·Î±× ºäÀÇ ³»¿ë
´ÙÀ½ SQL¹®À» »ç¿ëÇÏ¿© OLAP ÀÀ¿ëÇÁ·Î±×·¥ ¸ñ·ÏÀ» È®º¸ÇϽʽÿÀ.
SELECT DISTINCT APPNAME FROM OLAPSERV.CUBECATALOGVIEW
´ÙÀ½ SQL¹®À» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥ Sample¿¡¼ ´ÙÂ÷¿ø µ¥ÀÌÅͺ£À̽º ¸ñ·ÏÀ» È®º¸ÇϽʽÿÀ.
SELECT CUBENAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='Sample'
´ÙÀ½ SQL¹®À» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥ Sample¿¡¼ ´ÙÂ÷¿ø µ¥ÀÌÅͺ£À̽ºÀÇ ºä À̸§À» È®º¸ÇϽʽÿÀ.
SELECT CUBEVIEWNAME,FACTVIEWNAME,STARVIEWNAME,ALIASIDVIEWNAME,LROVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='Sample' AND CUBENAME='Basic'
Å¥ºê ºä¿Í Â÷¿ø ºä¿¡´Â °ü°èÇü Å¥ºê¿¡ ÀÖ´Â Â÷¿ø°ú ±¸¼º¿ø¿¡ °üÇÑ Á¤º¸°¡ µé¾î ÀÖ½À´Ï´Ù. °¢ °ü°èÇü Å¥ºê¿¡ ´ëÇØ ÇϳªÀÇ Å¥ºê ºä°¡, °ü°èÇü Å¥ºê ³»ÀÇ °¢ Â÷¿ø¿¡ ´ëÇØ¼´Â ÇϳªÀÇ Â÷¿ø ºä°¡ ÀÖ½À´Ï´Ù. ÀÌ·± ºä´Â OLAP À±°û¿¡ ÀÖ´Â Â÷¿ø ¹× ±¸¼º¿ø¿¡ ÁöÁ¤µÇ´Â ¿©·¯ ¼Ó¼ºÀ» Á¶È¸ÇÒ ¶§ »ç¿ëµË´Ï´Ù.
DB2 OLAP Server°¡ °ü¸®ÇÏ´Â °¢ °ü°èÇü Å¥ºê¿¡ ´ëÇØ ÇϳªÀÇ Å¥ºê ºä°¡ ÀÖ½À´Ï´Ù. Å¥ºê ºä¿¡´Â °ü°èÇü Å¥ºê¿¡ ÀÖ´Â °¢ Â÷¿ø¿¡ ´ëÇØ ÇÑ ÇàÀÌ µé¾î ÀÖ½À´Ï´Ù. Å¥ºêÀÇ Â÷¿ø¿¡ °üÇÑ Á¤º¸¸¦ º¸·Á¸é ÀÌ ºä¸¦ »ç¿ëÇϽʽÿÀ.
Å¥ºê ºä À̸§Àº Å¥ºê īŻ·Î±× ºäÀÇ CubeViewName¿¡¼ È®º¸ÇÒ ¼ö ÀÖ½À´Ï´Ù.
Ç¥ 15´Â Å¥ºê ºäÀÇ ¿À» º¸¿©ÁÝ´Ï´Ù.
Å¥ºê ºäÀÇ µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇÏ·Á¸é, ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¸ÕÀú Å¥ºê īŻ·Î±× ºä·ÎºÎÅÍ Å¥ºê ºäÀÇ À̸§À» ÆÇº°ÇØ¾ß ÇÕ´Ï´Ù.
¿¹¸¦ µé¾î, Sample ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÖ´Â Basic µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ Å¥ºê ºäÀÇ À̸§À» ¾Ë·Á¸é ´ÙÀ½ SQL¹®À» »ç¿ëÇÏ¿© µ¥ÀÌÅͺ£À̽º¸¦ Á¶È¸ÇÕ´Ï´Ù.
SELECT CUBEVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='Sample' AND CUBENAME='Basic'
ÀÌ Á¶È¸ÀÇ °á°ú´Â ´ÙÀ½°ú °°½À´Ï´Ù.
OLAPSERV.SAMPBASI_CUBEVIEW
Basic µ¥ÀÌÅͺ£À̽ºÀÇ Â÷¿ø À̸§ ¹× ÇØ´ç Â÷¿ø ºä À̸§À» ³ª¿ÇÏ·Á¸é ´ÙÀ½ ¸í·ÉÀ» »ç¿ëÇϽʽÿÀ.
SELECT DIMENSIONNAME.DIMENSIONVIEWNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW
Basic µ¥ÀÌÅͺ£À̽ºÀÇ Á¶¹Ð Â÷¿ø¿¡ ´ëÇÑ Â÷¿ø À̸§À» ³ª¿ÇÏ·Á¸é ´ÙÀ½ ¸í·ÉÀ» »ç¿ëÇϽʽÿÀ.
SELECT DIMENSIONNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONTYPE = 0
½ºÅ¸ ºä¿¡ ÀÖ´Â ¿¿¡ À̸§À» ÁöÁ¤Çϱâ À§ÇØ »ç¿ëµÇ´Â ¾ÞÄ¿ ÀÌ¿ÜÀÇ Â÷¿ø¿¡ ´ëÇÑ À̸§À» ÆÇº°ÇÏ·Á¸é ´ÙÀ½À» »ç¿ëÇϽʽÿÀ.
SELECT RELDIMENSIONNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONTYPE <> 2
Product Â÷¿ø¿¡ ´ëÇÑ °ü°èÇü ¼Ó¼º ºäÀÇ À̸§À» ¸®ÅÏÇÏ·Á¸é ´ÙÀ½À» »ç¿ëÇϽʽÿÀ.
SELECT RATVIEWNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONNAME='Product'
Â÷¿ø ºä À̸§Àº Å¥ºê ºäÀÇ DimensionViewName ¿·ÎºÎÅÍ È®º¸µË´Ï´Ù.
Ç¥ 16Àº Â÷¿ø ºäÀÇ ¿À» º¸¿©ÁÝ´Ï´Ù.
À̸§ | À¯Çü | Å©±â | ³»¿ë |
---|---|---|---|
MemberName | VarChar | 80 | ±¸¼º¿øÀÇ À̸§ |
RelMemberName | VarChar | 18 | ¾ÞÄ¿ Â÷¿ø Àü¿ë. DB2 OLAP Server ±¸¼º¿ø À̸§. ÀÌ À̸§Àº ¾ÞÄ¿ Â÷¿øÀÇ ±¸¼º¿ø¿¡
ÇØ´çµÇ´Â »ç½Ç ¹× ½ºÅ¸ ºäÀÇ ¿¿¡ À̸§À» ÁöÁ¤ÇÒ ¶§ »ç¿ëµË´Ï´Ù. À̰ÍÀº
ÀÌ °ü°èÇü Å¥ºêÀÇ ¾ÞÄ¿°¡ ¾Æ´Ñ Â÷¿ø À̸§°ú ´Ù¸¥ ¸ðµç ¾ÞÄ¿ Â÷¿ø À̸§¿¡
ºñ±³ÇÒ ¶§ °íÀ¯ÇÑ À̸§ÀÔ´Ï´Ù. À̰ÍÀº MemberNameÀÇ ¼öÁ¤µÈ ¹öÀüÀÔ´Ï´Ù.
DimensionName¿¡¼ ¹Ù²ã¾ß ÇÒ »çÇ×Àº ´ÙÀ½°ú °°½À´Ï´Ù.
|
RelMemberID | Integer | ¾øÀ½ | ÀÌ ±¸¼º¿øÀÇ DB2 OLAP Server ID. ÀÌ ID´Â Â÷¿ø Å×À̺íÀ» »ç½Ç Å×ÀÌºí¿¡ Á¶ÀÎÇÒ ¶§ »ç¿ëµË´Ï´Ù. |
ParentRelId | Integer | ¾øÀ½ | OLAP À±°û(outline)¿¡¼ ±¸¼º¿ø »óÀ§ÀÇ °ü°èÇü ID. °ªÀº ÃÖ»óÀ§ ·¹º§ ±¸¼º¿øÀÇ °æ¿ì ³Î(NULL)ÀÔ´Ï´Ù. |
LeftSiblingRelId | Integer | ¾øÀ½ | OLAP À±°û(outline)¿¡¼ ±¸¼º¿øÀÇ ¿ÞÂÊ ÇüÁ¦ÀÇ °ü°èÇü ID. ÀÌ °ªÀº ¿ÞÂÊ ÇüÁ¦°¡ ¾ø´Â ±¸¼º¿øÀÇ °æ¿ì ³Î(NULL)ÀÔ´Ï´Ù. |
Status | Integer | ¾øÀ½ | ÀÌ ±¸¼º¿øÀÇ »óÅ¿¡´Â ´ÙÀ½ °ªÀÇ Á¶ÇÕÀÌ Æ÷Ç﵃ ¼ö ÀÖ½À´Ï´Ù.
|
CalcEquation | Long VarChar(¿öÅ©½ºÅ×À̼Ç); VarChar(OS/390) | 32700(¿öÅ©½ºÅ×À̼Ç); 250(OS/390) | °è»êµÇ´Â ±¸¼º¿ø¿¡ ´ëÇÑ ±âº» °è»ê ¹æÁ¤½Ä. ±âº» °è»ê ¹æÁ¤½ÄÀº °ü°èÇü Å¥ºê¸¦ °è»êÇϱâ À§ÇØ »ç¿ëµÇ´Â °è»ê ½ºÅ©¸³Æ®¿¡ ´Ù¸¥ °è»êÀÌ ÁöÁ¤µÉ °æ¿ì¿¡, ±¸¼º¿øÀÇ °ªÀ» °è»êÇϱâ À§ÇØ »ç¿ëµÇ´Â µî½ÄÀÌ ¾Æ´Ò ¼öµµ ÀÖ´Ù´Â Á¡¿¡ À¯ÀÇÇϽʽÿÀ. |
UnarySymbol | Small Integer | ¾øÀ½ | ´ÜÇ× °è»ê ±âÈ£:
|
AccountsType | Integer | ¾øÀ½ | ÀÌ ¼Ó¼ºÀº Accounts Â÷¿ø¿¡ ´ëÇØ¼¸¸ »ç¿ëµË´Ï´Ù. ´ÙÀ½ °ªÀÇ Á¶ÇÕÀÌ
Æ÷Ç﵃ ¼ö ÀÖ½À´Ï´Ù.
|
NoCurrencyConv | Small Integer | ¾øÀ½ | ÅëÈ º¯È¯ ¼³Á¤:
|
CurrencyMemberName | VarChar | 80 | ÀÌ ±¸¼º¿ø°ú ¿¬°üµÇ´Â ÅëÈ Å¥ºê·ÎºÎÅÍÀÇ ±¸¼º¿ø À̸§. |
GenerationNumber | Integer | ¾øÀ½ | ÀÌ ±¸¼º¿ø¿¡ ´ëÇÑ ¼¼´ë ¹øÈ£. |
GenerationName | VarChar | 80 | ÀÌ ±¸¼º¿ø¿¡ ´ëÇÑ ¼¼´ë À̸§. |
LevelNumber | Integer |
| ÀÌ ±¸¼º¿ø¿¡ ´ëÇÑ ·¹º§ ¹øÈ£. |
LevelName | VarChar | 80 | ÀÌ ±¸¼º¿ø¿¡ ´ëÇÑ ·¹º§ À̸§. |
º°¸í Å×À̺í À̸§. À±°û(outline)¿¡ »ç¿ëµÈ °¢ OLAP º°¸í¿¡ ´ëÇØ ÇϳªÀÇ º°¸í ¿ÀÌ ÀÖ½À´Ï´Ù. | VarChar | 80 | ¿¬°üµÈ OLAP º°¸í Å×ÀÌºí¿¡¼ ÀÌ ±¸¼º¿øÀÇ º°¸í. ±¸¼º¿ø¿¡ ´ëÇØ º°¸íÀÌ Á¦°øµÇÁö ¾ÊÀ¸¸é, ÀÌ °ªÀº ³Î(NULL)ÀÔ´Ï´Ù. º°¸í ID ºä »ç¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. |
°ü°èÇü ¼Ó¼º ¿ À̸§. °¢ RatCol »ç¿ëÀÚ Á¤ÀÇ ¼Ó¼º¿¡ ´ëÇØ ÇϳªÀÇ °ü°èÇü ¼Ó¼º ¿ÀÌ ÀÖ½À´Ï´Ù. | °ü°èÇü ¼Ó¼º ¿ÀÌ ÀÛ¼ºµÉ ¶§ ÁöÁ¤µÇ´Â µ¥ÀÌÅÍ À¯Çü. | °ü°èÇü ¼Ó¼º ¿ÀÌ ÀÛ¼ºµÉ ¶§ ÁöÁ¤µÇ´Â Å©±â. | ÀÌ ±¸¼º¿ø¿¡ ´ëÇÑ °ü°èÇü ¼Ó¼ºÀÇ °ª. |
Â÷¿ø ºäÀÇ µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇÏ·Á¸é, ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¸ÕÀú Å¥ºê ºä¿¡¼ Â÷¿ø ºäÀÇ À̸§À» ÆÇº°ÇØ¾ß ÇÕ´Ï´Ù.
¿¹¸¦ µé¾î, Basic µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â Time Â÷¿ø¿¡ ´ëÇÑ Â÷¿ø ºäÀÇ À̸§À» ¾Ë·Á¸é ´ÙÀ½ SQL¹®À» »ç¿ëÇÏ¿© µ¥ÀÌÅͺ£À̽º¸¦ Á¶È¸ÇÕ´Ï´Ù.
SELECT DIMENSIONVIEWNAME FROM OLAPSERV.SAMPBASI_CUBEVIEW WHERE DIMENSIONNAME='Time'
Á¶È¸ °á°ú´Â OLAPSERV.SAMPBASID_TIMEÀÔ´Ï´Ù.
Time Â÷¿ø¿¡ ´ëÇÑ ±¸¼º¿ø À̸§À» ³ª¿ÇÏ·Á¸é ´ÙÀ½ ¸í·ÉÀ» »ç¿ëÇϽʽÿÀ.
SELECT MEMBERNAME FROM OLAPSERV.SAMPBASID_TIME
DB2 OLAP Server´Â ½ºÅ¸ ½ºÅ°¸¶¿¡ ´ëÇÑ »ç½Ç Å×À̺íÀÇ µÎ °¡Áö ºä¸¦ ÀÛ¼ºÇÏ¿© À¯Áöº¸¼öÇÕ´Ï´Ù.
»ç½Ç Å×ÀÌºí¿¡´Â ¼·Î ´Ù¸¥ ·¹º§ÀÇ ÇÕ°è °ªÀÌ ÀÖÀ¸¹Ç·Î, °¢ Â÷¿ø¿¡¼ ¼±ÅÃµÈ ±¸¼º¿ø ¼¼Æ®°¡, »ç¿ëÀÚ°¡ Áý°èÇÒ SQL ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇÒ °æ¿ì ÇÕ°è ·¹º§ÀÌ µ¿ÀÏÇÑÁö È®ÀÎÇØ¾ß ÇÕ´Ï´Ù. ±×·¸Áö ¾ÊÀ¸¸é, »ç¿ëÀÚÀÇ ÇÕ°è°¡ Á¤È®ÇÏÁö ¾ÊÀ» °ÍÀÔ´Ï´Ù. ÀÌ·¯ÇÑ ¿ä±¸»çÇ×À» ¸¸Á·½ÃŰ´Â ÇÑ °¡Áö ¹æ¹ýÀº Â÷¿ø Å×ÀÌºí¿¡ ÀÖ´Â ¼¼´ë ¹øÈ£³ª ·¹º§ ¹øÈ£ Çʵ忡 Á¦ÇÑÁ¶°ÇÀ» Æ÷ÇÔ½ÃŰ´Â °ÍÀÔ´Ï´Ù.
DB2 OLAP Server°¡ ÀÛ¼ºÇÏ´Â »ç½Ç Å×ÀÌºí¿¡´Â °¢ ¾ÞÄ¿ ÀÌ¿ÜÀÇ Â÷¿ø¸¶´Ù ÇϳªÀÇ ¿ÀÌ ÀÖ°í µ¥ÀÌÅ͸¦ ÀúÀåÇÏ´Â ¾ÞÄ¿ Â÷¿øÀÇ °¢ ±¸¼º¿ø¸¶´Ù ÇϳªÀÇ ¿ÀÌ ÀÖ½À´Ï´Ù. ±×¸² 9¿¡¼ »ç¿ëµÈ À±°û(outline)¿¡ ÇØ´çµÇ´Â »ç½Ç Å×ÀÌºí¿¡´Â ´ÙÀ½ ¿ÀÌ ÀÖ½À´Ï´Ù.
Â÷¿ø ¿Àº ¾ÞÄ¿ ÀÌ¿ÜÀÇ °¢ Â÷¿ø¿¡ ´ëÇÑ ±¸¼º¿øÀ» ÂüÁ¶ÇÏ´Â ±¸¼º¿ø ID¸¦ ÀúÀåÇÕ´Ï´Ù. ±¸¼º¿ø ID´Â Â÷¿ø ºä¸¦ »ç¿ëÇÏ¿© ±¸¼º¿ø À̸§¿¡ ¸ÊÇ뵃 ¼ö ÀÖ½À´Ï´Ù. ±¸¼º¿ø ¿Àº ½ÇÁ¦ µ¥ÀÌÅÍ °ªÀ» ÀúÀåÇÕ´Ï´Ù. ¾ÞÄ¿ Â÷¿ø ±¸¼º¿øÀº ¾ÞÄ¿ Â÷¿øÀÇ Â÷¿ø ºä¸¦ »ç¿ëÇÏ¿© »ç½Ç ºäÀÇ ¿¿¡ ¸ÊÇ뵃 ¼ö ÀÖ½À´Ï´Ù.
DB2 OLAP Server´Â »ç½Ç Å×À̺íÀÇ ¿¿¡ ´ëÇØ¼´Â ³»ºÎ À̸§À», ±×¸®°í ±¸¼º¿ø¿¡ ´ëÇØ¼´Â ³»ºÎ ID¸¦ »ç¿ëÇÕ´Ï´Ù. »ç½Ç ºä´Â ³»ºÎ ¿ À̸§À» Â÷¿ø°ú ±¸¼º¿ø À̸§À¸·Î ´ëüÇÏÁö¸¸, Â÷¿ø ¿ ±¸¼º¿ø ID¸¦ ±¸¼º¿ø À̸§¿¡ ¸ÊÇÎÇÏÁö´Â ¾Ê½À´Ï´Ù. ½ºÅ¸ ºä´Â ³»ºÎ ¿ À̸§À» Â÷¿ø°ú ±¸¼º¿ø À̸§À¸·Î ´ëüÇϰí, »ç½Ç Å×À̺íÀ» Â÷¿ø Å×ÀÌºí¿¡ Á¶ÀÎÇÏ¿© Â÷¿ø ¿ ±¸¼º¿ø ID¸¦ ±¸¼º¿ø À̸§¿¡ ¸ÊÇÎÇÕ´Ï´Ù.
¾ÞÄ¿ Â÷¿øÀ¸·Î¼ Á¶¹Ð Â÷¿øÀ» ÁöÁ¤ÇÒ ¼öµµ ÀÖÁö¸¸, SQL ÀÀ¿ëÇÁ·Î±×·¥À¸·ÎºÎÅÍ »ç½Ç ¶Ç´Â ½ºÅ¸ ºä¿¡ ¾×¼¼½ºÇÏ¿© Àӽà Á¶È¸¸¦ ½ÇÇàÇÒ °æ¿ì, ¾ÞÄ¿ Â÷¿øÀ¸·Î¼ ȸ°è Â÷¿øÀ» ÁöÁ¤ÇÒ °æ¿ì °¡Àå ÀÚ¿¬½º·¯¿î ¸ÊÇÎÀÌ È®º¸µË´Ï´Ù.
»ç½Ç ºä À̸§Àº Å¥ºê īŻ·Î±× ºäÀÇ FactViewName ¿·ÎºÎÅÍ È®º¸µË´Ï´Ù.
»ç½Ç ºä¿¡´Â µÎ°¡Áö À¯ÇüÀÇ ´Ù¾çÇÑ ¿ÀÌ ÀÖ½À´Ï´Ù.
Ç¥ 17Àº »ç½Ç ºä¿¡ ÀÖ´Â µÎ °¡Áö Á¾·ùÀÇ ¿¿¡ °üÇÑ
¼¼ºÎ»çÇ×À» º¸¿©ÁÝ´Ï´Ù.
»ç½Ç ºäÀÇ µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇÏ·Á¸é, ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¸ÕÀú Å¥ºê īŻ·Î±× ºä¿¡¼ »ç½Ç ºäÀÇ À̸§À» ÆÇº°ÇØ¾ß ÇÕ´Ï´Ù.
¿¹¸¦ µé¾î, Sample ÀÀ¿ëÇÁ·Î±×·¥ÀÇ Basic µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ »ç½Ç ºä À̸§À» ãÀ¸·Á¸é ´ÙÀ½ÀÇ SQL¹®À» »ç¿ëÇÕ´Ï´Ù.
SELECT FACTVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='Sample' AND CUBENAME='Basic'
ÀÌ Á¶È¸ÀÇ °á°ú´Â ´ÙÀ½°ú °°½À´Ï´Ù.
OLAPSERV.SAMPBASI_FACTVIEW
ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ±¸¼º¿ø¿¡ ´ëÇÑ RelMemberID °ªÀ» °è¼Ó ±â¾ïÇϸé Á÷Á¢ »ç½Ç ºä¸¦ Á¶È¸ÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, RelMemberID 4 (Q3) ½Ã°£¿¡, RelMemberId 2 (East) ½ÃÀå¿¡¼, RelMemberId 3 (100-20) Á¦Ç°ÀÇ µ¥ÀÌÅÍ °ªÀ» ¼±ÅÃÇÏ·Á¸é ´ÙÀ½À» »ç¿ëÇϽʽÿÀ.
SELECT PROFIT,SALES,COGS FROM OLAPSERV.SAMPBASI_FACTVIEW WHERE PRODUCT=3 AND MARKET=2 AND TIME=4
´õ ÀϹÝÀûÀ¸·Î´Â, »ç½Ç ºä¿¡ ´ëÇÑ Á¶È¸¿¡´Â Â÷¿ø ºä°¡ ÀÖ´Â Á¶ÀÎÀÌ Æ÷ÇԵ˴ϴÙ. ÀÌÀü Á¶È¸¿¡ »óÀÀÇÏ´Â Á¶ÀÎÀ» »ç¿ëÇÑ Á¶È¸´Â ´ÙÀ½°ú °°½À´Ï´Ù.
SELECT PROFIT,SALES,COGS FROM OLAPSERV.SAMPBASI_FACTVIEW, OLAPSERV.SAMPBASID_TIME, OLAPSERV.SAMPBASID_MARKET, OLAPSERV.SAMPBASID_PRODUCT, WHERE OLAPSERV.SAMPBASID_TIME.MEMBERNAME='Q3' AND OLAPSERV.SAMPBASID_PRODUCT.MEMBERNAME='100-20' AND OLAPSERV.SAMPBASID_MARKET.MEMBERNAME='East' AND OLAPSERV.SAMPBASI_FACTVIEW.TIME=OLAPSERV.SAMPBASID_TIME.RELMEMBERID AND OLAPSERV.SAMPBASI_FACTVIEW.PRODUCT=OLAPSERV.SAMPBASID_PRODUCT.RELMEMBERID AND OLAPSERV.SAMPBASI_FACTVIEW.MARKET=OLAPSERV.SAMPBASID_MARKET.RELMEMBERID
½ºÅ¸ ºä À̸§Àº Å¥ºê īŻ·Î±× ºäÀÇ StarViewName ¿¿¡¼ È®º¸ÇÒ ¼ö ÀÖ½À´Ï´Ù.
½ºÅ¸ ºä¿¡´Â µÎ°¡Áö À¯ÇüÀÇ ´Ù¾çÇÑ ¿ÀÌ ÀÖ½À´Ï´Ù.
Ç¥ 18Àº ½ºÅ¸ ºä¿¡ ÀÖ´Â µÎ °¡Áö Á¾·ùÀÇ ¿¿¡ °üÇÑ ¼¼ºÎ»çÇ×À» º¸¿©ÁÝ´Ï´Ù.
½ºÅ¸ ºäÀÇ µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇÏ·Á¸é, ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¸ÕÀú Å¥ºê īŻ·Î±× ºä¿¡¼ ½ºÅ¸ ºäÀÇ À̸§À» ÆÇº°ÇØ¾ß ÇÕ´Ï´Ù.
¿¹¸¦ µé¾î, Sample ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ Basic µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ »ç½Ç ºä À̸§À» ãÀ¸·Á¸é ´ÙÀ½ÀÇ SQL¹®À» »ç¿ëÇÕ´Ï´Ù.
SELECT STARVIEWNAME FROM OLAPSERV.CUBECATALOGVIEW WHERE APPNAME='SAMPLE' and CUBENAME='BASIC'
ÀÌ Á¶È¸ °á°ú´Â OLAPSERV.SAMPBASI_STARVIEWÀÔ´Ï´Ù.
1»çºÐ±â Áß¿¡ ÁߺΠÁö¿ª¿¡¼ Á¦Ç° 100-10¿¡ ´ëÇÑ µ¥ÀÌÅÍ °ªÀ» ¼±ÅÃÇÏ·Á¸é ´ÙÀ½À» ÀÔ·ÂÇϽʽÿÀ.
SELECT PROFIT,SALES,COGS FROM OLAPSERV.SAMPBASI_STARVIEW WHERE PRODUCT='100-10' AND MARKET='Central' AND TIME='Q1'
2»çºÐ±â Áß¿¡ ÁߺΠÁö¿ª¿¡¼ ¼ÕÇØ¸¦ º» ¸ðµç Á¦Ç°À» ¼±ÅÃÇÏ·Á¸é ´ÙÀ½À» »ç¿ëÇϽʽÿÀ.
SELECT PRODUCT,PROFIT,SALES,COGS FROM OLAPSERV.SAMPBASI_STARVIEW WHERE MARKET='Central' AND TIME='Q2' AND PROFIT < 0
½ºÅ¸ ºäÀÇ ¸ðµç ±¸¼º¿øÀÌ °°Àº °èÃþ ±¸Á¶¿¡ ÀÖ´Â °ÍÀº ¾Æ´Ï¹Ç·Î, SQL¹®À» »ç¿ëÇÏ¿© ÇÕ°è ¿¬»êÀ» ¼öÇàÇÒ ¶§¿¡ ÁÖÀǸ¦ ±â¿ï¿© Á¶È¸¸¦ ±¸¼ºÇØ¾ß ÇÕ´Ï´Ù. Â÷¿ø¿¡¼ ¼±ÅÃÇÑ ±¸¼º¿øÀº ÀÌÁß Çհ踦 ÇÇÇϱâ À§ÇØ °°Àº ·¹º§¿¡ ÀÖ¾î¾ß ÇÕ´Ï´Ù.
¿¹¸¦ µé¾î, ´ÙÀ½ÀÇ SQL¹®Àº ½ºÅ¸ ºä¿¡¼ ´Ù¸¥ ·¹º§·ÎºÎÅÍ ¼±ÅÃµÈ ±¸¼º¿øÀ» º¸¿©ÁÝ´Ï´Ù. µÎ ·¹º§ÀÇ ÇÕ°è°¡ °è»êµÇ¹Ç·Î ÀϺΠÆÇ¸Å¾×ÀÌ µÎ ¹ø °è»êµÉ °ÍÀÔ´Ï´Ù.
SELECT SUM(PROFIT) FROM OLAPSERV.SAMPBASI_STARVIEW WHERE MARKET IN ('Central','Illinois') AND PRODUCT='100' AND TIME IN ('Q1','1996')
Illinois°¡ ÁߺΠÁö¿ªÀÇ ºÎºÐÀ̰í Q1ÀÌ 1996³âÀÇ ºÎºÐÀ̶ó°í °¡Á¤ÇÒ °æ¿ì, ÀÌ Á¶È¸¸¦ ¼öÇàÇϸé ÁÖ ¹× Áö¿ª ·¹º§°ú ºÐ±â ¹× ¿¬µµ ·¹º§ µÑ´Ù¿¡¼ PROFIT ¼öÄ¡µéÀÌ ÇÕÇØÁý´Ï´Ù. ÁߺΠÁö¿ª¿¡´Â ÀÌ¹Ì Illinois µ¥ÀÌÅͰ¡ Æ÷ÇԵǾî ÀÖÀ¸¹Ç·Î, Illinois ¹× Q1 µ¥ÀÌÅͰ¡ ÇÕ°è¿¡¼ µÎ ¹ø °è»êµË´Ï´Ù. ¼·Î ´Ù¸¥ µÎ ºÐ±â µ¿¾È ÁߺΠÁö¿ª¿¡¼ µÎ ÁÖ¿¡ ´ëÇÑ ÆÇ¸Å¾×ÀÇ ÇÕÀ» ³»µµ·Ï SQLÀ» Á¤Á¤ÇÏ·Á¸é, ´ÙÀ½ ¿¹Á¦¸¦ »ç¿ëÇÏ¸é µË´Ï´Ù.
SELECT SUM(PROFIT) FROM OLAPSERV.SAMPBASI_STARVIEW WHERE MARKET IN ('Indiana','Illinois') AND PRODUCT='100' AND TIME IN ('Q1','Q2')