HI,
Actually from NAV 2013 we don't have any Business Intelligence setup. So we have to develop all Dimensions and Measures from Dot net Environment.
If we use SQL Server 2012 then we have to select "SQL Server Data Tools" which is SQL Server 2012 folder from all programs.
If we use SQL Server 2008 R2 then we have to select "SQL Server Business Intelligence Development Studio" which is SQL Server 2008 R2 folder from all programs.
****** IMP ****
(Before starting we should create a Database in Database engine and import the required tables from live database for Analysis. This database information will update dynamically from live databse by creating SSIS Package because we should not disturb the Live database)
Steps to Create MultiDimesional Project for SQL Analysis:
After Opening and select path for the Project,
Step :1
The first thing we need to do is to tell upon which Data Source this project should be defined.
Right-click the Data Sources folder to start defining a Data Source for the Multidimensional project.
Clicking the New button will open the Connection Manager dialogue box.
The new connection must point to the your database on our SQL server. To access databases on our SQL server, you should select Native OLE DB\SQL Server Native Client 11.0.
Step : 2
The Data Source View is where we define the relationship between the tables that we want to access via the Data Source.
We will also be setting logical keys for the tables as we define their relationship.
Start out by right-clicking the Data Source Views folder to create a new Data Source View.
In the next step we will select from a list of available objects (tables in our case) to be included in the Data Source View.
And click next after that finish it.
Now we want to establish the relationship between the tables.
Already we know about Fact tables and Dimension tables
Some tables contain the actual data or the facts that we would like to analyze such as Amount, Quantity etc. These tables are often referred to as Fact tables.
Each post in the fact tables also contains information about e.g. which item has been sold, but the item information in the fact table may be kept short, often limited to the item number only.
Further information about an item number such as name and description may then be found in another table, e.g. the Item table. Such a table is then called a Dimension table.
Just Right click on the table and establish relation ship by selecting the tables and the fields which are primary and Foreign keys.
Step :3
Creating Dimensions for our Analysis:
Dimensions to categorize and to filter your data.
we can go to the Solution Explorer on the right hand side to add a New Dimension to the Dimensions folder.
Select appropriate Data source view and select Main table(from where you want to select the dimension fields) and Key column.
Next select the remaining field which you want to use as dimensions. and finish it.
All you can see in Dimension Structure tab.
Finally Process the Dimension.(If you get any impersonation problem,
To correct the Impersonation setting, you will have to go back to the Data Source definition of your project in the Solution Explorer on the right hand side. Double click the DBName.ds Data Source or right click to open the Data Source settings dialogue box.Select the Service Account.)
Step : 4
Creating Cubes for our Analysis :
To create a new cube you should right click the Cubes folder in the Solution Explorer on the right hand side of Visual Studio.
the Cube Wizard is actually just showing all the fields from the table that has a numeric data type.So Select the Tables and in the next select the required measures for Analysis.Next you will get the Dimension sets what you created, select as per requirement.
Finally Process The Cube.(If you get any impersonation problem,
Before clicking the Run button above there is one button change settings select it Dimension Key Errors
Select "Use Custom error configuration" and select "Ignore errors count")
Its Completed now you can check data by selecting dimensions and measures in Browser tab in visual studio.
But Database day by day increse so we should create SSIS package to get data Dynamically.
We will see how to create SSIS package in my next post.
Actually from NAV 2013 we don't have any Business Intelligence setup. So we have to develop all Dimensions and Measures from Dot net Environment.
If we use SQL Server 2012 then we have to select "SQL Server Data Tools" which is SQL Server 2012 folder from all programs.
If we use SQL Server 2008 R2 then we have to select "SQL Server Business Intelligence Development Studio" which is SQL Server 2008 R2 folder from all programs.
****** IMP ****
(Before starting we should create a Database in Database engine and import the required tables from live database for Analysis. This database information will update dynamically from live databse by creating SSIS Package because we should not disturb the Live database)
Steps to Create MultiDimesional Project for SQL Analysis:
After Opening and select path for the Project,
Step :1
The first thing we need to do is to tell upon which Data Source this project should be defined.
Right-click the Data Sources folder to start defining a Data Source for the Multidimensional project.
Clicking the New button will open the Connection Manager dialogue box.
The new connection must point to the your database on our SQL server. To access databases on our SQL server, you should select Native OLE DB\SQL Server Native Client 11.0.
Step : 2
The Data Source View is where we define the relationship between the tables that we want to access via the Data Source.
We will also be setting logical keys for the tables as we define their relationship.
Start out by right-clicking the Data Source Views folder to create a new Data Source View.
Now we want to establish the relationship between the tables.
Already we know about Fact tables and Dimension tables
Some tables contain the actual data or the facts that we would like to analyze such as Amount, Quantity etc. These tables are often referred to as Fact tables.
Each post in the fact tables also contains information about e.g. which item has been sold, but the item information in the fact table may be kept short, often limited to the item number only.
Further information about an item number such as name and description may then be found in another table, e.g. the Item table. Such a table is then called a Dimension table.
Just Right click on the table and establish relation ship by selecting the tables and the fields which are primary and Foreign keys.
Step :3
Creating Dimensions for our Analysis:
Dimensions to categorize and to filter your data.
we can go to the Solution Explorer on the right hand side to add a New Dimension to the Dimensions folder.
Select appropriate Data source view and select Main table(from where you want to select the dimension fields) and Key column.
Next select the remaining field which you want to use as dimensions. and finish it.
All you can see in Dimension Structure tab.
Finally Process the Dimension.(If you get any impersonation problem,
To correct the Impersonation setting, you will have to go back to the Data Source definition of your project in the Solution Explorer on the right hand side. Double click the DBName.ds Data Source or right click to open the Data Source settings dialogue box.Select the Service Account.)
Step : 4
Creating Cubes for our Analysis :
To create a new cube you should right click the Cubes folder in the Solution Explorer on the right hand side of Visual Studio.
the Cube Wizard is actually just showing all the fields from the table that has a numeric data type.So Select the Tables and in the next select the required measures for Analysis.Next you will get the Dimension sets what you created, select as per requirement.
Finally Process The Cube.(If you get any impersonation problem,
Before clicking the Run button above there is one button change settings select it Dimension Key Errors
Select "Use Custom error configuration" and select "Ignore errors count")
Its Completed now you can check data by selecting dimensions and measures in Browser tab in visual studio.
But Database day by day increse so we should create SSIS package to get data Dynamically.
We will see how to create SSIS package in my next post.
No comments:
Post a Comment