Προβολή πηγαίου κώδικα για τη σελίδα CSDataWarehouseConfigurationEN

{{DISPLAYTITLE:CS Data Warehouse Configuration EN}}


Table of = Required tools =

To begin with, in order to develop and deploy On-line analytical processing cubes the following must be installed:

* '''MS SQL Server Analysis Services (SSAS)'''

It is an analytical data engine used in decision support and business analytics and it provides enterprise-grade semantic data models for business reports.

Hereafter, the SQL Server Analysis Services will be called ‘SSAS’.

This engine is installed through the MS SQL Server Management Studio installation CD.

* '''MS SQL Server Integration Services (SSIS)'''

It is a platform for building enterprise-level data integration and data transformation solutions.

Hereafter, the SQL Server Analysis Services will be called ‘SSIS’.

This platform is also installed through the MS SQL Server Management Studio installation CD.

* '''MS SQL Server Data Tools (SSDT)'''

This tool is used for building, debugging, maintaining and refactoring databases or database projects.

Hereafter, the SQL Server Analysis Services will be called ‘SSDT’.

This tool could be found and downloaded for free by accessing the following URL: [https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt Download SQL Server Data Tools (SSDT)]

= SSAS Parametersts =

= Data Warehouse Objects =

This data warehouse project is consisted of three (3) Cubes; '''SalesAnalytics''', '''RetailSalesTransactionAnalytics''', '''RetailItemDimensionAnalysis''' and numerous dimensions and measure groups.

== SalesAnalytics cube ==

{|
! Dimension
! Attributes
|-
| Company
| Company
|-
| Company Branch
| Branch
|-
| Customer
| Customer, Code, Name, Family, Industry
|-
| Customer Address
| Region – Country – District – City, Address
|-
| Date Analysis
| Year – Semester – Quarter – Month, Date, IsCurrent
|-
| Sales Person
| Name
|-
| Item
| Family – Group – Category – Subcategory – Item
|-
| Business Unit
| Business unit
|-
| Business Activity
| Business activity
|-
| Dimension 1
| Dimension 1
|-
| Dimension 2
| Dimension 2
|}

{|
! '''Measure Group'''
! '''Measures'''
|-
| Fact Key Figures
| Turnover, Discounts, Sales Qty, Cost Of Goods Sold, Profit Margin<ref>Calculated member</ref>, Profit Margin Percent<sup>1</sup>, Turnover Variance<sup>1</sup>, Turnover Variation Percent<sup>1</sup>, Sales Qty Variance<sup>1</sup>, Sales Qty Variation Percent<sup>1</sup>
|-
| Transaction Key Figures
| Transactions Count, Transactions Count Variance<sup>1</sup>, Transactions Count Variation Percent<sup>1</sup>
|-
| Customer Key Figures
| Customers Count, Customers Count Variance<sup>1</sup>, Customers Count Variation Percent<sup>1</sup>
|-
| Item Key Figures
| Items Count, Items Count Variance<sup>1</sup>, Items Count Variation Percent<sup>1</sup>
|}

== RetailSalesTransactionAnalytics cube ==

{|
! Dimension
! Attributes
|-
| Company
| Company
|-
| Company Branch
| Branch
|-
| Customer
| Customer, Code, Name, Family, Industry
|-
| Customer Address
| Region – Country – District – City, Address
|-
| Retail Date Analysis
| Year – Semester – Quarter – Month – Week - Day, Date, IsCurrent
|-
| Item
| Family – Group – Category – Subcategory – Item
|-
| Document
| Document, Document Type, Anonymous customer, Credit Card Use, Time Zone
|-
| Business Unit
| Business unit
|-
| Business Activity
| Business activity
|-
| Dimension 1
| Dimension 1
|-
| Dimension 2
| Dimension 2
|}

{|
! '''Measure Group'''
! '''Measures'''
|-
| Fact Key Figures
| Turnover, Discounts, Sales Qty, Cost Of Goods Sold, Turnover Variance<ref>Calculated member</ref>, Average Turnover Per Transaction<sup>2</sup>, Sales Qty Variance<sup>2</sup>, Sales Qty Variation Percent<sup>2</sup>
|-
| Transaction Key Figures
| Transactions Count, Transactions Count Variance<sup>2</sup>, Transactions Count Variation Percent<sup>2</sup>, Credit Card Transaction Count<sup>2</sup>, Anonymous Customer Transaction Count<sup>2</sup>
|-
| Customer Key Figures
| Customers Count, Customers Count Variance<sup>2</sup>, Customers Count Variation Percent<sup>2</sup>, Anonymous Customer Count<sup>2</sup>, Anonymous Customer Turnover<sup>2</sup>, Anonymous Customer Turnover Percent<sup>2</sup>
|-
| Item Key Figures
| Items Count, Items Count Variance<sup>2</sup>, Items Count Variation Percent<sup>2</sup>, Item Family Count<sup>2</sup>, Item Group Count<sup>2</sup>, Item Category Count<sup>2</sup>, Item Subcategory Count<sup>2</sup>
|}

== RetailItemDimensionAnalysis cube ==

{|
! Dimension
! Attributes
|-
| Company
| Company
|-
| Company Branch
| Branch
|-
| Retail Date Analysis
| Year – Semester – Quarter – Month – Week - Day, Date, IsCurrent
|-
| Retail Item
| Family – Group – Category – Subcategory – Item
|-
| Item Main Color
| Main color
|-
| Item Main Size
| Main size
|}

{|
! '''Measure Group'''
! '''Measures'''
|-
| Fact Retail Item Entries
| Turnover, Discounts, Sales Qty
|}

= SQL Server Analysis Services (SSAS) Project  =

Each one of the previously mentioned cubes consists of several sets of partitions. The number of sets depends on the number of measure groups.

{|
! '''Cube'''
! '''Measure groups'''
! '''Total number of partitions'''
|-
| SalesAnalytics
| Fact Key Figures, Transaction Key Figures, Customer Key Figures, Item Key figures
| 8
|-
| RetailSalesTransactionAnalytics
| Fact Key Figures, Transaction Key Figures, Customer Key Figures, Item Key figures
| 8
|-
| RetailItemDimensionAnalysis
| Fact Retail Item Entries
| 2
|}

By default, each measure group has two partitions; ‘Facts up to 3 months before’ and ‘Recent facts’.

# Facts up to 3 months before

This partition has as starting point of the time frame that will be used for data retrieval the value of a parameter. This parameter value is stored in the column ‘'''Value'''’ of '''ES00SSASParameters''' table in the database and refers to a number of '''years'''. The ending point of the time frame is three months before the current date. For instance, let us suppose that the current date is 2019-06-13. If you wish to retrieve data from 2016, then the parameter value should be 3. Specifically, the partition will retrieve data with starting point 2016-01-01 and ending point 2019-03-13.

<ol start="2" style="list-style-type: decimal;">
<li><p>Recent facts</p></li></ol>

This partition uses as starting point of the time frame for data retrieval one day after the ending point of the ‘Facts up to 3 months before’ and it has no ending point. For example, if the ending point of ‘Facts up to 3 months before’ partition is 2019-03-13 then ‘Recent facts’ partition will retrieve data with starting point 2019-03-14.

<ul>
<li><p>[[File:CSDataWarehouseConfigurationEN-image2.png|44x42px]]'''The starting point of the time frame of the ‘Facts up to 3 months before’ partition is ALWAYS the beginning of the year.'''</p></li>
<li><p>'''If one of the two partitions is changed, then the same partition in the other measure groups must be updated.'''</p></li>
<li><p>'''Data on a partition must not be the same with data on another partition.'''</p>
<ol style="list-style-type: decimal;">
<li>== [[File:CSDataWarehouseConfigurationEN-image4.png|572x113px]]Set deployment server ==
</li></ol>
</li></ul>

After downloading the folder '''CSDataWarehouseProject''' and declaring the value of the ‘'''Value'''’ of '''ES00SSASParameters''' table in the database, go to '''SSAS''' and open the '''EntersoftDW.sln''' file.

To set the deployment server right-click on the file of the project in the ''Solution Explorer'' ''pane'' and go to '''Properties &gt; Deployment''' and declare the server name and the database name.

<blockquote>[[File:CSDataWarehouseConfigurationEN-image2.png|51x50px]]'''Changing the Database name is <span class="underline">NOT ALLOWED</span>. The Database name should ALWAYS be <span class="underline">CSDW</span>.'''
</blockquote>
[[File:CSDataWarehouseConfigurationEN-image5.png|230x323px]]

[[File:CSDataWarehouseConfigurationEN-image6.png|396x229px]]

== Set Data Source ==

[[File:CSDataWarehouseConfigurationEN-image7.png|162x153px]]To set data source connection, go to '''EntersoftDW &gt; Data Sources &gt; EBS_Server.ds''' and double-click on '''EBS_Server.ds''' in the ''Solution Explorer'' pane'''.'''

When the Data Source Designer opens, click on '''edit''' button to set the Server name, server log on credentials and the database in the ‘'''General’''' tab.

By default, the ‘''Use the service account''’ option is selected in the ‘'''Impersonation Information'''’ tab. In this case, the Analysis Services object uses the security credentials associated with the Analysis Services service that manages the object.

<blockquote>[[File:CSDataWarehouseConfigurationEN-image2.png|51x50px]]'''Changing the Data source name is <span class="underline">NOT ALLOWED</span>. The Data source name should ALWAYS be <span class="underline">EBS_Server</span>.'''
</blockquote>
[[File:CSDataWarehouseConfigurationEN-image8.png|271x292px]]

[[File:CSDataWarehouseConfigurationEN-image9.png|297x258px]]

= SQL Server Integration Services (SSIS) Project  =

[[File:CSDataWarehouseConfigurationEN-image10.jpeg|250x175px]]The existence of '''Integration Services Catalog''' is mandatory in the MS SQL Server Management Studio. If there is no integration services catalog, you must create one by accessing the MS SQL Server Management Studio. Right-click on the '''Integration services catalogs''' file and select '''Create Catalog.'''

== Set SQL Server Analysis Services (SSAS) Database connection ==

After creating the Integration Services Catalog, go to the '''CSDataWarehouseProject''' folder &gt; '''SSIS''' and open the '''SSISEntersoftDW.sln''' file and set the SQL Server Analysis Services (SSAS) Database connection.

To set the SQL Server Analysis Services Database connection, go to '''SSISEntersoftDW &gt; Connection Managers''' and double-click on '''conmgr''' file in the ''Solution Explorer'' pane'''.'''

When the Analysis Services Connection Manager opens, click on '''edit''' button to set the Server name.

<blockquote>[[File:CSDataWarehouseConfigurationEN-image2.png|51x50px]]'''Changing the Initial Catalog is <span class="underline">NOT ALLOWED</span>. The Initial Catalog should ALWAYS be <span class="underline">CSDW</span>.'''
</blockquote>
[[File:CSDataWarehouseConfigurationEN-image11.png|242x178px]]

[[File:CSDataWarehouseConfigurationEN-image12.png|296x188px]][[File:CSDataWarehouseConfigurationEN-image13.png|246x214px]]

== Project deployment ==

<blockquote>To complete the overall process, the project must be deployed. On the SSDT Toolbar select '''Project &gt; Deploy''' to open the deployment wizard. Click ‘Next’ in the Introduction pane to proceed.
</blockquote>
<ol style="list-style-type: lower-alpha;">
<li><p>Select “Project Deployment” option in ''Select the deployment model'' parameter.</p></li>
<li><p>Activate the “Project deployment file” option and click browse to go to '''…\CSDataWarehouseProject\SSIS\SSISEntersoftDW\bin\Development,''' select '''SSISEntersoftDW.ispac''' and click next.</p></li>
<li><p>Set Server name, click ‘Connect’ and browse to assign the integration catalog folder. In the following panes, click ‘Next’ and finally ‘Deploy’.</p></li></ol>

<blockquote>[[File:CSDataWarehouseConfigurationEN-image14.png|280x262px]][[File:CSDataWarehouseConfigurationEN-image15.png|364x205px]] [[File:CSDataWarehouseConfigurationEN-image16.png|262x246px]] [[File:CSDataWarehouseConfigurationEN-image17.png|237x223px]] [[File:CSDataWarehouseConfigurationEN-image18.png|227x219px]]
</blockquote>
= SQL Server Agent Job  =

After deploying the SQL Server Integration Services Project, go to SQL Server Management Studio, browse SQL Server Agent. Right-click on ‘Jobs’ folder and select ‘New Job’.

<ol style="list-style-type: lower-alpha;">
<li><p>Give a name to this job in ‘General’ tab</p></li>
<li><p>Go to ‘Steps’ tab and click ‘New…’. Name this step.</p></li>
<li><p>In the type parameter select ‘SQL Server Integration Services Package’, set the server name in the '''packages''' tab and select the SSIS package that was previously deployed.</p></li>
<li><p>[[File:CSDataWarehouseConfigurationEN-image19.png|275x59px]]Go to the '''configuration''' tab and define which of the [[#data-warehouse-objects|three (3) available cubes]] you wish to be processed by setting each parameter to '''True''' or '''False.'''</p></li>
<li><p>[[File:CSDataWarehouseConfigurationEN-image20.png|273x248px]][[File:CSDataWarehouseConfigurationEN-image21.png|330x245px]][[File:CSDataWarehouseConfigurationEN-image22.png|297x269px]][[File:CSDataWarehouseConfigurationEN-image23.png|357x265px]][[File:CSDataWarehouseConfigurationEN-image24.png|298x227px]]Schedule the job based on your needs in order to process the selected cubes.</p></li></ol>

= Accessing Cube through Excel =

Open a new Excel sheet and go to '''Data &gt; From other Sources''' and select '''From Analysis Services.''' On the pop-up screen declare the SQL Server Analysis Services name and then the cube. Define the type of data depiction (Pivot table or Pivot chart) and declare the measures and dimensions that you want to insert through the Pivot table or chart pane.

[[File:CSDataWarehouseConfigurationEN-image25.png|358x172px]][[File:CSDataWarehouseConfigurationEN-image26.png|255x375px]][[File:CSDataWarehouseConfigurationEN-image27.png|301x260px]][[File:CSDataWarehouseConfigurationEN-image28.png|336x235px]][[File:CSDataWarehouseConfigurationEN-image29.png|362x261px]]

[[File:CSDataWarehouseConfigurationEN-image30.png|697x241px]]

[[File:CSDataWarehouseConfigurationEN-image31.png|579x345px]]

= Accessing Cube through Power BI =

Open Power BI and go to '''Get''' '''Data &gt; Analysis Services.''' On the pop-up screen declare the SQL Server Analysis Services name (mandatory field) and the database name (optional field) and click ok. A ‘Navigator’ form is shown in order to select the cube you wish to use. After selecting a cube, its schema is shown in the '''Fields''' pane and you can design your charts by selecting one or more measures and dimensions.

[[File:CSDataWarehouseConfigurationEN-image32.png|239x291px]][[File:CSDataWarehouseConfigurationEN-image33.png|448x202px]][[File:CSDataWarehouseConfigurationEN-image34.png|158x306px]]

= Best Practices =

<references />

[[Category:Tech_Reporting]]

{{#seo:
|title=CSDataWarehouseConfigurationEN
|title_mode=append
|keywords=ERP,CRM,Expert,Retail,Sales Force,Mobile,Cloud,B2B,Entersoft Business Suite,Entersoft Expert,Entersoft CRM,Entersoft WMS,Entersoft Retail,Entersoft Cloud,Entersoft Sales Force for iOS,Entersoft Mobile for Android,Entersoft Medical Representative,Entersoft Field Service,Enterprise Mobility,Entersoft xVan
}}



[[Media:CSDataWarehouseConfigurationEN.pdf|PDF Version]]