A cube is defined by
its measures and dimensions.
A measure is a
column in a table/ view that contains quantifiable data, usually numeric that
can be aggregated
- like total no. of
items sold/ profit/ revenue. A cube contains 1 or
more measure.
Attributes are
fields on table/ view .Dimensions are group of attributes.
The cube we create
will allow us to analyze the data in Table format .
Prerequisites
- Dynamics AX 2012
- Dynamics AX 2012 Analysis Services
- SQL Server 2012
Once you have identified the tables and views which contain the relevant data for your BI report, you can begin creating perspectives (cubes) in the AOT. Let’s suppose we have to develop a sales report for which the following tables/views contain the relevant data:
CUSTTRANSTOTALSALES view
CUSTTABLECUBE view
CUSTPAYMMODETABLE table
Creating Cube
Follow these steps to create a new perspective in the AOT: 
1. Expand Data Dictionary/Perspectives node
2. Create a new perspective and give it a suitable name
3. Set the USAGE property to OLAP
4. Drag the relevant tables and views
Defining Measures and Dimensions
Next step is to define measures and dimensions. Expand CUSTTRANSTOTALSALES view and set the field properties in the AOT as follows:
Repeat the above step to specify measures and dimensions for other tables/views in your perspective as per your report requirements.
Generate Project
Click Tools > Business Intelligence (BI) tools > SQL Server Analysis Services project wizard. Create a new project and give a suitable name as shown below:
On the next screen, select your newly created perspective:
The next couple of screens offer optional features which include adding financial, date dimensions, language selection and currency conversion. The wizard will generate the project:
Its up to you whether you save the project on disk or in the AOT.
Deploy cube
Next, the wizard will prompt you to choose the partitions to deploy the cube in if multiple partitions exist in the database environment. You can either to choose to process the cube using this wizard by ticking the checkbox or process the cube later in the SQL Server Management Studio.
Below screen - you can find based on no. of partitions of your DB .
Go to >> System Administrator >> Setup >> Business intelligence>> Analysis servers >> Analysis server
based on no. of records ( Partition keys) we get the same no.of records here.
Process cube
Open SQL Server Management Studio, connect to Analysis Services. In the Object Explorer, you should be seeing now your newly created cube:
Right click your cube and click Process. Make sure the user has sufficient rights to process the cube otherwise you would have to provide Impersonation information in the Security settings of the database properties of your cube. The wizard will successfully process the cube.
This ends the process of creating cubes! Now you can make MDX queries, build reports on top of your cubes. One such report that I created using SQL Server 2012 Power View plugin looks like below in the role center:
No comments:
Post a Comment