[fusion_builder_container background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ background_repeat=”no-repeat” background_position=”left top” video_aspect_ratio=”16:9″ video_mute=”yes” video_loop=”yes” fade=”no” border_size=”0px” padding_top=”20″ padding_bottom=”20″ hundred_percent=”no” equal_height_columns=”no” hide_on_mobile=”no”][fusion_builder_row][fusion_builder_column type=”1_1″ layout=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none” last=”no” hover_type=”none” link=”” border_position=”all”][fusion_text]Our biggest consideration for designing the process to pull source tables into stage was that many Dynamics GP customers have multiple companies in their Dynamics database, which we refer to as entities. To bring data into staging tables, we needed a process to create tables that was reusable, configurable and easily deployed, since this is the first step of our GP template for Power BI.
Our process has the following components:
1. One view to define the collection of entities that we will be looping through to build our stage.
[/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_1″ layout=”1_1″ last=”yes” spacing=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#e0e0e0″ background_image=”” background_repeat=”no-repeat” background_position=”left top” hover_type=”none” link=”” border_position=”all” border_size=”0px” border_color=”” border_style=”solid” padding=”20px” margin_top=”20px” margin_bottom=”20px” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=”” min_height=””][fusion_text]CREATE VIEW [dbo].[Dim_Entity]
AS
SELECT ROW_NUMBER() OVER(ORDER BY INTERID ASC) AS ID, INTERID AS [Entity ID], CMPNYNAM AS Entity
FROM dbo.SY01500
WHERE (CMPNYNAM NOT LIKE ‘%%’)[/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_1″ layout=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none” last=”no” hover_type=”none” link=”” border_position=”all”][fusion_text]
2. One configuration table needed to hold the tables we will be grabbing from each entity.
For Dynamics GP we pull from the Dynamics Database’s table SY01500, which holds all the entities that are set up.
[/fusion_text][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container][fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ layout=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none” last=”no” hover_type=”none” link=”” border_position=”all”][fusion_text]
3. Three stored procedures.
- A procedure to do a check if all the stage tables exist in sys.tables and create them if they are missing
- A procedure to generate the insert statements that use system schema information to generate insert statements for the stage tables. Currently, we have it set up to handle a full load only.
[/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_1″ layout=”1_1″ last=”yes” spacing=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#e0e0e0″ background_image=”” background_repeat=”no-repeat” background_position=”left top” hover_type=”none” link=”” border_position=”all” border_size=”0px” border_color=”” border_style=”solid” padding=”20px” margin_top=”20px” margin_bottom=”20px” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=”” min_height=””][fusion_text][code]
–[3]
select COLUMN_NAME from GP_Stage.INFORMATION_SCHEMA.tables tb
inner join GP_Stage.INFORMATION_SCHEMA.COLUMNS co on tb.TABLE_NAME = co.TABLE_NAME
where tb.TABLE_TYPE = ‘BASE TABLE’
and tb.TABLE_NAME = @StageTable –‘GL00100’
[/code][/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_1″ layout=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none” last=”no” hover_type=”none” link=”” border_position=”all”][fusion_text]
- A procedure to populate the stage tables. This is accomplished by cross joining the Entity table and the configuration table and running the insert statements for each of the cross joined dataset. In addition to all the data from the source table, we also bring in the name of the Entity. For example, if you brought in the table GL10100 from the entity ‘TWO’, there would be a column called Enity_ID that is filled with the value ‘TWO’
[/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_1″ layout=”1_1″ last=”yes” spacing=”yes” center_content=”no” hide_on_mobile=”no” background_color=”#e0e0e0″ background_image=”” background_repeat=”no-repeat” background_position=”left top” hover_type=”none” link=”” border_position=”all” border_size=”0px” border_color=”” border_style=”solid” padding=”20px” margin_top=”20px” margin_bottom=”20px” animation_type=”0″ animation_direction=”down” animation_speed=”0.1″ animation_offset=”” class=”” id=”” min_height=””][fusion_text][code]
–[4]
select d.[Entity ID], t.GPTableName, isnull(t.StageTableName,t.GPTableName) as ‘StageTableName’
from [dbo].[DIM_ENTITY] d
cross join [dbo].[SETUP_tb_GPTablesToStage] t
where t.PullFlag = 1
[/code][/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_1″ layout=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none” last=”no” hover_type=”none” link=”” border_position=”all”][fusion_text]
For our initial deployment, we create the entity table and the configuration table that holds the names of our stage tables and create the stored procedure. After the initial setup, the diagram below shows how data will be brought from the GP database into the stage.
[/fusion_text][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]
