Power BI |构建我们的Dynamics GP阶段数据库

阅读时间:6分钟

分享:

在脸书上分享
分享到Twitter
分享在linkedin

[fusion_builder_container background_parallax =“none”enabent_mobile =“no”parallax_speed =“0.3”background_repeat =“no-Repect”background_position =“左上”videa_aspect_ratio =“16:9”video_mute =“是”video_loop =“是”fade =“否“bayer_size =”0px“padding_top =”20“padding_bottom =”20“百分之=”否“等于_height_columns =”no“hide_on_mobile =”no“] [fusion_builder_row] [fusion_builder_column类型=”1_1“layout =”1_1“background_position_position_position_postion_position_position_position_position_“左上角”background_color =“”biders_size =“”bayer_color =“”biders_style =“solid”spacing =“background_repeat =”“background_repeat =”无重复“padding =”“margin_top =”0px“margin_bottom =”0px“class =”“id =”“动画_type =”03“动画=”=“left”leed“hide_on_mobile =”no“center_content =”no“min_height =”none“last =”no“hover_type =”none“链接=“”边界=“全部”] [Fusion_text]我们为设计将源表进入舞台的过程的最大考虑因素是许多动态GP客户在其动态中拥有多家公司S数据库,我们称为实体。要将数据带入临时表,我们需要一个进程来创建可重复使用,可配置和轻松部署的表,因为这是我们GP模板的Power BI模板的第一步。

我们的进程具有以下组件:

1.一个定义实体集合的视图,我们将遍历这些实体来构建我们的舞台。

[/ fusion_text] [/ fusion_builder_column] [fusion_builder_column类型=“1_1”layout =“1_1”last =“yes”spacing =“是”center_content =“no”hide_on_mobile =“no”background_color =“#e0e0e0”background_image =“”background_repeat =“no-feam”background_position =“左上”hover_type =“none”链接=“”border_position =“所有”bayer_size =“0px”biders_color =“”border_style =“solid”padding =“20px”marging_top =“20px“Margin_bottom =”20px“动画_type =”0“动画_direction =”down“动画_peed =”0.1“动画_offset =”class =“”id =“”min_height =“”] [fusion_text]创建视图[dbo]。[dim_entity]
作为
选择Row_Number()Over(通过InterD ASC)作为ID,interniD为[实体ID],CMPnyNam作为实体
dbo。SY01500
在哪里(CMPNYNAM不像“% %”)[/ fusion_text] [/ fusion_builder_column] [fusion_builder_column类型=“1 _1”布局=“1 _1”background_position =“左前”background_color = " " border_size = " " border_color = " border_style =“固体”间距=“是”准确=“background_repeat =“没有重演”填充= " margin_top =“0 px”margin_bottom = " 0 px”类= " 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.保存表所需的一个配置表我们将从每个实体抓取。

对于动态GP,我们从动态数据库的表SY01500中拉动,该表SY01500保存已设置的所有实体。

[/ fusion_text] [/ fusion_builder_column] [/ fusion_builder_row] [/ fusion_builder_container] [fusion_builder_container hundred_percent = " yes "溢出= "明显"][fusion_builder_row] [fusion_builder_column type = " 1 _1”布局=“1 _1”background_position =“左前”background_color = " " border_size = " " border_color = " border_style =“固体”间距=“是”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.三个存储过程。

  • 如果在sys.tables中存在检查所有阶段表的过程,请在sys.Tables中进行检查,并在缺少时创建它们
  • 生成插入语句的过程,该插入语句使用系统模式信息为阶段表生成插入语句。目前,我们将其设置为只处理满载。

[/ fusion_text] [/ fusion_builder_column] [fusion_builder_column类型=“1_1”layout =“1_1”last =“yes”spacing =“是”center_content =“no”hide_on_mobile =“no”background_color =“#e0e0e0”background_image =“”background_repeat =“no-feam”background_position =“左上”hover_type =“none”链接=“”border_position =“所有”bayer_size =“0px”biders_color =“”border_style =“solid”padding =“20px”marging_top =“20px“margin_bottom =”20px“动画_type =”0“动画_direction =”down“动画_peed =”0.1“动画_offset =”class =“”id =“”min_height =“”] [fusion_text] [code]
- - - - - - [3]
从gp_stage中选择column_name.information_schema.tables TB
Inner加入gp_stage.information_schema.columns co on tb.table_name = co.table_name
结核病的地方。Table_type = '基表'
和结核病。TABLE_NAME = @StageTable - ' GL00100 '
[/ code] [/ fusion_text] [/ fusion_builder_column] [fusion_builder_column类型=“1_1”layout =“1_1”background_position =“左上”background_color =“border_size =”“border_color =”“border_style =”solid“spacing =”是” background_image =”” background_repeat =”不重复”填充=”” margin_top =” 0像素” margin_bottom =” 0像素”类=”” ID =”” animation_type =”” animation_speed =” 0.3“animation_direction =”左” hide_on_mobile=“no”center_content =“no”min_height =“none”last =“no”hover_type =“none”链接=“”border_position =“全部”] [fusion_text]

  • 填充阶段表的过程。这是通过交叉连接实体表和配置表并为每个交叉连接的数据集运行插入语句来实现的。除了源表中的所有数据外,我们还引入了Entity的名称。例如,如果你从实体' TWO '中引入表GL10100,就会有一个名为Enity_ID的列,它的值是' TWO '

[/ fusion_text] [/ fusion_builder_column] [fusion_builder_column类型=“1_1”layout =“1_1”last =“yes”spacing =“是”center_content =“no”hide_on_mobile =“no”background_color =“#e0e0e0”background_image =“”background_repeat =“no-feam”background_position =“左上”hover_type =“none”链接=“”border_position =“所有”bayer_size =“0px”biders_color =“”border_style =“solid”padding =“20px”marging_top =“20px“margin_bottom =”20px“动画_type =”0“动画_direction =”down“动画_peed =”0.1“动画_offset =”class =“”id =“”min_height =“”] [fusion_text] [code]
- - - - - - [4]
select d.[实体ID], t.GPTableName, isnull(t.GPTableName, t.GPTableName) as ' StageTableName '
从[dbo]。[dim_entity] d
交叉连接(dbo)。[SETUP_tb_GPTablesToStage] t
t.PullFlag = 1

[/ code] [/ fusion_text] [/ fusion_builder_column] [fusion_builder_column类型=“1_1”layout =“1_1”background_position =“左上”background_color =“border_size =”“border_color =”“border_style =”solid“spacing =”是” background_image =”” background_repeat =”不重复”填充=”” margin_top =” 0像素” margin_bottom =” 0像素”类=”” ID =”” animation_type =”” animation_speed =” 0.3“animation_direction =”左” hide_on_mobile=“no”center_content =“no”min_height =“none”last =“no”hover_type =“none”链接=“”border_position =“全部”] [fusion_text]

对于初始部署,我们创建实体表和包含阶段表名称的配置表,并创建存储过程。在初始设置之后,下图显示了如何将GP数据库中的数据带入该阶段。

[/ fusion_text] [/ fusion_builder_column] [/ fusion_builder_row] [/ fusion_builder_container]

本出版物仅包含一般信息,通过本出版物,Sikich并不提供会计、商业、金融、投资、法律、税务或任何其他专业建议或服务。本出版物不能替代此类专业建议或服务,您也不应将其作为任何可能影响您或您的业务的决定、行动或不作为的依据。在作出任何可能影响你或你的业务的决定、采取任何行动或不采取任何行动之前,你应该咨询合格的专业顾问。您承认,Sikich不会对您或任何依赖本出版物的人所遭受的任何损失负责。

注册洞察188bet188asia

加入14,000多名商业管理人员和决策者

即将来临的事件

最新的见解188bet188asia

关于作者