Sharepoint 2010의 Managed Metadata Service 구성 중 아래와 같은 오류 메시지를 만나게 되면,

아래 방법으로 해결을 해 보세요. 많은 방법들 중 저에게 문제 해결이 되었던 방법입니다.

"The managed Metadata Service or Connection is currently not available. The Application Pool or Managed Metadata Web Service may not have been started. Please Contact your Administrator."

Resolution>

Sharepoint 2010 Management Shell을 통해 Initialize-SPResourceSecurity 명령어를 실행시켜 권한관련 설정을 초기화시킵니다.

위 명령어 실행 후 정상적으로 Term Store Management Tool이 뜨는 것을 확인할 수 있었습니다.

오늘 Review할 Dynamics AX 개발 관련 서적은 "Microsoft Dynamics AX 2012 Development Cookbook"입니다. Cookbook 단어처럼 Dynamics AX 개발에 첫 발을 내딪는 분들께 추천해 드리고 싶은 책입니다.

다양한 예제 중심의 교재로 손쉽게 따라할 수 있는 내용들이 많이 담겨 있고, 또한 업무에 바로 응용할 수 있는 내용들이 많이 담겨 있습니다.

하단에 교재의 목차를 담아 드릴 테니, 관심 있으신분들께서는 구입해서 읽어보시길 추천합니다.

Table of Contents

Preface 1

Chapter 1: Processing Data 7

Introduction 7

Creating a new number sequence 8

Renaming the primary key 13

Merging two records 17

Adding a document handling note 19

Using a normal table as a temporary table 21

Copying a record 22

Building a query object 25

Using a macro in an SQL statement 30

Executing a direct SQL statement 31

Enhancing the data consistency check 37

Exporting data to an XML file 41

Importing data from an XML file 44

Creating a comma-separated value file 46

Reading a comma-separated value file 49

Using the date effectiveness feature 52

Chapter 2: Working with Forms 57

Introduction 57

Creating a dialog 58

Handling a dialog event 63

Building a dynamic form 67

Adding a form splitter 72

Creating a modal form 77

Modifying multiple forms dynamically 79

Storing last form values 82

Using a tree control 85

Building a checklist 97

Adding the View details link 105

Chapter 3: Working with Data in Forms 109

Introduction 109

Using a number sequence handler 110

Creating a custom filter 113

Creating a custom instant search filter 117

Building a selected/available list 120

Preloading images 127

Creating a wizard 134

Processing multiple records 144

Coloring records 146

Adding an image to records 147

Chapter 4: Building Lookups 157

Introduction 157

Creating an automatic lookup 158

Creating a lookup dynamically 161

Using a form for building a lookup 163

Building a tree lookup 169

Displaying a list of custom options 173

Another way of displaying custom options 175

Building a lookup based on record description 179

Building the Browse for Folder lookup 185

Building a lookup for selecting a file 190

Creating a color picker lookup 194

Chapter 5: Processing Business Tasks 201

Introduction 201

Using a segmented entry control 202

Creating a general journal 207

Posting a general journal 215

Processing a project journal 217

Creating and posting a ledger voucher 221

Changing an automatic transaction text 225

Creating a purchase order 228

Posting a purchase order 231

Creating a sales order 236

Posting a sales order 239

Creating an electronic payment format 243

Chapter 6: Integration with Microsoft Office 253

Introduction 253

Creating an Excel file 254

Reading an Excel file 256

Creating a Word document from a template 259

Creating a Word document with repeating elements 262

Creating a Microsoft Project file 266

Sending an e-mail using Outlook 271

Chapter 7: Using Services 275

Introduction 275

Consuming the system query service 276

Consuming the system metadata service 279

Consuming an existing document service 281

Creating a document service 285

Consuming a document service 290

Using an enhanced document service 292

Creating a custom service 298

Consuming a custom service 301

Consuming an external service 303

Chapter 8: Improving Development Efficiency 309

Introduction 309

Creating an editor template 310

Modifying the Tools menu 315

Modifying the right-click context menu 317

Searching for an object in a development project 322

Modifying the Personalization form 325

Modifying the application version 329

Chapter 9: Improving Dynamics AX Performance 333

Introduction 333

Calculating code execution time 334

Writing efficient SQL statements 336

Caching a display method 338

Using Dynamics AX Trace Parser 341

Using SQL Server Database Engine Tuning Advisor 345

Index 349

Dynamics AX의 성능을 올리는 방법 중 하나는 SQL Server에 만들어진 AX Database에 대한 Tuning 을 진행하는 방법도 있습니다. Tuning을 위해 SQL Server에서 제공되는 Analyze Query in Database Engine Tuning Advisor를 실행합니다.

Analyze Query in Database Engine Tuning Advisor를 실행 후 Start Analysis 버튼을 눌러, AX에서 사용 중인 Query에 대한 분석을 진행합니다.

분석이 끝나면, 아래 화면과 같이 Index 생성, Statistics 생성등과 같이, 해당 Query의 성능을 높이기 위한 방법들을 DETA(Database Engine Tuning Advisor)에서 보여줍니다.

Definition 칼럼에서 제공되는 이런 내용들을 Database에 적용 후 다시 DETA를 이용한 분석 진단을 진행해 봅니다.

이런 반복적인 과정을 통해서 Dynamics AX Performance를 높일 수 있습니다.

Analyze Query in Database Engine Tuning Advisor 실행시 아래와 같은 오류가 발생한다면, MSDB에 DTA 관련 Objects들을 생성해 주면 됩니다.

[오류 메시지]

[스크립트 실행]

USE [msdb] GO /****** Object: StoredProcedure [dbo].[sp_DTA_start_xmlprefix] Script Date: 12/31/2008 10:56:11 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_start_xmlprefix] as begin declare @startTags nvarchar(128) set @startTags = N'<DTAXML><DTAOutput><AnalysisReport>' select @startTags end GO /****** Object: Table [dbo].[DTA_input] Script Date: 12/31/2008 10:56:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_input]( [SessionName] [sysname] NOT NULL, [SessionID] [int] IDENTITY(1,1) NOT NULL, [TuningOptions] [ntext] NOT NULL, [CreationTime] [datetime] NOT NULL DEFAULT (getdate()), [ScheduledStartTime] [datetime] NOT NULL DEFAULT (getdate()), [ScheduledJobName] [sysname] NOT NULL DEFAULT (''), [InteractiveStatus] [tinyint] NOT NULL DEFAULT ((0)), [LogTableName] [nvarchar](1280) NOT NULL DEFAULT (''), [GlobalSessionID] [uniqueidentifier] NULL DEFAULT (newid()), PRIMARY KEY CLUSTERED ( [SessionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_progress] Script Date: 12/31/2008 10:57:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_progress]( [ProgressEventID] [int] IDENTITY(1,1) NOT NULL, [SessionID] [int] NULL, [TuningStage] [tinyint] NOT NULL DEFAULT ((0)), [WorkloadConsumption] [tinyint] NOT NULL, [EstImprovement] [int] NOT NULL DEFAULT ((0)), [ProgressEventTime] [datetime] NOT NULL DEFAULT (getdate()), [ConsumingWorkLoadMessage] [nvarchar](256) NULL, [PerformingAnalysisMessage] [nvarchar](256) NULL, [GeneratingReportsMessage] [nvarchar](256) NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_output] Script Date: 12/31/2008 10:57:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_output]( [SessionID] [int] NOT NULL, [TuningResults] [ntext] NOT NULL, [StopTime] [datetime] NOT NULL DEFAULT (getdate()), [FinishStatus] [tinyint] NOT NULL DEFAULT ((0)), PRIMARY KEY CLUSTERED ( [SessionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_tuninglog] Script Date: 12/31/2008 11:01:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_tuninglog]( [SessionID] [int] NOT NULL, [RowID] [int] NOT NULL, [CategoryID] [nvarchar](4) NOT NULL, [Event] [ntext] NULL, [Statement] [ntext] NULL, [Frequency] [int] NOT NULL, [Reason] [ntext] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_database] Script Date: 12/31/2008 10:57:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_database]( [DatabaseID] [int] IDENTITY(1,1) NOT NULL, [SessionID] [int] NOT NULL, [DatabaseName] [sysname] NOT NULL, [IsDatabaseSelectedToTune] [int] NULL, PRIMARY KEY CLUSTERED ( [DatabaseID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_query] Script Date: 12/31/2008 10:59:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_query]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [StatementType] [smallint] NOT NULL, [StatementString] [ntext] NOT NULL, [CurrentCost] [float] NOT NULL, [RecommendedCost] [float] NOT NULL, [Weight] [float] NOT NULL, [EventString] [ntext] NULL, [EventWeight] [float] NOT NULL, CONSTRAINT [DTA_reports_query_pk] PRIMARY KEY CLUSTERED ( [SessionID] ASC, [QueryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_querydatabase] Script Date: 12/31/2008 11:00:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_querydatabase]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [DatabaseID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_table] Script Date: 12/31/2008 11:00:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_table]( [TableID] [int] IDENTITY(1,1) NOT NULL, [DatabaseID] [int] NOT NULL, [SchemaName] [sysname] NOT NULL, [TableName] [sysname] NOT NULL, [IsView] [bit] NOT NULL DEFAULT ((0)), PRIMARY KEY CLUSTERED ( [TableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_partitionfunction] Script Date: 12/31/2008 10:58:57 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_partitionfunction]( [PartitionFunctionID] [int] IDENTITY(1,1) NOT NULL, [DatabaseID] [int] NOT NULL, [PartitionFunctionName] [sysname] NOT NULL, [PartitionFunctionDefinition] [ntext] NOT NULL, PRIMARY KEY CLUSTERED ( [PartitionFunctionID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_partitionscheme] Script Date: 12/31/2008 10:59:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_partitionscheme]( [PartitionSchemeID] [int] IDENTITY(1,1) NOT NULL, [PartitionFunctionID] [int] NOT NULL, [PartitionSchemeName] [sysname] NOT NULL, [PartitionSchemeDefinition] [ntext] NOT NULL, PRIMARY KEY CLUSTERED ( [PartitionSchemeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_tableview] Script Date: 12/31/2008 11:01:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_tableview]( [TableID] [int] NOT NULL, [ViewID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_querytable] Script Date: 12/31/2008 11:00:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_querytable]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [TableID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_index] Script Date: 12/31/2008 10:58:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_index]( [IndexID] [int] IDENTITY(1,1) NOT NULL, [TableID] [int] NOT NULL, [IndexName] [sysname] NOT NULL, [IsClustered] [bit] NOT NULL DEFAULT ((0)), [IsUnique] [bit] NOT NULL DEFAULT ((0)), [IsHeap] [bit] NOT NULL DEFAULT ((1)), [IsExisting] [bit] NOT NULL DEFAULT ((1)), [Storage] [float] NOT NULL, [NumRows] [int] NOT NULL, [IsRecommended] [bit] NOT NULL DEFAULT ((0)), [RecommendedStorage] [float] NOT NULL, [PartitionSchemeID] [int] NULL, [SessionUniquefier] [int] NULL, PRIMARY KEY CLUSTERED ( [IndexID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_column] Script Date: 12/31/2008 10:57:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_column]( [ColumnID] [int] IDENTITY(1,1) NOT NULL, [TableID] [int] NOT NULL, [ColumnName] [sysname] NOT NULL, PRIMARY KEY CLUSTERED ( [ColumnID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_queryindex] Script Date: 12/31/2008 11:00:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_queryindex]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [IndexID] [int] NOT NULL, [IsRecommendedConfiguration] [bit] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_querycolumn] Script Date: 12/31/2008 10:59:50 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_querycolumn]( [QueryID] [int] NOT NULL, [SessionID] [int] NOT NULL, [ColumnID] [int] NOT NULL ) ON [PRIMARY] GO /****** Object: Table [dbo].[DTA_reports_indexcolumn] Script Date: 12/31/2008 10:58:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_reports_indexcolumn]( [IndexID] [int] NOT NULL, [ColumnID] [int] NOT NULL, [ColumnOrder] [int] NULL, [PartitionColumnOrder] [int] NOT NULL DEFAULT ((0)), [IsKeyColumn] [bit] NOT NULL DEFAULT ((1)), [IsDescendingColumn] [bit] NOT NULL DEFAULT ((1)) ) ON [PRIMARY] GO /****** Object: StoredProcedure [dbo].[sp_DTA_check_permission] Script Date: 12/31/2008 10:53:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_check_permission] @SessionID int as begin declare @retcode int declare @dbname nvarchar(128) declare @sql nvarchar(256) declare @dbid int set nocount on -- Check if SA if (isnull(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1) begin return(0) end -- if not yukon return if (patindex('%9.00.%',@@version) = 0) begin return (1) end -- declare and open a cursor and get all the databases specified in the input declare db_cursor cursor for select DatabaseName from DTA_reports_database where SessionID = @SessionID and IsDatabaseSelectedToTune = 1 -- open open db_cursor -- fetch first db name fetch next from db_cursor into @dbname -- loop and get all the databases selected to tune while @@fetch_status = 0 begin -- build use db string select @dbid = DB_ID(@dbname) -- In Yukon this masks the error messages set @sql = N'begin try dbcc autopilot(5,@dbid) WITH NO_INFOMSGS end try begin catch set @retcode = 1 end catch' execute sp_executesql @sql , N'@dbid int output, @retcode int OUTPUT' , @dbid output , @retcode output -- if caller is not member of dbo if (@retcode = 1) begin -- close and reset cursor,switch context to current -- database and return 1 close db_cursor deallocate db_cursor return(1) end fetch from db_cursor into @dbname end -- close and reset cursor,switch context to current -- database and return 1 close db_cursor deallocate db_cursor return(0) end GO /****** Object: StoredProcedure [dbo].[sp_DTA_index_usage_helper_xml] Script Date: 12/31/2008 10:54:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_index_usage_helper_xml] @SessionID int, @IsRecommended int as begin select 1 as Tag, NULL as Parent, '' as [IndexUsageReport!1!!ELEMENT], case when @IsRecommended = 1 then 'false' else 'true' end as [IndexUsageReport!1!Current], NULL as [Database!2!DatabaseID!hide], NULL as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] union all select 2 as Tag, 1 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , NULL as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D where D.SessionID = @SessionID and D.DatabaseID in (select D.DatabaseID from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID and QI.IsRecommendedConfiguration = @IsRecommended GROUP BY D.DatabaseID) union all select 3 as Tag, 2 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , NULL as [Table!4!TableID!hide], NULL as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, ( select D.DatabaseID,T.SchemaName from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and QI.IsRecommendedConfiguration = @IsRecommended and D.SessionID = @SessionID GROUP BY D.DatabaseID,T.SchemaName ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID union all select 4 as Tag, 3 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D.DatabaseID as [Database!2!DatabaseID!hide], D.DatabaseName as [Database!2!Name!ELEMENT] , R.SchemaName as [Schema!3!Name!ELEMENT] , R.TableID as [Table!4!TableID!hide], T.TableName as [Table!4!Name!ELEMENT], NULL as [Index!5!IndexID!hide], NULL as [Index!5!Name!ELEMENT], NULL as [Index!5!NumberOfReferences!ELEMENT], NULL as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D, [msdb].[dbo].[DTA_reports_table] as T, ( select D.DatabaseID,T.SchemaName,T.TableID from [msdb].[dbo].[DTA_reports_queryindex] as QI, [msdb].[dbo].[DTA_reports_index] as I, [msdb].[dbo].[DTA_reports_table] as T, [msdb].[dbo].[DTA_reports_database] as D where QI.IndexID = I.IndexID and I.TableID = T.TableID and T.DatabaseID = D.DatabaseID and D.SessionID = @SessionID and QI.IsRecommendedConfiguration = @IsRecommended GROUP BY D.DatabaseID,T.SchemaName, T.TableID ) R where D.SessionID = @SessionID and D.DatabaseID = R.DatabaseID and R.TableID = T.TableID and T.DatabaseID = D.DatabaseID union all select 5 as Tag, 4 as Parent, NULL as [IndexUsageReport!1!!ELEMENT], NULL as [IndexUsageReport!1!Current], D1.DatabaseID as [Database!2!DatabaseID!hide], D1.DatabaseName as [Database!2!Name!ELEMENT] , T1.SchemaName as [Schema!3!Name!ELEMENT] , T1.TableID as [Table!4!TableID!hide], T1.TableName as [Table!4!Name!ELEMENT], I1.IndexID as [Index!5!IndexID!hide], I1.IndexName as [Index!5!Name!ELEMENT], R.Count as [Index!5!NumberOfReferences!ELEMENT], CAST(R.Usage as decimal(38,2)) as [Index!5!PercentUsage!ELEMENT] from [msdb].[dbo].[DTA_reports_database] as D1 , [msdb].[dbo].[DTA_reports_index] as I1, [msdb].[dbo].[DTA_reports_table] as T1, ( select D.DatabaseID,T.TableID , I.IndexID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_index] as I LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QI.SessionID and QI.IsRecommendedConfiguration = @IsRecommended and Q.SessionID = @SessionID GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.IndexID = I1.IndexID and D1.SessionID = @SessionID and R.Count > 0 order by [Database!2!DatabaseID!hide],[Schema!3!Name!ELEMENT],[Table!4!TableID!hide], [Index!5!NumberOfReferences!ELEMENT] , [Index!5!IndexID!hide] FOR XML EXPLICIT end GO /****** Object: StoredProcedure [dbo].[sp_DTA_index_usage_helper_relational] Script Date: 12/31/2008 10:54:26 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_index_usage_helper_relational] @SessionID int, @IsRecommended int as begin select D1.DatabaseName as 'Database Name' ,T1.SchemaName as 'Schema Name' ,T1.TableName as 'Table/View Name' ,I1.IndexName as 'Index Name' ,R.Count as 'Number of references' ,CAST(R.Usage as decimal(38,2)) as 'Percent Usage' from DTA_reports_database as D1 , DTA_reports_index as I1, DTA_reports_table as T1, ( select D.DatabaseID,T.TableID , I.IndexID ,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_index] as I LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_queryindex] as QI ON QI.IndexID = I.IndexID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QI.QueryID = Q.QueryID JOIN [msdb].[dbo].[DTA_reports_table] as T ON I.TableID = T.TableID JOIN [msdb].[dbo].[DTA_reports_database] as D ON T.DatabaseID = D.DatabaseID and Q.SessionID = QI.SessionID and QI.IsRecommendedConfiguration = @IsRecommended and Q.SessionID = @SessionID GROUP BY I.IndexID,T.TableID,D.DatabaseID) as R where R.DatabaseID = D1.DatabaseID and R.TableID = T1.TableID and R.IndexID = I1.IndexID and D1.SessionID = @SessionID and R.Count > 0 order by R.Count desc end GO /****** Object: StoredProcedure [dbo].[sp_DTA_database_access_helper_xml] Script Date: 12/31/2008 10:53:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_database_access_helper_xml] @SessionID int as begin select 1 as Tag, NULL as Parent, '' as [DatabaseAccessReport!1!!ELEMENT], NULL as [Database!2!Name!ELEMENT] , NULL as [Database!2!NumberOfReferences!ELEMENT], NULL as [Database!2!PercentUsage!ELEMENT] union all select 2 as Tag, 1 as Parent,NULL,D1.DatabaseName , R.Count , CAST(R.Usage as decimal(38,2)) from [msdb].[dbo].[DTA_reports_database] as D1 , ( select D.DatabaseID,SUM(Q.Weight) as Count, 100.0 * SUM(Q.Weight) / ( 1.0 * ( select CASE WHEN SUM(Q.Weight) > 0 THEN SUM(Q.Weight) else 1 end from [msdb].[dbo].[DTA_reports_query] as Q where Q.SessionID = @SessionID )) as Usage from [msdb].[dbo].[DTA_reports_database] as D LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_querydatabase] as QD ON QD.DatabaseID = D.DatabaseID LEFT OUTER JOIN [msdb].[dbo].[DTA_reports_query] as Q ON QD.QueryID = Q.QueryID and Q.SessionID = QD.SessionID and Q.SessionID = @SessionID GROUP BY D.DatabaseID ) as R where R.DatabaseID = D1.DatabaseID and D1.SessionID = @SessionID and R.Count > 0 order by Tag,[Database!2!NumberOfReferences!ELEMENT] desc FOR XML EXPLICIT end GO

DTA_Objects_SQL.txt

Dynamics AX Trace Parser는 AOS Server와 Client간의 User Session, Call Trees, SQL Statements, Execution durations과 같은 정보를 모아 분석할 수 있는 환경을 제공합니다.

Trace 파일 생성을 위해 아래와 같이 CusTransTracing Class와 Main Method를 작성했습니다.

Trace 파일 수집을 위해서 Tools > Tracing cockpit 메뉴를 실행합니다.

[Tracing cockpit 화면]

Start trace 버튼을 눌러 Trace 파일이 저장될 공간과 이름을 C:\Temp\trace.etl 파일로 지정합니다.

이후 CustTransTracing Class를 실행시킨 후 Stop Trace 버튼을 눌러 Trace를 종료합니다.

Open Trace 버튼을 눌러 Microsoft Dynamics AX Trace Parser 프로그램을 띄웁니다. AX Trace Parser는 아래 그림과 같이 MS Dynamics AX 2012 프로그램 그룹안에서 실행할 수도 있습니다.

[Microsoft Dynamics AX Trace Parser 실행 화면]

MS Dynamics AX Trace Parser를 이용하면 개발되어 실행되는 모듈의 Class 정보, Class를 통해서 실행되는 SQL 문과 실행시간 등을 확인할 수 있습니다. 이렇게 수집된 자료를 분석하면, AX Performance를 높일 수 있는 Tuning Point를 발견해 성능 개선을 해 나갈 수 있습니다.

Organization administration > Setup > E-mail templates에 들어가 메일 전송에 사용될 새로운 Template을 작성합니다.

E-Mail message 버튼을 눌러 아래와 같이 본문 내용을 작성합니다.

Project management and accounting > Common > Projects > All Projects 위치에 있는 프로젝트 중 하나를 열어 Plan > Forecast > Hours forecasts 메뉴의 Action Pane의 Hour forecasts에 들어가 아래와 같이 몇 개의 항목을 만듭니다.

Hour forecast 작성 후 작성된 스케쥴의 갱신을 위해서 Scheduling > Scheduling 을 실행시킵니다.

그리고, 아래 코드를 작성해 실행시키면 Project 스케쥴링과 관련된 내용이 MS Project 파일로 그대로 옮겨와 지는 것을 볼 수 있습니다.

[결과화면]

이번에는 Word 표에 LedgerParameters 테이블의 정보를 나열하는 로직을 구현하려고 합니다.

작업을 위해서 TableName이라는 책갈피를 추가해 주시고, 아래와 같이 행이 2개인 표를 추가한 table.dotx 서식 파일을 C:\Temp 경로에 저장합니다.

아래 코드를 이용해 위에서 작업한 서식 파일(table.dotx)에 LedgerParameter 테이블의 필드와 값을 보여지도록 할 수 있습니다.

[결과화면]

table.dotx