Excel 2013 және PowerPivot қондырмасы арқылы еске сақтағыш тиімді деректер үлгісін жасау

Excel 2013 бағдарламасында миллиондаған қатарлары бар деректер үлгісін жасап, содан соң осы үлгілерге қарсы қуатты деректер талдамасын орындай аласыз. Деректер үлгісін жиынтық кестелерінің, диаграммалардың және Power View көріністерінің кез келген санына бір жұмыс кітапшасында қолдау көрсету үшін Power Pivot қондырмасымен не онсыз жасауға болады.

Excel бағдарламасында үлкен деректер үлгісін оңай жасай алатыныңызды ескере отырып, оны жасамаудың бірнеше себептері бар. Біріншіден, бірнеше кестелер мен бағандарды қамтитын үлкен үлгілер көптеген талдаулар үшін асыра сілтеушілік және Өрістер тізіміне өтуді қиындатады. Екіншіден, үлкен үлгілер бірдей жүйе ресурстарын ортақ пайдаланатын басқа бағдарламалар мен есептерге кері әсер ете отырып, пайдалы жадты пайдаланады. Соңында, Office 365 жиынтығында, SharePoint Online және Excel Web App бағдарламаларында Excel файлының өлшемін 10 Мб дейін шектейді. Миллиондаған қатарларды қамтитын жұмыс кітапшасының деректер үлгісі үшін 10 Мб лимитке жылдам кіре аласыз. Деректер үлгісі спецификациясы және шектеулері бөлімін қараңыз.

Осы мақалада оңай жұмыс істеуге болатын және көп жад пайдаланбайтын тығыз құрастырылған үлгіні жасау әдісін үйренесіз. Тиісті үлгі дизайнын жасауды үйренуге уақыт жұмсау алдағы уақытта пайдаланытын және жасалатын үлгіні Excel 2013, Office 365 SharePoint Online, Office Web Apps Server және SharePoint 2013 бағдарламаларының бірінде көру кезінде көмектеседі.

Workbook Size Optimizer бағдарламасы іске қосуды да қараңыз. Ол Excel жұмыс кітабын талдап, оны тиісінше қысады. Workbook Size Optimizer бағдарламасын жүктеп алыңыз.

Осы мақалада

Сығу деңгейлері және жад ішіндегі талдау механизмі

Қолданысы төмен жадқа арналған жоқ бағанды ештеңемен салыстыруға келмейді

Қамтылуы қажет емес бағандардың екі үлгісі

Қажет емес бағандарды есептен шығару жолы

Қажетті жолдарды ғана сүзу жолы ше?

Баған қажет болса, оның көлемін әлі де азайта аламыз ба?

Уақыт бағандарын өзгерту

SQL сұрауын өзгерту

Бағандардың орнына DAX есептелген өлшемдерін пайдалану

Қай 2 бағанды сақтау қажет?

Қорытынды

Қосымша сілтемелер

Сығу деңгейлері және жад ішіндегі талдау механизмі

Excel деректер үлгісіндегі жад ішіндегі талдау механизмін деректерді жадта сақтау үшін пайдаланыңыз. Механизм нәтижелер жиынтығын өзінің бастапқы өлшемінің бөлігіне дейін кішірейте отырып, жад талаптарын азайту үшін қуатты сығу техникаларын жүзеге асырады.

Орта есеппен алғанда, деректер үлгісінің бастапқы нүктесіндегі бірдей деректерге қарағанда 7 - 10 рет кішірек деректер үлгісін күте аласыз. Мысалы, егер 7 Мб деректі SQL Server дерекқорынан импорттасаңыз, Excel бағдарламасындағы деректер үлгісі оңай 1 Мб немесе одан аз болады. Әдетте, алынған сығу деңгейі біріншіден әр бағандағы бірегей мәндер санына байланысты. Мәндер қаншалықты бірегей болған сайын оларды сақтау үшін соншалықты жад қажет.

Біз неліктен сығу және бірегей мәндер туралы айтып жатырмыз? Өйткені жад қолданысын төмендететін тиімді үлгіні құру сығуды жоғарылатуға қатысты және оны жасаудың ең оңай жолы қажет емес бағандардың барлығын алып тастау, әсіресе ол бағандар бірегей мәндердің біршама санын қамтитын болса.

Ескерту :  Жеке бағандарға арналған сақтау талаптарындағы өзгешеліктер үлкен болуы мүмкін. Кей жағдайларда бірегей мәндері көп бір бағанға қарағанда аздаған бірегей мәндері бар бірнеше бағандардың болғаны жақсы. Datetime оңтайландыруындағы сеанс осы техниканы толық қамтиды.

Беттің жоғарғы жағы

Қолданысы төмен жадқа арналған жоқ бағанды ештеңемен салыстыруға келмейді

Ең жадқа тиімді баған - ешқашан бірінші орынға импорттамаған баған. Егер тиімді үлгі жасағыңыз келсе, әр бағанға қараңыз және өзіңізден орындағыңыз келетін талдауға көмектесетініне сенімді болыңыз. Егер олай болмаса немесе сенімді болмасаңыз, қалдырып кетіңіз. Сондай-ақ, қажетінше жаңа бағандарды қоса аласыз.

Қамтылуы қажет емес бағандардың екі үлгісі

Бірінші мысал деректер қоймасынан алынған деректерге қатысты. Деректер қоймасында қоймадағы деректерді жүктеп, жаңартатын ETL процестерінің үлгілерін табу жиі кездеседі. “Күнді жасау”, “күнді жаңарту” және “ETL іске қосу” сияқты бағандары күн жүктелгенде жасалады. Бұл бағандардың ешқайсысы үлгіде қажет емес және деректерді импорттағанда белгілеуді алып тастау тиіс.

Екінші мысал, мәлімет кестесін импорттағанда алғашқы кілт бағанын есепке алмауды қамтиды.

Көптеген кестелер, соның ішінде, мәлімет кестелерін қосқанда бастапқы кілттер бар. Көптеген кестелер үшін мысалы, тұтынушы, қызметкер немесе сату деректерін қамтитын, үлгіде қатынастар жасау үшін кестенің бастапқы кілтін қажет етуіңіз мүмкін.

Мәлімет кестелері өзгеше. Мәлімет кестесінде бастапқы кілт әр қатарды жеке анықтау үшін пайдаланылады. Қалпына келтіру мақсаттары үшін қажет болғанда талдау үшін тек сол бағандардың пайдаланылуын қалайтын деректер үлгісінде немесе кесте қатынастарын орнату үшін пайдасы аз. Сол себептен, мәлімет кестесінен импорттағанда оның бастапқы кілтін қамтымаңыз. Мәлімет кестелеріндегі бастапқы кілттер үлгіде едәуір көп орынды алады, қатынастарды жасау үшін пайдаланылмайды.

Ескерту :  Деректер қоймасында және көп өлшемді дерекқорларда, көбі сандық деректерден тұратын үлкен кестелер «мәлімет кестелері» ретінде жиі қарастырылады. Әдетте, мәлімет кестелері ұйым бірліктеріне, өнімдерге, нарық сегменттеріне, географиялық аймақтарға және т.б. топтастырылған және құрылған сатылымдар мен шығын деректері секілді бизнес өнімділігі немесе транзакция деректерін қамтиды. Бизнес деректері немесе басқа кестелерде сақталған айқас сілтеме деректеріне қолданылатын деректері бар мәлімет кестесіндегі барлық бағандар деректерін талдауын қолдау үшін үлгіде де болуы тиіс. Шығарғыңыз келмеген баған басқа жерде жоқ, тек мәлімет кестесінде бар бірыңғай мәндерден тұратын мәлімет кестесінің негізгі бағаны болып табылады. Себебі, мәлімет кестелері өте үлкен, үлгінің нәтижесіне әсер ететін кейбір үлкендері осы кестеден қатарлардың немесе бағандардың есебінен алынады.

Қажет емес бағандарды есептен шығару жолы

Тиімді үлгілер жұмыс кітабына қажетті, нақты бағандардан тұрады. Үлгідегі бағандарды басқару қажет болса, деректерді импоттау үшін Excel бағдарламасындағы «Деректерді импорттау» диалогтық терезесіне қарағанда Power Pivotқондырмасындағы «Кестені импорттау» шеберін пайдалануыңыз қажет.

«Кестені импорттау» шеберін іске қосқан кезде импортталатын кестелерді таңдайсыз.

PowerPivot қондырмасындағы Кестені импортау шебері

Әр кесте үшін «Алдын ала қарап алу» & «Сүзу» түймешігін басуға және өте қажет кестелердің бөлігін таңдай аласыз. Алдымен, барлық бағандардың белгісін алып, талдауға қажеттілерін анықтағаннан кейін қалаған бағандарға белгі қоюды жалғастыруға кеңес береміз.

Кестені импорттау шеберінің Алдын ала қарап алу аумағы

Беттің жоғарғы жағы

Қажетті жолдарды ғана сүзу жолы ше?

Корпоративтік дерекқорлар мен деректер қоймасындағы көптеген кестелер ұзақ уақыт бойы жиналған тарихи деректерді қамтиды. Сонымен қатар, арнайы талдауларды қажет етпейтін бизнес салаларындағы деректерден тұратын кестелерді таба аласыз.

«Кестені импорттау» шеберін пайдалана отырып, тарихи немесе қатысты емес деректерді сүзіп, алып тастай аласыз және үлгінің орнын үнемдей аласыз. Мына суреттегі деректер сүзгісі қажет емес тарихи деректерді алып тастай отырып, ағымдағы жылдың деректері бар қатарларды ғана алуға пайдаланылады.

Кестені импорттау шеберінің сүзу аумағы

Беттің жоғарғы жағы

Баған қажет болса, оның көлемін әлі де азайта аламыз ба?

Бағанды қысуға қолданылатын бірнеше қосымша әдістер бар. Қысуға әсер ететін бағанның сипаттамасы ғана бірыңғай мәндер саны болып табылатындығын ескеріңіз. Осы бөлімде бірыңғай мәндердің санын азайту үшін кейбір бағандардың өзгертілу жолын үйренесіз.

Уақыт бағандарын өзгерту

Көптеген жағдайда уақыт бағандары бірнеше орынды алады. Осы деректер түріне сақтау шарттарын азайтудың бірнеше жолы бар. Әдістер бағанның қолданылуына және SQL сұрауларын құру деңгейіне байланысты өзгереді.

Уақыт бағандары уақыт бөлігін және уақытты қамтиды. Өзіңізден қажет бағанды сұраған кезде «Уақыт» бағанына да осы сұрақты бірнеше рет қойыңыз.

  • Уақыт бөлігі қажет пе?

  • Уақыттың сағат, минут, секунд, миллисекунд бөліктері қажет пе?

  • «Уақыт» бағандары арасындағы айырмашылықты есептеу немесе деректерді жылы, айы, тоқсаны және т.б. бойынша топтастыру үшін бірнеше «Уақыт» бағандары қажет пе?

«Уақыт» бағанының опциясын анықтайтын осы сұрақтардың әрқайсысына қалай жауап бересіз?

Барлық осы шешімдер SQL сұрауын өзгеруді қажет етеді. Сұрауды өзгертуді жеңілдету үшін әр кестедегі кемінде бір бағанды сүзіп, алып тастауыңыз керек. Бағанды алып тастау арқылы қысқартылған (ТАҢДАУ *) пішімінен бастап, өзгерту қиын толықтай жарамды баған атауы бар ТАҢДАУ мәлімдемесіне дейін сұрау құрылымын өзгертесіз.

Жасаған сұрауларыңызды қарап шығайық. «Кесте сипаттары» диалогтық терезесінен Сұрау өңдегішіне ауыса аласыз және әр кестеге арналған ағымдағы SQL сұрауын көре аласыз.

Кесте сипаттары пәрменін көрсететін PowerPivot терезесіндегі таспа

Кесте сипаттары терезеснде Сұрауды өңдегіш пәрменін таңдаңыз.

Кесте сипаттары диалогтық терезесінен Сұрау өңдегіші пәрменін ашу

Сұрау өңдегіші кестені толтыруға қолданылатын SQL сұрауын көрсетеді. Импорттау кезінде қандайда бір бағанды сүзіп алып тастасаңыз, сұрау толықтай толытырылған баған атауларын қамтиды:

Деректерді алуға қолданылатын SQL сұрауы

Кейбір бағанды белгілемей немесе сүзгіні қолданбай кестені толықтай импорттасаңыз, сұрауды өзгерту қиын болатын «* таңдау» ретінде көресіз:

Әдепкі, қысқа синтаксисті пайдалану арқылы SQL сұрауы

SQL сұрауын өзгерту

Енді сұрауды іздеу жолын білесіз, үлгінің көлемін кішірейту үшін оны өзгерте аласыз.

  1. Валюта немесе ондық деректері бар бағандарға, үтірден кейін таңба қажет болмаса, үтірден кейінгі таңбаларды алып тастау үшін мына синтаксисті пайдаланыңыз:

    “SELECT ROUND([Ондық_баған_атауы],0)… .”

    Центтің бөлігі емес, цент қажет болса, 0 мәнін 2 мәнімен ауыстырыңыз. Теріс сандарды пайдалансаңыз, оларды бірліктерге, ондықтарға жүздіктерге және т.б. дөңделектей аласыз.

  2. dbo.Bigtable.[Уақыт] деп аталатын «Уақыт» бағаны болса және «Уақыт бөлігі» қажет болмаса, уақытты алып тастау үшін синтаксисті пайдаланыңыз:

    “SELECT CAST (dbo.Bigtable.[Уақыт] күн ретінде) AS [Уақыт]) “

  3. dbo.Bigtable.[Уақыт] деп аталатын «Уақыт» бағаны болса, «Күн» және «Уақыт бөліктері де қажет болса, жалғыз «Уақыт» бағанының орнына SQL сұрауындағы бірнеше бағандарды пайдаланыңыз:

    “SELECT CAST (dbo.Bigtable.[Уақыт] күн ретінде ) AS [Уақыт],

    datepart(hh, dbo.Bigtable.[Уақыт]) as [Сағат],

    datepart(mi, dbo.Bigtable.[Уақыт]) as [Минут],

    datepart(ss, dbo.Bigtable.[Уақыт]) as [Секунд],

    datepart(ms, dbo.Bigtable.[Уақыт]) as [Миллисекунд]”

    Әр бөлікті жеке бағанда сақтау үшін бірнеше бағандарды пайдаланыңыз.

  4. Сағат және минут қажет болса және екеуін бір уақыт бағаны ретінде пайдалануды қаласаңыз, синтаксисті пайдаланыңыз:

    Timefromparts(datepart(hh, dbo.Bigtable.Уақыт]), datepart(mm, dbo.Bigtable.[Уақыт])) as [Минут]

  5. [Басталу уақыты] және [Аяқталу уақыты] секілді екі уақыт бағаны болса және [Ұзақтық] деп аталатын баған ретінде олардың арасындағы уақыт айырмашылығы секудпен қажет болса, екі бағанды да тізімнен жойып, мынаны қосыңыз:

    “datediff(ss,[Басталу уақыты],[Аяқталу уақыты] [Ұзақтық] ретінде”

    сс орнына мс құпия сөзін пайдалансаңыз, ұзақтықты миллисекундпен аласыз

Беттің жоғарғы жағы

Бағандардың орнына DAX есептелген өлшемдерін пайдалану

DAX өрнегінің тілімен бұрын жұмыс істеп көрсеңіз, есептелген бағандар есептелген өлшемдер үлгіде бір рет анықталған кезде үлгідегі кейбір басқа бағанның негізінде жаңа бағандарды алу үшін қолданылатындығын, бірақ жиынтық кестеде немесе басқа есепте қолданылған кезде бағаланатындығын білесіз.

Бір жадты үнемдеу әдісі - тұрақты немесе есептелген бағандарды есептелген өлшемдермен ауыстыру. Классикалық мысал - Бірлік құны, Саны және Жалпы сомасы. Осы үшеуінің барлығы болса, осының екеуін сақтап, DAX өрнегін пайдалана отырып, үшіншісін есептеу арқылы орынды үнемдей аласыз.

Қай 2 бағанды сақтау қажет?

Жоғарыда берілген мысалда, Саны және Бірлік құны бағандарын сақтаңыз. Осы екеуінің Жалпы сомасы бағанына қарағанда мәні азырақ. Жалпы сомасын есептеу үшін мына секілді есептелген өрнекті қосыңыз:

“Жалпы сатылымдар:=sumx(‘Сатылым кестесі’,’Сатылым кестесі’[Бірлік құны]*’Сатылым кестесі’[Саны])”

Екеуіндегі тұрақты бағандар секілді есептелген бағандар үлгіде орын алады. Есептелген өлшемдер тез есептелетіндіктен, орын алмайды.

Беттің жоғарғы жағы

Қорытынды

Осы мақалада жадты тиімдірек пайдаланатын үлгіні құруға көмектесетін бірнеше әдістер туралы айтылды. Файл көлемі мен деректер үлгісінің жад шарттарын азайтудың жолы - бағандар мен қатарлардың жалпы санын және әр бағандағы бірыңғай мәндердің санын азайту. Мына жерде кейбір әдістер бар:

  • Бағандарды жою - орынды үнемдеудің ең жақсы жолы. Өте қажет бағанды анықтаңыз.

  • Кейде бағандарды жойып, оларды кестеде есептелген өлшемдермен ауыстыра аласыз.

  • Кестедегі барлық жолдар қажет болмауы мүмкі. «Кестені импорттау» шеберінде жолдарды сүзіп алып тастай аласыз.

  • Жалпы бір бағанды бірнеше бөліктерге бөлу - бағандағы бірыңғай мәндердің санын азайтудың тиімді жолы. Бөліктердің әрқайсысы бірыңғай мәндердің аз ғанасы болатындықтан, топтастырылған қорытындысы бастапқы біріктірілген бағаннан азырақ болады.

  • Көп жағдайда, жеке бөліктерді есептерде шектегіштер ретінде пайдалануы қажет. Сәйкес болған жағдайда, «Сағат», «Минут» және «Секунд» секілді бөліктерден иерархиялар жасай аласыз.

  • Көптеген жағдайда бағандарда қажетіңізге байланысты көбірек мәлімет болады. Мысалы, баған оңдықтарды сақтайды делік, бірақ барлық ондықтарды жасыру үшін пішімдеуді қолдандыңыз. Дөңгелектеу - сандық бағанның көлемін азайту үшін өте тиімді.

Жұмыс кітабының көлемін азайтуға болатын әрекеттерді орындағаннан кейін Workbook Size Optimizer бағдарламасын іске қосып көріңіз.Workbook Size Optimizer бағдарламасын жүктеп алыңыз.

Беттің жоғарғы жағы

Қосымша сілтемелер

Деректер үлгісі спецификациясы және шектеулері

Workbook Size Optimizer жүктеу

Power Pivot: Excel бағдарламасындағы қуатты деректерді талдау және деректерді үлгілеу

Дағдыларды жетілдіру
Оқыту курсымен танысыңыз
Жаңа мүмкіндіктерге бірінші болып қол жеткізу
Office Insider бағдарламасына қосылу

Осы ақпарат пайдалы болды ма?

Пікіріңіз үшін рақмет!

Пікіріңізге рақмет! Сізді Office қолдау көрсету қызметіндегі агенттердің бірімен байланыстырған жөн болуы мүмкін.

×