[SQL Server2008]MDX²éѯÓï¾ä
ÔÚEssbaseÖÐʹÓÃMDXÖ»ÄÜÓÃÓÚ²éѯ£¬Éв»ÄÜÀ´²Ù×÷Cube£¨MS SSASÖпÉÒÔʹÓÃMDXÀ´²Ù×÷CubeµÄ£©£¬ËùÒÔÕâÀïֻѧϰMDXµÄSelectÓï¾ä¡£
ÔÚѧϰµÄ¹ý³ÌÖУ¬ÎÒ¾õµÃ×îÓÐÄѶȵĵط½ÓÐÁ½¸ö¡£
Ò»ÊÇ£¬¶ÔOLAP¶àάµÄÀí½â¡£ÈçͬѧϰSQLÒ»Ñù£¬SQL×÷ÓõĶÔÏóÊÇ±í£¬±íµÄ½á¹¹¶¼ÊǶþάµÄ£¬±êʶÐÐÁм´¿É£»µ«ÊÇMDX×÷ÓõĶÔÏóÊǶàάÊý¾Ý¿â£¬ÄÇô¾ÍµÃÀí½â¶àάÊý¾Ý¿âµÄά¶È£¬²ã¼¶£¬³ÉÔ±£¬ÒÔ¼°ÈçºÎÔÚ¶àάÊý¾Ý¿âÖж¨Î»²»Í¬²ã¼¶µÄ³ÉÔ±¡£
¶þÊÇ£¬Àí½âMDX²éѯÓï¾äµÄ½á¹¹¼°ÊõÓïµÄº¬Ò塣ѧϰSQLʱ£¬ÎÒÃdz£Ìáµ½±í£¬×ֶΣ¬ÄÇôMDXÖÐÄØ£¬Ïë±ØÕâЩÊõÓï±ØÐëÀí½â£¬ÀýÈ磺Cube£¬Slice£¬Axis£¬Set£¬Tuple£¬Members
ÎÒµÄѧϰ¾ÀúÊÇÕâÑùµÄ£¬Ê×ÏÈÁ˽âMDXÓï¾äµÄÌص㼰¹¦Óá£
“MDXÈ«³ÆÊÇMutil-Dimensional Expressions£¬¼´¶àΪ±í´ïʽ¡£MDXÊÇÒ»ÖÖºÍSQLÀàËƵÄÓïÑÔ£¬ËüÒ²¿ÉÒÔÓÃÓÚ²éѯ¡¢¼ÆËãºÍ¶¨ÒåһЩԪÊý¾Ý¡£Ö»²»¹ýSQLÊÇ»ùÓÚOLTPµÄ£¬¶øMDXÊÇ»ùÓÚOLAPµÄ£¬Ò²¾ÍÊÇ˵£¬MDXÊǶԶàάÊý¾Ý½øÐвéѯµÄ¡£ºÍÆäËüµÄOLAPÓïÑÔ²»Í¬£¬MDX²¢²»ÍêÈ«ÊÇÒ»ÖÖ±¨¸æÐÎʽÓïÑÔ¡£µ«ÓÃMDX²éѯ³öÀ´µÄ½á¹ûÈÔÈ»¿ÉÒÔÔÚ¿Í»§¶Ë³ÌÐòÖÐÒÔ±í¸ñÐÎʽ½øÐÐÕ¹ÏÖ¡£MDXºÍSQLÒ»Ñù£¬Ò²Óкܶ಻ͬ¹¦ÄܵÄAPIÀ´Ö§³ÖMDX£¬×î³£Óõ½µÄ¾ÍÊÇXML API¡£”
ÉÏÃæµÄÒ»¶Î»°ÊÇ´ÓÍøÉÏժ¼À´µÄ£¬¸øMDX×öÁËÒ»¸ö¶¨Òå¡£
½ÓÏÂÀ´Ñ§Ï°MDX²éѯÓï¾äµÄ½á¹¹¼°×é³É¡£
[<with_section>]
SELECT [<axis_specification>
[, <axis_specification>...]]
[from [<cube_specification>]]
[WHERE [<slicer_specification>]]
Õâ¸ö½á¹¹¾ÍÊÇMDX²éѯÓï¾äµÄͨÓýṹ£¬´ÓÖÐÒªÀí½âÄÄЩÊǹؼü×ÖÒÔ¼°¹Ø¼ü×ÖµÄ˳Ðò£¬WITH section¡¢Axis¡¢Cube¡¢SlicerµÄλÖá£ÕâЩ¶¼ÊÇMDXµÄÓï·¨¹æ¶¨£¬ËùÒÔÒªÀμǸ÷×ÔµÄλÖá£
È»ºóѧϰµÄÊÇÒÔÉÏMDXÓï¾ä½á¹¹Öи÷¸öλÖõĺ¬Òå¡£
<with_section>µÄλÖÃÓÃÓÚÉ趨×Ô¶¨ÒåµÄ¼ÆËã¡£ÓÐÁ½ÖÖÔËÓã¬WITH SET…,WITH MEMBER…£¬ÔÚ¸´ÔÓµÄMDX²éѯÓï¾äÖо³£±»Óõ½¡£
<axis_specification>µÄλÖÃÓÃÓÚÉ趨Öá¡£Õâ¸öÖáÊÇ·´Ó³ÔÚMDXÓï¾äµÄÊä³ö±¨±íÉϵġ£³£ÓõľÍÊÇÁС¢ÐС¢Ò³£¬ÈçÏÂͼËùʾ£º
<cube_specification>λÖÃÓÃÓÚÖ¸¶¨²éѯÓõÄCube£¬Ö»ÄÜÖ¸¶¨Ò»¸ö¡£
<slicer_specification>λÖÃÓÃÓÚÖ¸¶¨ÏÞÖÆÌõ¼þ£¬¿ÉÒÔÊǶà¸öÏÞÖÆÌõ¼þ£¬·´Ó³ÔÚCubeÖгÆΪÇÐƬ¡£
&n
Ïà¹ØÎĵµ£º
×öÒ»¸öϵͳµÄºǫ́£¬»ù±¾É϶¼ÉÙ²»ÁËÔöɾ¸Ä²é£¬×÷Ϊһ¸öÐÂÊÖÈëÃÅ£¬ÎÒÃDZØÐëÒªÕÆÎÕSQLËÄÌõ×î»ù±¾µÄÊý¾Ý²Ù×÷Óï¾ä£ºInsert£¬Select£¬UpdateºÍDelete£¡ ÏÂÃæ¶ÔÕâËĸöÓï¾ä½øÐÐÏêϸµÄÆÊÎö£º
¡¡¡¡ ÊìÁ·ÕÆÎÕSQLÊÇÊý¾Ý¿âÓû§µÄ±¦¹ó²Æ¸»¡£ÔÚ±¾ÎÄÖУ¬ÎÒÃǽ«Òýµ¼ÄãÕÆÎÕËÄÌõ×î»ù±¾µÄÊý¾Ý²Ù×÷Óï¾ä—SQLµÄºËÐŦÄÜ—À´ÒÀ´Î½éÉܱȽ ......
ÇÉÓÃSQLµÄÈ«¾ÖÁÙʱ±í·ÀÖ¹Óû§Öظ´µÇ¼
ÎÄÕÂÀ´×Ô£ºhttp://www.cnblogs.com/lindayyh/archive/2010/04/05/1704763.html
ÔÚÎÒÃÇ¿ª·¢ÉÌÎñÈí¼þµÄʱºò£¬³£³£»áÓöµ½ÕâÑùµÄÒ»¸öÎÊÌ⣺ÔõÑù·ÀÖ¹Óû§Öظ´µÇ¼ÎÒÃǵÄϵͳ£¿ÌرðÊǶÔÓÚÒøÐлòÊDzÆÎñ²¿ÃÅ£¬¸üÊÇÒªÏÞÖÆÓû§ÒÔÆ乤ºÅÉí·Ý¶à´ÎµÇÈë¡£
¿ÉÄÜ»áÓÐÈË˵ÔÚÓû§ÐÅÏ¢±íÖмÓÒ»×Ö¶ÎÅÐ ......
/*********************************************************/
Ŀ¼Çåµ¥CONTEXT LIST
/*********************************************************/
1.Êý¾Ý¿âDataBase
1.1Êý¾Ý¿â½¨Á¢/ɾ³ýcreate/drop database
1.2Êý¾Ý¿â±¸·ÝÓë»Ö¸´backup/restore database
/***************************************************** ......
SELECT DISTINCT '['+user_name(b.uid)+'].['+b.name+']' AS ¶ÔÏóÃû,b.type AS ÀàÐÍ
from sysdepends a,sysobjects b
WHERE b.id=a.depid
AND a.id=OBJECT_ID('¹ý³ÌÃû');
EXEC SP_DEPENDS '¹ý³ÌÃû';
......