SQL Server Query Optimization- Execution Plan

 clipped from www.sql-server-performance.com

SQL Server Query Execution Plan Analysis

By : Brad McGehee Apr 04, 2006

When it comes time to analyze the performance of a specific query, one of the best methods is to view the query execution plan. A query execution plan outlines how the SQL Server query optimizer actually ran (or will run) a specific query. This information if very valuable when it comes time to find out why a specific query is running slow.

There are several different ways to view a query's execution plan. They include:

• From within Query Analyzer is an option called "Show Execution Plan" (located on the Query drop-down menu). If you turn this option on, then whenever you run a query in Query Analyzer, you will get a query execution plan (in graphical format) displayed in a separate window.
• If you want to see an execution plan, but you don't want to run the query, you can choose the option "Display Estimated Execution Plan" (located on the Query drop-down menu). When you select this option, immediately an execution plan (in graphical format) will appear. The difference between these two (if any) is accountable to the fact that when a query is really run (not simulated, as in this option), current operations of the server are also considered. In most cases, plans created by either method will produce similar results.
• When you create a SQL Server Profiler trace, one of the events you can collect is called: MISC: Execution Plan. This information (in text form) shows the execution plan used by the query optimizer to execute the query.
• From within Query Analyzer, you can run the command SET SHOWPLAN_TEXT ON. Once you run this command, any query you execute in this Query Analyzer sessions will not be run, but a text-based version of the query plan will be displayed. If the query you are running uses temp tables, then you will have to run the command, SET STATISTICS PROFILE ON before running the query.

Of these options, I prefer using the "Show Execution Plan", which produces a graphical output and considers current server operations. [7.0, 2000] Updated 8-5-2005

*****

If you see any of the following in an execution plan, you should consider them warning signs and investigate them for potential performance problems. Each of them are less than ideal from a performance perspective.

• Index or table scans: May indicate a need for better or additional indexes.
• Bookmark Lookups: Consider changing the current clustered index, consider using a covering index, limit the number of columns in the SELECT statement.
• Filter: Remove any functions in the WHERE clause, don't include wiews in your Transact-SQL code, may need additional indexes.
• Sort: Does the data really need to be sorted? Can an index be used to avoid sorting? Can sorting be done at the client more efficiently?

It is not always possible to avoid these, but the more you can avoid them, the faster query performance will be. [7.0, 2000, 2005] Updated 8-5-2005

*****

If you have a stored procedure, or other batch Transact-SQL code that uses temp tables, you cannot use the "Display Estimated Execution Plan" option in the Query Analyzer or Management Studio to evaluate it. Instead, you must actually run the stored procedure or batch code. This is because when a query is run using the "Display Estimated Execution Plan" option, it is not really run, and temp tables are not created. Since they are not created, any references to them in the code will fail, which prevents an estimated execution plan from being created.

On the other hand, if you use a table variable instead of a temp table, you can use the "Display Estimated Execution Plan" option [7.0, 2000, 2005] Updated 8-5-2005

*****

If you have a very complex query you are analyzing in Query Analyzer or Management Studio as a graphical query execution plan, the resulting plan can be very difficult to view and analyze. You may find it easier to break down the query into its logical components, analyzing each component separately. [7.0, 2000, 2005] Updated 8-5-2005

*****

The results of a graphical query execution plan are not always easy to read and interpret. Keep the following in mind when viewing a graphical execution plan:

• In very complex query plans, the plan is divided into many parts, with each part listed one on top of the other on the screen. Each part represents a separate process or step that the query optimizer has to perform in order to get to the final results.
• Each of the execution plan steps is often broken down into smaller sub-steps. Unfortunately, they are displayed on the screen from right to left. This means you must scroll to the far right of the graphical query plan to see where each step starts.
• Each of the sub-steps and steps is connected by an arrow, showing the path (order) taken of the query when it was executed.
• Eventually, all of the parts come together at the top left side of the screen.
• If you move your cursor above any of the steps or sub-steps, a pop-up windows is displayed, providing more detailed information about this particular step or sub-step.
• If you move your cursor over any of the arrows connecting the steps and sub-steps, you see a pop-up window showing how many records are being moved from one step or sub-step to another step or sub-step.

Get Clipmarks - The easiest way to email text, images and videos you find on the web.

Cross Apply in SQL Server

Source Text at

A Visual Explanation of SQL Joins

I thought Ligaya Turmelle's post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set-based, the use of Venn diagrams to explain them seems, at first blush, to be a natural fit. However, like the commenters
to her post, I found that the Venn diagrams didn't quite match the

SQL join syntax
reality in my testing.

I love the concept, though, so let's see if we can make it work. Assume we have the following two tables. Table A is on the left, and
Table B is on the right. We'll populate them with four records each.

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate

3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.

SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 3 Ninja 4 Ninja Inner join produces only the set of records that match in both Table A and Table B.
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name id name id name -- ---- -- ---- 1 Pirate 2 Pirate 2 Monkey null null 3 Ninja 4 Ninja 4 Spaghetti null null Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null id name id name -- ---- -- ---- 2 Monkey null null 4 Spaghetti null null null null 1 Rutabaga null null 3 Darth Vader To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

There's also a cartesian product or cross join, which as far as
I can tell, can't be expressed as a Venn diagram:

SELECT * FROM TableA
CROSS JOIN TableB

This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we
had in the original sets. If you do the math, you can see why this is a very
dangerous join to run against large tables.

Database Snapshots and SSIS

clipped from blogs.conchango.com

Database Snapshots and SSIS

Did you think SQL Server 2005 database snapshots were really cool, but wondered how useful they were in practice? Building a Data Warehouse, here's an ideal use for them:

In my current project we're extracting data for the data warehouse from several SQL Server 2005 source system databases. Since we're extracting from multiple tables from each database, and since those tables continue to be populated during our extract, there is a definite risk that the extracted data is out of sync due to the extracts from different tables happens at slightly different times.

Database snapshots to the rescue

By first creating a snapshot of the source system database, and extracting from that static snapshot (instead of the original, changing database), these synchronization issues are avoided!

Even better, while the underlying database might be modified during the extract, the overhead for creating and using the snapshot is very small - it's proportional to the number of source system table pages changed during the extract.

Each source system database to be extracted has the following tasks configured:

1. The first Execute SQL Task creates the snapshot. Since no data is copied, this is a virtually instantaneous operation. At this stage the source system Connection Manager still points to the original database.

2. The first Script Task switches the database ConnectionString from the original database to the newly created snapshot database. This avoids having to create and keep a second connection manager in sync with the original database.

3. The Sequence Container contains all the Data Flow tasks etc. needed to move the source system data (now guaranteed to be static during the extract) into the data warehouse. NB: the following precedence constraints are "On Completion", so even if the extract fails, the snapshot will be deleted.

4. The second Script Task switches the ConnectionString back to the original database, i.e. almost same code as 2 above, but change "Source_System_Snapshot_Extract" into "Source_System", and vice versa.

5. The final Execute SQL Task simply drops the snapshot, again an instantaneous operation.

Wrapping up

One thing to watch out for is when additional database files are created, either manually by the DBA or automatically due to the source system having a maximum file size set. The snapshot creation command must specify ALL database files, so either update the snapshot command manually when this happens, or extend the Script Tasks to automatically figure out the number and filenames required.

In summary, database snapshots and SSIS have proved to be a very useful combination for extracting consistent data into the data warehouse.

Published 06 March 2006 18:45 by Kristian.Wedberg

Get Clipmarks - The easiest way to email text, images and videos you find on the web.

MDX at First Glance: Introduction to SQL Server MDX Essentials

www.databasejournal.com

MDX at First Glance: Introduction to SQL Server MDX Essentials

---

Unlock data warehouses with the analytical language of OLAP. Join Author Bill Pearson in the first of a new series of tutorials designed to get you up and running with the fundamentals of Multidimensional Expressions (MDX).

About the Series...

This is the first article of my new series, MDX Essentials. The primary focus of this series will be an introduction to the MDX language. The series is designed to provide hands-on application of the fundamentals of the Multidimensional Expressions (MDX) language, with each tutorial progressively adding features designed to meet specific real-world needs.

As we progress through the series, we will build upon previous lessons and the concepts we have introduced therein. However, one of my objectives is to make each lesson as "standalone" as possible, meaning that we should not encounter cases where we cannot complete a given lesson without components or objects that we have created in previous lessons. This should make it easier for "casual" visitors to join us with any lesson, and still successfully complete that session, given an existing understanding of concepts and principles that we have accumulated up to that point.

What We Will Need to Complete the Tutorials

To get the most out of the MDX Essentials series, we need to have installed at least the Analysis Services component of MSSQL Server 2000. While the full installation of SQL Server 2000 allows for virtually any exercise we might undertake, the majority of our sessions center on Analysis Services, the PivotTable Service, and their constituent parts. Installation of Analysis Services from the Standard Edition of MSSQL Server 2000 will be adequate for the vast majority of our activities.

For purposes of carrying out limited Microsoft Office -- related activities, Microsoft Excel 2000 and, to a lesser extent, Microsoft FrontPage 2000 will come in handy. We will also make use of the Microsoft OLAP Provider, included in a typical Excel 2000 installation, which consists of the data source driver and the client software needed to access cubes created by Microsoft SQL Server 2000 Analysis Services.

For purposes of the series, it is assumed that MSSQL Server 2000 and, specifically, the MSSQL Server 2000 Analysis Services components (I will often substitute the term "Analysis Services" going forward, to save time and space) are accessible to/installed on the PC, with the appropriate access rights to the sample cubes provided in a Typical installation of Analysis Services. It is also assumed that the computer(s) involved meet the system requirements, including hardware and operating systems, of the applications we have mentioned.

www.databasejournal.com

Introduction to MDX: The First Blush

This initial tutorial will introduce the MDX query in its simplest form. We'll take a look at some of the basic keywords, focusing only on simple queries, upon which we will build on in later lessons. Beginning in our first lesson, and throughout most of our series, we will discuss the element(s) of the language under consideration and then perform practice activities, with meaningful examples, to reinforce the concepts we have introduced. In this session, we will explore the rudiments of MDX queries within their simplest contexts (syntax) and introduce terms (semantics) that are applicable as they arise. This lesson will include:

• A brief introduction to MDX;
• A discussion of the basic keywords commonly used in MDX;
• A breakdown of a simple MDX query into its component parts;
• Brief comparisons and contrasts of MDX to SQL where useful;
• Other introductory items.

Let's begin by creating and executing a basic MDX query that will provide us a tangible starting point for discussing keywords and components.

Multidimensional Expressions (MDX) as a Language

MDX emerged circa 1998, when it first began to appear in commercial applications. MDX was created to query OLAP databases, and has become widely adopted within the realm of analytical applications. MDX forms the language component of OLE DB for OLAP, and was designed by Microsoft as a standard for issuing queries to, and exchanging data with, multidimensional data sources. The language is rapidly gaining the support of many OLAP providers, and this trend is expected to continue.

The focus of the MDX Essentials series will be MDX as implemented in MSSQL 2000 Analysis Services. MDX acts in two capacities within Analysis Services: as an expression language that is used to calculate values, and as a query language that is accessed and used by client applications to retrieve data. We will address aspects of both perspectives throughout the series.

The Basic MDX Query

Let's begin by creating a rudimentary query using the MDX Sample Application. The Sample Application is installed with Analysis Services and allows us to create an MDX statement, execute it, and see the results in a grid-like results pane. We'll start the application and proceed, taking the following steps:

1. Go to the Start button on the PC, and then navigate to Microsoft SQL Server -> Analysis Services, then to the MDX Sample Application.
2. We are initially greeted by the Connect dialog, shown in Illustration 1 below.

Illustration 1: The Connect Dialog for the MDX Sample Application (Enlarge)

The illustration above depicts the name of one of my servers, RAPHAEL, and properly indicates that we will be connecting via the MSOLAP provider (the default).

3. Click OK.

(We might also choose to cancel the dialog box and connect later by clicking Connect on the File menu.)

The MDX Sample Application window appears.

4. Clear the top area (the Query pane) of any remnants of queries that might appear.
5. Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.
6. Select the Warehouse cube in the Cube drop-down list box.

The MDX Sample Application window should resemble that shown below, complete with the information from the Warehouse cube displaying in the Metadata tree (in the left section of the Metadata pane between the Query pane at the top of the application window and the Results pane at the bottom.).

Illustration 2: The MDX Sample Application Window (Compressed)

We type our MDX statements into the Query pane, as we shall see. Above the Query pane are the menu bar and toolbar. The Metadata pane allows us to visually inspect structural information about the selected cube. Syntax examples on the right side of this middle pane assist us in the creation of our statements. The Results pane at the bottom of the application window presents the output of our MDX queries. We will discuss various attributes of the MDX Sample Application where they are relevant to the exercises we undertake, but it is highly useful to explore the Books Online for a wealth of detail about the application.

We will begin with an example: We are asked by an information consumer to provide the total sales and total cost amounts for the years 1997 and 1998 individually for all USA-based stores (including all products). We are asked, moreover, to provide the information in a two-dimensional grid, with the sales and cost amounts (called measures in our data warehouse) in the rows and the years (1997 and 1998) in the columns.

1. Type the following query into the Query pane:

   --MDX01-1:  Basic Query SELECT {[Time].[1997],[Time].[1998]}ON COLUMNS, {[Measures].[Warehouse Sales],[Measures].[Warehouse Cost]}  ON ROWS FROM Warehouse WHERE  ([Store].[All Stores].[USA]) 

The diagram below labels the various parts of the query:

Illustration 3: Labeled Parts of a Basic MDX Query

The following general discussion items apply to the syntax above, and to MDX queries in general:

• The top line of the query is a comment. We will discuss comments later in the series, but suffice it to say for now that the two dashes (--) represent one of three typical ways to place a comment in MDX syntax, so that it is ignored when the MDX is parsed.
  I introduce this at the present stage because I like to "label" queries in this way as I create them, so as to make them easy to identify for reuse or review. This is particularly handy when using the Sample Application, because the application displays the initial characters of the query in the dropdown selector (labeled "Query:") to the right of the database ("DB:") selector in the toolbar. Selection of a given query from a query file is easy, therefore, given the use of intuitive names/descriptions in the top line of the syntax.
  
  

• The cube that is targeted by the query (the query scope) appears in the FROM clause of the query. The FROM clause in MDX works much as it does in SQL (Structured Query Language), where it stipulates the tables used as sources for the query.
  


• The query syntax also uses other keywords that are common in SQL, such as SELECT and WHERE. Even though there are apparent similarities in the two languages, there are also significant differences. A prominent difference is that the output of an MDX query, which uses a cube as a data source, is another cube, whereas the output of an SQL query (which uses a columnar table as a source) is typically columnar.
  It is important to realize that MDX's cube output allows us to place any dimension from the source cube onto any axis of the query's result cube. Many axes can exist, and it is often better to think in terms of "axes" than in "dimensions" (as is quite common among both developers and information consumers) when designing an MDX query. This is for two main reasons: The "axes" concept allows for distinction between the source dimensions and the apparent result cube dimensions, which may be very different indeed. Another reason is that a given axis can contain a number of cube dimensions in combination. Axis references are therefore more precise, and less subject to misinterpretation.
  
  

• A query has one or more axes. The query above has two. (The first three axes that are found in MDX queries are known as rows, columns and pages.) We stipulate the axes above through our use of the "columns" and "rows" specifications. Keep in mind that columns always come before rows, and rows always precede pages, within the query.
  


• Curled brackets "{}" are used in MDX to represent a set of members of a dimension or group of dimensions. The query above has one dimension each on the two query axes. The dimensions that appear are the Measures and Time dimensions.
  


• We can display more than one dimension on a result axis. When we do this, an "intersection" occurs, in effect, and each cell appearing in the associated axis relates to the combination of a member from each of the indicated dimensions. When more than one dimension is mapped onto an axis, the axis is said to consist of "tuples," containing the members of each of the mapped dimensions.
  


• Dimensions that are not specified within the axes of a query will have members specified by default; we can also stipulate such members in the WHERE clause, as shown in our query above.
  

2. Click the Run Query button (the button sporting the green, arrowhead-shaped icon -- a tool tip will alight when the cursor is placed upon the button to positively identify it for us).

We see the results below, which appear as soon as Analysis Services fills the cells that it determines to be specified by the query.

Illustration 4: The Initial Query Results

2. Save the query by selecting File -> Save As and call the file MDX01-1, as shown in the illustration below.

Illustration 5: Saving the MDX Query via the Save As Dialog

Note: I typically prefer to save files to a context-oriented directory/folder (for example a folder I have created for a client for whom I am writing MDX queries as a part of an engagement, or for a presentation I am assembling). This is obviously a point of personal taste; the objective is simply to keep track of where the queries are, so that we can find them in time of need. Much rewriting and confusion between altered versions can be avoided by storing the queries in a logical system of some sort to keep organized. My favorite way to do this is to create a database within which to store the query strings, together with descriptions, author and keyword information, along with date time data, "version" information, and other specifics, if applicable.

Let's create another query to conclude this introductory session. This time, let's say that information consumers have asked for a comparison between the total US warehouse sales for the first and second quarters of 1997. We will again create a query against our Warehouse cube to generate this information.

1. Click the New Query button (depicted in the illustration below).

Illustration 6: The New Query Button

We might also have selected File -> New from the top menu.

2. Type the following query into the Query pane:

   --MDX01-2:  Basic Query 2  SELECT  {[Time].[1997].[Q1],[Time].[1997].[Q2]}ON COLUMNS,  {[Warehouse].[All Warehouses].[USA]}  ON ROWS  FROM Warehouse  WHERE  ([Measures].[Warehouse Sales]) 

Because we have specified the Warehouse Sales measure in the WHERE statement, we have made it the slicer dimension. The slicer shows that we have picked only the Warehouse Sales measure from the measures dimension. We will work with slicer dimensions, as well as with the other components of the simple queries we have examined in this lesson (and far more), as we progress through the MDX Essentials series.

3. Click Query on the top menu, then select Run, as shown below:

Illustration 7: Select Run from the Query Menu

Alternatively, F5 or the Run Query button might be selected for the same effect.

We see the results below, which appear as soon as Analysis Services fills the cells specified by the query.

Illustration 8: The Query Results

4. Save the query as MDX01-2.
5. Exit the Sample Application.

Our intent with the above examples is to begin our exploration of MDX and to provide a first exposure to simple query structure. We will cover the details of the syntax and its arrangement, and a host of other considerations, as we progress through the series.

clipped from www.databasejournal.com

Next in Our Series...

With this tutorial article, MDX at First Glance: Introduction to MDX Essentials, we began the new MDX Essentials Series. Our objective in this lesson was to introduce the MDX query in its simplest form. We took a look at some of the basic keywords, focusing only on simple queries, as a basis upon which to build in later lessons. In this lesson, we began a discussion of the elements of the MDX language that will carry forward as we progress through the series, and then performed practice activities, as we will do throughout the entire MDX Essentials series, to reinforce the concepts we introduce.

We explored the rudiments of MDX queries within their simplest contexts (syntax), and introduced several terms (semantics) that were applicable as they arose. We provided a brief introduction to MDX, and then discussed several basic keywords commonly used in MDX. We examined a breakdown of a simple MDX query into its component parts, comparing and contrasting MDX to SQL where useful. Finally, we discussed other relevant introductory keywords and components throughout the lesson as part of creating and executing basic MDX queries.

In our next lesson, Structure of the MDX Data Model, we will introduce the MDX data model, together with numerous of its components. These components will include cubes, dimensions, and several other terms we have already exposed. We will focus on the composition and use of tuples and sets in detail, and provide hands-on exposure to these building blocks. Rules of syntax will be emphasized, and will provide a basis for more complex query building later in the series. Finally, we will step through practice exercises to demonstrate tangible results to reinforce our discussions with examples.

See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

MDX Essentials Series

Article 1: MDX at First Glance: Introduction to MDX Essentials
Article 2: Structure of the MDX Data Model
Article 3: MDX Operators: The Basics
Article 4: MDX Members: Introducing Members and Member
Article 5: MDX Member Functions: The "Family" Functions
Article 6: MDX Member Functions: More "Family" Functions
Article 7: MDX Member Functions: The Cousin () Function
Article 8: MDX Member Functions: "Relative" Member Functions
Article 9: MDX Time Series Functions, Part I: PeriodsToDate() and Kindred Functions
Article 10: MDX Time Series Functions, Part II: The OpeningPeriod () and ClosingPeriod() Functions
Article 11: MDX Time Series Functions, Part III: The LastPeriods() and ParallelPeriod() Functions
Article 12: Basic Set Functions: The Order() Function
Article 13: Basic Set Functions: The Union() Function
Article 14: Basic Set Functions: The Intersect() Function
Article 15: Basic Set Functions: The EXCEPT() Function
Article 16: Basic Set Functions: The Filter() Function
Article 17: Basic Numeric Functions: The Count() Function
Article 18: Basic Set Functions: The CrossJoin() Function
Article 19: Basic Set Functions: Subset Functions: The Head() Function
Article 20: Basic Set Functions: Subset Functions: The Tail() Function
Article 21: Basic Set Functions: Subset Functions: The Subset() Function
Article 22: Basic Member Functions: The .Item() Function
Article 23: Numeric Functions: Introduction to the AVG() Function
Article 24: Basic Set Functions: The EXTRACT() Function
Article 25: Logical Functions: The IsEmpty() Function
Article 26: String / Numeric Functions: Introducing the IIF() Function
Article 27: String / Numeric Functions: More on the IIF() Function
Article 28: The CROSSJOIN() Function: Breaking Bottlenecks
Article 29: Set and String Functions: The GENERATE() Function
Article 30: Enhancing CROSSJOIN() with Calculated Members
Article 31: Basic Set Functions: The TopCount() Function, Part I
Article 32: Basic Set Functions: The TopCount() Function, Part II
Article 33: String / Numeric Functions: The CoalesceEmpty() Function
Article 34: String Functions: The .Name Function
Article 35: String Functions: The .UniqueName Function
Article 36: Drilling Through with MDX: The DRILLTHROUGH Statement
Article 37: Set Functions: The DRILLDOWNMEMBER() Function
Article 38: Set Functions: The DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM() Functions
Article 39: MDX Set Functions: The DRILLUPMEMBER() Function
Article 40: MDX Set Functions: DrillDownLevel()
Article 41: Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
Article 42: Set Functions: The DrillUpLevel() Function
Article 43: MDX Set Functions: The ToggleDrillState() Function
Article 44: MDX Set Functions: The Distinct() Function
Article 45: MDX Operators: The IS Operator
Article 46: Other MDX Entities: Perspectives
Article 47: MDX Numeric Functions: The .Ordinal Function
Article 48: MDX Operators: The IsLeaf() Operator: Conditional Logic within Calculations
Article 49: MDX Operators: The IsLeaf() Operator: Conditional Logic within Filter Expressions
Article 50: Logical Functions: IsSibling(): Conditional Logic within Calculations
Article 51: Logical Functions: IsSibling(): Conditional Logic within Filter Expressions
Article 52: Logical Functions: IsAncestor(): Conditional Logic within Calculations
Article 53: MDX Clauses and Keywords: Use HAVING to Filter an Axis
Article 54: Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
Article 55: Logical Functions: IsGeneration(): Conditional Logic within Calculations
Article 56: MDX Scripting Statements: Introducing the Simple CASE Statement
Article 57: Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
Article 58: String Functions: The .Properties Function
Article 59: String Functions: The .Properties Function, Part II
Article 60: MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
Article 61: Set Functions: The .AllMembers Function
Article 62: MDX Numeric Functions: The Max() Function
Article 63: MDX Numeric Functions: The Min() Function
Article 64: Set Functions: The AddCalculatedMembers() Function
Article 65: Set Functions: The StripCalculatedMembers() Function
Article 66: Intrinsic Member Properties: The MEMBER_CAPTION Property
Article 67: Intrinsic Member Properties: The MEMBER_KEY Property
Article 68: Intrinsic Member Properties: The MEMBER_NAME Property
Article 69: Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property

Get Clipmarks - The easiest way to email text, images and videos you find on the web.

A Jump start to SQL Server BI

www.sqlmag.com

A Jump Start to SQL Server BI

---

Author: Don Awalt Larry Barnes Alexei Bocharov Herts Chen Rick Dobson Rob Ericsson Kirk Haselden Brian Lawton Jesper Lind Tim Ramey Paul Sanders Mark D. Scott David Walls Russ Whitney

Sponsored by: About the Sponsor
Price: Free

Whether you're heavily involved in BI or just starting out, this eBook will prove a valuable resource. If you're new to BI, the collection of chapters will give you a solid foundation for understand where BI has come from and where it's headed. If you're a BI veteran, you'll most likely turn first to the chapters that explain what's coming in SQL Server 2005. But you'll also find that the earlier chapters will help validate what you already know.

In three sections, a cadre of knowledgeable authors cover the essentials of BI, provide a wealth of BI tips and techniques, and talk in detail about the new features coming with SQL Server 2005. If you're doing anything with BI, this is a resource you shouldn't be without.

Table of Contents
Foreword	View Foreword
Section I – Essential BI Concepts
Chapter 1: Data Warehousing: Back to Basics
Chapter 2: 7 Steps to Data Warehousing
Chapter 3: The Art of Cube Design
Chapter 4: DTS in Action
Chapter 5: Rock-Solid MDX
Chapter 6: XML for Analysis: Marrying OLAP and Web Services
Chapter 7: Improving Analysis Services Query Performance
Chapter 8: Reporting Services 101
Section II – BI Tips and Techniques
Section III – New BI Features in SQL Server 2005
Chapter 1: Building Better BI in SQL Server 2005
Chapter 2: UDM: The Best of Both Worlds
Chapter 3: Data Mining Reloaded
Chapter 4: What's New in DTS
Whole Book	Download PDF (2.1 MB)

Microsoft BI ebook - Upload a Document to Scribd

Read this document on Scribd: Microsoft BI ebook

ER Diagram from Microsoft Dynamics AX

doc.qiuhao.com

How to: Create an ERX ER Data Model

---

When you select the ERX ER Data Model option for the Reverse Engineering tool, it generates an .erx file that can be used to create an entity relationship (ER) data model. After you generate an .erx file, you can import the file into a modeling tool, such as Microsoft Office Visio or CA ERwin Data Modeler, to create an ER data model.

The Reverse Engineering tool can create an .erx file from a private or shared project or from a perspective. Before you generate an .erx file, be sure that the project or perspective is already defined. For more information, see How to: Create a Development Project or How to: Create a Perspective for a Report Model (MorphX Reporting Tools).

The following procedures explain how to generate an .erx file and create an ER data model from an .erx file.

To generate an .erx file

1. On the Microsoft Dynamics AX menu, point to Tools, point to Development Tools, and then click Reverse Engineer. The Reverse Engineering dialog box displays.

   Note
   An alternative way to display the Reverse Engineering dialog box is to right-click the project or perspective, point to Add-Ins, and then click Reverse Engineer.

2. In the dialog box, select ERX ER Data Model.

3. Select the type of item that you want to generate a model for, Private project, Shared project, or Perspective, and then select the item from the drop-down menu.

4. If you want to change the name or location of the file, click the folder icon, and then specify a name and location for the file.

5. Click OK.

To create an ER data model in Microsoft Office Visio

1. Open Microsoft Office Visio.

2. On the File menu, point to New, point to Software and Database (or Database if you are using Microsoft Office Visio 2003) and then click Database Model Diagram.

3. On the Database menu, point to Import, and then click Import Erwin ERX file.

   Note
   You must be using the Database Model Diagram template to access the Import command.

4. In the dialog box, click the Browse button, navigate to the location of the .erx file that you generated from the Reverse Engineering tool, select the file, and then click OK.

   Note
   Microsoft Office Visio imports the file and displays the progress in the Output window. The imported tables and views display in the Tables and Views window.

5. On the Database menu, point to View, and then click Tables and Views.

6. Drag items from the Table and Views window onto the diagram surface.

   After you have added a table to the diagram, you can right-click the table in the diagram, and then click Show Related Tables. This will add all the related tables from the Tables and Views window that are not already in the diagram.

To create an ER data model in CA ERwin Data Modeler

1. Open CA ERwin Data Modeler.

2. On the File menu, point to Import, and then click From External Format. Import the ERX ER data model file that you generated from the Reverse Engineering tool.

   Note
   When the diagram first displays, all items are displayed in a single stack. You must arrange the items in the diagram.

See Also

Reverse Engineering Tool Overview

How to: Create a Visio UML Data Model

How to: Create a Visio UML Object Model

Get Clipmarks - The easiest way to email text, images and videos you find on the web.