1C अकाउंटिंग सिस्टम का उपयोग करके कंपनी में MS SQL Server 2008 R2 का उपयोग करके बिजनेस इंटेलिजेंस

इस लेख में मैं 1C प्लेटफॉर्म पर आधारित OLTP अकाउंटिंग सिस्टम का उपयोग करके एक संगठन में MS SQL Server 2008 R2 का उपयोग करके एक विश्लेषणात्मक रिपोर्टिंग प्रणाली के निर्माण के मुख्य चरणों का वर्णन करना चाहूंगा। लेख बिजनेस इंटेलिजेंस समाधान के निर्माण में मेरे पहले अनुभव का वर्णन करता है।

सामान्य इनपुट

जिस कंपनी के लिए मैं काम करता हूं वह थोक व्यापार में लगी हुई है और इसमें लगभग 30 कार्यालय रूस के क्षेत्रों में स्थित हैं। प्रत्येक कार्यालय में एक सूचना डेटाबेस 1 सी होता है, जिसमें बिक्री डेटा दर्ज किया जाता है। संगठन दो प्रकार के 1C डेटाबेस कॉन्फ़िगरेशन का उपयोग करता है । एक कॉन्फ़िगरेशन का उपयोग मॉस्को में केंद्रीय कार्यालय में किया जाता है, दूसरा शाखाओं में (रूस के क्षेत्रों में)। DBC के रूप में, 1C सिस्टम का संचालन प्रदान करते हुए, Microsoft SQL Server 2008 R2 (SP2) स्टैंडर्ड एडिशन (64-बिट) का उपयोग किया जाता है। एक भी सामान्य विनियामक संदर्भ जानकारी (NSI) नहीं है। "उत्पाद" निर्देशिका और कुछ अन्य निर्देशिकाएं जो उत्पादों और समकक्षों के वर्गीकरण हैं, एक कोड या अन्य पहचानकर्ता द्वारा सिंक्रनाइज़ किए जाते हैं जो 1 सी सिस्टम में संग्रहीत होते हैं। संगठन में उपयोग की जाने वाली मुख्य रिपोर्टों में से एक बिक्री रिपोर्ट है। मौजूदा बिक्री रिपोर्ट आपको केवल उस सिस्टम से डेटा पुनर्प्राप्त करने की अनुमति देती है जिसमें यह उत्पन्न होता है। उत्पन्न रिपोर्ट एमएस एक्सेल में अपलोड की जाती हैं, जहां उन्हें आगे संसाधित किया जाता है। कंपनी की वृद्धि और नए कार्यालयों के उद्भव के संबंध में, प्रबंधन ने आईटी विभाग को एक समेकित रिपोर्ट विकसित करने का काम दिया है, जो आपको संगठन के सभी कार्यालयों के संदर्भ में बिक्री की जानकारी स्वचालित रूप से प्राप्त करने की अनुमति देता है।

व्यवसाय की आवश्यकताएं

मुख्य व्यावसायिक आवश्यकता सभी कंपनी कार्यालयों के लिए बिक्री रिपोर्ट की स्वचालित पीढ़ी थी। इसके अलावा, रिपोर्ट में निम्नलिखित विश्लेषणात्मक वर्गों में संख्या और बिक्री की मात्रा का डेटा होना चाहिए:

• अवधि (वर्ष, तिमाही, महीना, दिन)।
• उत्पाद (उत्पाद वर्गीकरण विशेषताओं सहित)।
• प्रतिपक्ष (उन विशेषताओं को शामिल करता है जो समकक्षों को वर्गीकृत करती हैं)।

रिपोर्ट आपको किसी भी विश्लेषणात्मक वर्गों के लिए नमूने के लिए फ़िल्टर लागू करने की अनुमति देनी चाहिए। मानों की एक मनमानी संख्या को फ़िल्टर के रूप में निर्दिष्ट किया जा सकता है। एक रिपोर्ट को एक मिनट से अधिक नहीं बनाया जाना चाहिए। रिपोर्ट पीढ़ी को 1 सी लेखा प्रणालियों के प्रदर्शन को महत्वपूर्ण रूप से प्रभावित नहीं करना चाहिए। कार्यान्वयन और रिपोर्ट का आगे रखरखाव न्यूनतम रूप से महंगा होना चाहिए।

प्रारंभिक मूल्यांकन और निर्णय चयन

उपलब्ध इनपुट डेटा और आवश्यकताओं के आधार पर, ग्राहक को निम्नलिखित समाधान की पेशकश की गई थी:

• एक डेटा वेयरहाउस विकसित करें जिसमें समेकित बिक्री रिपोर्ट उत्पन्न करने के लिए आवश्यक सभी जानकारी शामिल हो।
• केंद्रीय कार्यालय में SQL सर्वर डेटाबेस इंजन उदाहरण पर डेटा वेयरहाउस की तैनाती करें।
• एक बहुआयामी डेटा मॉडल विकसित करें जिसमें बिक्री रिपोर्ट उत्पन्न करने के लिए आवश्यक उपाय और माप शामिल हों।
• केंद्रीय कार्यालय में SSAS SQL सर्वर के एक उदाहरण पर बहुआयामी मॉडल वाले एक बहुआयामी डेटाबेस को तैनात करें।
• SSIS ETL पैकेज विकसित करने के लिए जिसके साथ डेटा वेयरहाउस और एक बहुआयामी डेटाबेस में अपडेट किया जाएगा।
• केंद्रीय कार्यालय में SSIS SQL सर्वर के एक उदाहरण पर SSIS पैकेजों को तैनात करें।
• पैकेज निष्पादन की स्थिति के बारे में तकनीकी सहायता विशेषज्ञों के ई-मेल द्वारा अधिसूचना के साथ एसएसआईएस पैकेजों का स्वत: निष्पादन सुनिश्चित करें।
• MS Excel में PivotTable रिपोर्ट ऑब्जेक्ट का उपयोग करके समेकित विक्रय रिपोर्ट बनाने के लिए कंपनी कर्मचारियों को एक बहुआयामी डेटाबेस तक पहुँच प्रदान करें।
• बिक्री रिपोर्टिंग कर्मचारियों के लिए प्रशिक्षण का प्रदर्शन करें।

समाधान कार्यान्वयन

स्टेज नंबर 1। 1 सी सिस्टम में डेटा स्रोतों के बारे में जानकारी का संग्रह। आवश्यक डेटा तक पहुंच के लिए प्रतिनिधित्व (दृश्य) का निर्माण

रिपॉजिटरी को डिज़ाइन करने से पहले, मैंने SQL डेटाबेस में विचार बनाए जो 1C सिस्टम का समर्थन करते हैं। मुझे विचारों के दो सेट मिले: केंद्रीय कार्यालय में एक डेटाबेस के लिए एक सेट (चित्र 1 देखें) और शाखाओं में डेटाबेस के लिए एक सेट (छवि 2)। आपको याद दिला दूं कि संगठन की शाखाओं में डेटाबेस संरचना समान है, लेकिन केंद्रीय कार्यालय में डेटाबेस संरचना से अलग है।


अंजीर। 1. केंद्रीय कार्यालय के एसक्यूएल डेटाबेस में प्रतिनिधित्व


अंजीर। 2. शाखाओं के SQL डेटाबेस में प्रतिनिधित्व

केंद्रीय कार्यालय और शाखाओं में प्रस्तुतियाँ की संरचना अलग-अलग हो गई, क्योंकि NSI का हिस्सा आम है और केंद्रीय कार्यालय में एक डेटाबेस में पूर्ण रूप से संग्रहीत किया जाता है। विशेष रूप से, हम अभ्यावेदन के बारे में बात कर रहे हैं:

• dbo.ChainStores (क्लाइंट ट्रेडिंग नेटवर्क)।
• dbo.Countries (दुनिया के देशों का वर्गीकरण)।
• dbo.Products (उत्पाद)।
• dbo.ProductAnalogs (उत्पाद एनालॉग्स)।
• dbo.ProductTypes (उत्पाद प्रकारों का वर्गीकरण)।
• dbo.Projects (ग्राहकों के प्रकार का वर्गीकरण)।
• dbo.ProjectsForProductMatrix (उत्पाद प्रकारों का वर्गीकरण)।
• dbo.CrossProductsAndProjectsForProductMatrix (dbo.Products और dbo.ProjectsForProductMatrix विचारों के बीच कई-से-कई संबंध प्रदान करने के लिए एक दृश्य)।

SQL डेटाबेस में विचार बनाना आपको समाधान को और अधिक बहुमुखी बनाने की अनुमति देता है। उदाहरण के लिए, 1C डेटाबेस में तालिकाओं की संरचना को बदलते समय, हमें ETL पैकेज में परिवर्तन नहीं करना है, यह विचारों को फिर से करने के लिए पर्याप्त होगा।

स्टेज नंबर 2। डेटा वेयरहाउस संरचना डिज़ाइन करें। डेटा वेयरहाउस परिनियोजन

पहले चरण को पूरा करने के बाद, हम आसानी से डेटा स्रोतों में संग्रहीत संरचना और प्रकार के डेटा के बारे में जानकारी प्राप्त कर सकते हैं और भंडारण संरचना को डिज़ाइन कर सकते हैं। ऐसा करने के लिए, केवल प्रस्तुति कॉलम के प्रकार देखें। उदाहरण के लिए, dbo.Clients दृश्य इस प्रकार है।


अंजीर। 3. dbo.Clients का प्रतिनिधित्व

ध्यान दें कि dbo.Clients दृश्य में एक ParentId फ़ील्ड है। इस क्षेत्र का उपयोग करते हुए, हम बाद में "ग्राहक" आयाम के लिए बहुआयामी डेटा मॉडल में पेरेंट-चाइल्ड पदानुक्रम का निर्माण कर सकते हैं। एक समान फ़ील्ड dbo.Products और dbo में मौजूद है। ग्रामीणों के विचार।

इससे पहले कि आप एक डेटा वेयरहाउस डिजाइन करना शुरू करें, आपको इसकी योजना तय करने की आवश्यकता है। डेटा स्टोरेज के लिए दो स्कीम हैं- एक स्टार और एक स्नोफ्लेक । दोनों योजनाओं में उनके पेशेवरों और विपक्ष हैं, और उनकी तुलना इस लेख के दायरे से परे है। मैंने स्नोफ्लेक स्कीम को चुना, इस तथ्य से निर्देशित कि जब SQL Server 2012 में स्विच किया जाता है और भविष्य में स्वयं-सेवा BI का उपयोग किया जाता है, तो संभवतः उपयोगकर्ताओं के लिए Excel के लिए PowerPivot में अपने स्वयं के डेटा मॉडल विकसित करते समय डेटा वेयरहाउस से अधिक सामान्यीकृत डेटा के साथ काम करना सुविधाजनक होगा। मेरे द्वारा विकसित किए गए डेटा वेयरहाउस की संरचना को निम्न आकृति में दिखाया गया है।


अंजीर। 4. डेटा वेयरहाउस संरचना

तालिकाओं के मंद। डिमडेट्स (दिनांक), मंद.डिमोफ़िसेस (कार्यालय), मंद.डिमेयर (रूस के क्षेत्र) एक बार में भरे गए थे और उन्हें स्वचालित अपडेट की आवश्यकता नहीं थी। Dim.DimOffices तालिका में कंपनी के कार्यालयों के नाम शामिल हैं। Dim.DimDates तालिका में एक बहुआयामी डेटा मॉडल में संबंधित आयाम के लिए तारीख की जानकारी होती है। माप तालिका में एक सरोगेट कुंजी होती है जो प्राथमिक कुंजी के रूप में कार्य करती है। ऐसा इसलिए है क्योंकि अलग-अलग डेटा स्रोतों में रिकॉर्ड कीज़ अंतर हो सकती हैं।

स्टेज नंबर 3। एक बहुआयामी डेटा मॉडल का विकास। एक बहुआयामी डेटाबेस तैनात करें

बहुआयामी डेटा मॉडल बनाते समय, एक डेटा स्रोत दृश्य बनाया गया था, जिसमें मंच के अलावा, डेटा वेयरहाउस से सभी तालिकाओं को शामिल किया गया था। इस तालिका को केवल तथ्य में लोड करने से पहले बिक्री डेटा को अस्थायी रूप से संग्रहीत करने के लिए उपयोग किया जाएगा। FactSales तथ्य तालिका।

उपायों के दो समूह बिक्री घन में लागू किए गए हैं (चित्र 5 देखें)।


अंजीर। 5. उपाय

उत्पाद मैट्रिक्स माप समूह के लिए क्रॉस उत्पाद और परियोजनाएं उत्पाद मैट्रिक्स के लिए आयाम उत्पाद और वितरण चैनलों के बीच कई-से-कई संबंध प्रदान करती हैं।

माप की सूची चित्र 6 में दिखाई गई है।


अंजीर। 6. माप

माल, ग्राहकों, प्रबंधकों के मापन के लिए, अभिभावक-बच्चे के पदानुक्रम को कार्यान्वित किया जाता है।


अंजीर। 7. माप का सामान

एक बहुआयामी डेटाबेस तक पहुंच को नियंत्रित करने के लिए, एनलिस्ट की भूमिका बनाई गई थी, जिसे सेल्स क्यूब के लिए रीड एंड ड्रिलिल राइट प्रदान किया गया था। Drillthrough अनुमतियाँ उपयोगकर्ताओं को इस बारे में जानकारी के साथ एक प्रतिलेख प्राप्त करने की अनुमति देती हैं कि रिपोर्ट में सेल मान की गणना कैसे की गई।


अंजीर। 8. विश्लेषकों की भूमिका

सर्वर पर एक बहुआयामी डेटाबेस को तैनात करने के लिए, प्रोजेक्ट गुणों में SSAS SQL सर्वर का उदाहरण निर्दिष्ट करें, सर्वर पर डेटाबेस का नाम और BIDS मेनू में Deploy क्लिक करें। हम एसएमएस का उपयोग करके SSAS उदाहरण से कनेक्ट करते हैं और देखते हैं कि एक बहुआयामी डेटाबेस बनाया गया है।


अंजीर। 9. बहुआयामी बिक्री OLAP डेटाबेस

स्टेज नंबर 4। ETL पैकेज का विकास। ईटीएल संकुल की तैनाती। ईटीएल पैकेट का स्वत: निष्पादन स्थापित करना

बिजनेस इंटेलिजेंस समाधानों को डिजाइन करने में सबसे अधिक समय लेने वाला कदम ईटीएल पैकेजों का विकास है। यह इस तथ्य के कारण है कि डेटा स्रोत, एक नियम के रूप में, एक अलग संरचना है, और उनमें संग्रहीत डेटा में त्रुटियां हैं और एक अलग प्रारूप है। उदाहरण के लिए, विभिन्न डेटाबेस में एक कर्मचारी के लिंग को एम और एफ या अंक 0 और 1 के अक्षरों द्वारा दर्शाया जा सकता है, और इस डेटा को रिपॉजिटरी में लोड करने से पहले, इसे साफ करना और इसे एक सामान्य दृश्य में लाना आवश्यक है। इसके अलावा, केवल डेटा जो आखिरी डाउनलोड में दर्ज या परिवर्तित किया गया है, उसे डेटा वेयरहाउस में अपडेट किया जाना चाहिए। ये केवल मुख्य कठिनाइयां हैं, वास्तव में, कई और भी हैं। हालाँकि, SSIS उपकरणों के साथ, इनमें से अधिकांश समस्याओं का समाधान किया जा सकता है। मेरे कार्यान्वयन में, आयाम तालिकाओं में डेटा पूरी तरह से अपडेट किया गया है, अर्थात्। नई प्रविष्टियाँ जोड़ी गई हैं और मौजूदा प्रविष्टियाँ अधिलेखित हैं। फैक्ट टेबल को तीन महीने की डिफ़ॉल्ट अवधि में फिर से साफ और आबाद किया जाता है। तथ्य तालिका को महीनों में अपडेट करने की गहराई एसएसआईएस पैकेज कॉन्फ़िगरेशन में संग्रहीत है, जो डेटा वेयरहाउस में एक अलग तालिका है।


अंजीर। 10. एसएसआईएस पैकेज

चित्र 10 में 4 एसएसआईएस पैकेज दिखाए गए हैं जिनका उद्देश्य निम्नानुसार है:

• अद्यतन DW और प्रक्रिया बिक्री OLAP.dtsx एक मास्टर पैकेज है जो ETL प्रक्रिया के सामान्य तर्क को लागू करता है और अन्य सभी पैकेज चलाता है।
• मास्को डायटक्स से आयात आयाम और तथ्य - डेटा वेयरहाउस में केंद्रीय कार्यालय डेटाबेस से आयाम और तथ्य तालिकाओं में डेटा लोड करने के लिए एक पैकेज।
• Filials.dtsx से आयात आयाम और तथ्य - डेटा वेयरहाउस में शाखा डेटाबेस से आयाम और तथ्य तालिकाओं में डेटा लोड करने के लिए एक पैकेज।
• प्रक्रिया बिक्री OLAP.dtsx - एक पैकेज जो एक बहुआयामी डेटाबेस में डेटा अपडेट (प्रसंस्करण) करता है।

मास्टर पैकेज का तर्क (नियंत्रण प्रवाह) निम्नानुसार है (चित्र 11 देखें)।


अंजीर। 11. DW और प्रोसेस सेल्स OLAP को अपडेट करें

इस सर्किट के प्रत्येक तत्व पर विचार करें:

• सबसे पहले, सेट पैकेज के वेरिएबल्स वैल्यूज़ (एक्सक्यू्यूट SQL टास्क) को निष्पादित किया जाता है। इस तत्व का कार्य पैकेज कॉन्फ़िगरेशन से मान प्राप्त करना और उन्हें पैकेज चर में लिखना है। पैकेज कॉन्फ़िगरेशन में महीनों में तथ्य तालिका को अद्यतन करने की गहराई के बारे में जानकारी शामिल है। पैकेज कॉन्फ़िगरेशन को डेटाबेस रिपॉजिटरी में एक अलग तालिका में संग्रहीत किया जाता है और इसे आईटी विशेषज्ञों द्वारा बदला जा सकता है।
• अगला, आयामों में डिफ़ॉल्ट मान डालें (SQL टास्क निष्पादित करें) चेक करें और खाली तत्वों के साथ डेटा वेयरहाउस को पॉप्युलेट करें। उदाहरण के लिए, dim.DimProducts तालिका में, इस कार्य को पूरा करने के बाद, शून्य के बराबर एक पहचानकर्ता (Id) वाला तत्व दिखाई देना चाहिए। तार्किक डेटा अखंडता सुनिश्चित करने के लिए अशक्त पहचानकर्ताओं के साथ रिकॉर्ड सभी आयाम तालिकाओं में बनाए जाएंगे, क्योंकि तथ्य तालिका के सभी क्षेत्रों को NULL के रूप में परिभाषित नहीं किया गया है और उनका डिफ़ॉल्ट मान शून्य है। तथ्य तालिका में NULLs की उपस्थिति एक बहुआयामी डेटाबेस को संसाधित करते समय त्रुटियों की ओर ले जाती है।
• मॉस्को में सोर्स ओएलटीपी डेटाबेस की सूची प्राप्त करें (एक्सेक्यूट एसक्यूएल टास्क) केंद्रीय कार्यालय के डेटाबेस की एक सूची प्राप्त करता है (मेरे मामले में केवल एक ही ऐसा डेटाबेस है, लेकिन समाधान की सबसे सार्वभौमिकता के लिए, मैंने सुझाव दिया कि कई हो सकते हैं)। डेटाबेस की सूची को तालिका dim.DimOffices में संग्रहीत किया जाता है। डेटाबेस कनेक्शन स्ट्रिंग्स भी इस तालिका में संग्रहीत हैं। परिणामी चयन पैकेज चर के लिए लिखा गया है।
• मास्को में सभी ओएलटीपी डेटाबेस के लिए (फॉरचून लूप कंटेनर) पिछले चरण में प्राप्त चयन पर आधारित है, और प्रत्येक चयन पंक्ति (यानी प्रत्येक डेटाबेस के लिए) से आयात आयाम और तथ्यों को मास्को.dtsx से आयात करता है। पैरामीटर को संकुल कॉन्फ़िगरेशन मूल्यों को सेट करके संकुल से मास्टर पैकेज से स्थानांतरित किया जाता है जिसे सेट पैकेज कॉन्फ़िगरेशन (निष्पादित SQL टास्क) निष्पादित करता है।
• अगले दो चरण फ़िल्टर (एक्ज़ेक्यूट पैकेज टास्क) में स्रोत ओएलटीपी डेटाबेस की सूची प्राप्त करें (फ़ाइलेक लूप कंटेनर में सभी ओएलटीपी डेटाबेस के लिए) फ़ॉरचैट डेटाबेस के लिए केवल पिछले दो के समान हैं।
• अंतिम चरण प्रक्रिया बिक्री OLAP (एक्सक्यूट पैकेज पैकेज) एक बहुआयामी डेटाबेस में डेटा अपडेट पैकेज लॉन्च करती है।

ऊपर वर्णित पैकेज SSIS SQL सर्वर के एक उदाहरण के लिए तैनात किए गए हैं। SQL सर्वर पर स्वचालित रूप से मास्टर पैकेज चलाने के लिए, अद्यतन DW और प्रक्रिया बिक्री OLAP कार्य बनाया गया था (चित्र 12 देखें)।


अंजीर। 12. SSIS पैकेज को चलाने के लिए SQL जॉब

ईटीएल प्रक्रिया के निष्पादन को नियंत्रित करने के लिए, कार्य पूरा होने के ई-मेल द्वारा समर्थन विशेषज्ञों को सूचित करने के लिए कॉन्फ़िगर किया गया है (देखें। छवि 13)।


अंजीर। 13 नौकरी अधिसूचना ईमेल की स्थापना

स्टेज नंबर 5। एक बहुआयामी डेटाबेस तक पहुँच प्रदान करना

एसएमएस के उपयोग से बहुआयामी डेटाबेस की विश्लेषकों की भूमिका में उनके डोमेन खातों को शामिल करके संगठन के कर्मचारियों को बहुआयामी डेटाबेस तक पहुंच प्रदान की जाती है (चित्र 14 देखें)।


अंजीर। 14. एनालिस्ट की भूमिका में सदस्यता

स्टेज नंबर 6। संगठन कर्मचारी प्रशिक्षण

उपयोगकर्ता प्रशिक्षण के लिए, एक 15 मिनट का वीडियो रिकॉर्ड किया गया था, जिसने एमएस एक्सेल की क्षमताओं का प्रदर्शन किया था, जिससे आप एक बहुआयामी डेटाबेस से जुड़ सकते हैं और PivotTable रिपोर्ट ऑब्जेक्ट का उपयोग करके एक रिपोर्ट बना सकते हैं। संभावित रिपोर्ट विकल्पों में से एक चित्र 15 में दिखाया गया है।


अंजीर। 15. एक्सेल में नमूना PivotTable रिपोर्ट

निष्कर्ष

ग्राहकों की आवश्यकताओं को पूरी तरह से लागू किया गया है। दैनिक बिक्री रिपोर्ट तैयार करते हुए, कंपनी के प्रमुख उपयोगकर्ताओं द्वारा बीटा परीक्षण किया गया। अपनी रिपोर्ट में, मुख्य उपयोगकर्ताओं ने बनाए गए समाधान को बिक्री का व्यापक विश्लेषण करने के लिए बहुत सुविधाजनक, तेज और पर्याप्त बताया। समाधान का मूल्यांकन करने के लिए, मैं कुछ आंकड़े देता हूं:

• इस समाधान के कार्यान्वयन पर 40 मानव-घंटे खर्च किए गए थे। वर्णित सभी एक व्यक्ति द्वारा किया गया था, अर्थात्। मेरे द्वारा। मैंने पहले पाठ्यक्रमों में भाग लिया और सफलतापूर्वक Microsoft परीक्षा उत्तीर्ण की, बिजनेस इंटेलिजेंस में Microsoft प्रमाणित समाधान विशेषज्ञ प्रमाणपत्र प्राप्त किया।
• उत्पादन डेटाबेस में तथ्य तालिका में ~ 40 मिलियन पंक्तियाँ हैं।
• ETL प्रक्रिया में लगभग 20 मिनट लगते हैं।
• रिपोर्टिंग कुछ सेकंड के भीतर की जाती है।