AS+Planning+and+Impl ementation

SQL Server Analysis ServicesPlanning & Implementation : 

SQL Server Analysis ServicesPlanning & Implementation By: Ben Aminnia President, L.A. SQL Server Professionals Group ben@sql.la

Agenda : 

2 Agenda Introduction Presentation Questions and Answers

Introduction : 

3 Introduction Primary Objective: Explore a recent implementation of Analysis Services, with focus on some important planning tasks. Presentation Style: We will be placed in a unique setting called: “Live from SQL Server Studios” I will be the interviewer – telling you about an Analysis Services application one of my staff had developed. Now that he has left the company, I’m interviewing you for his position.

Introduction : 

4 Introduction Why the “Interview” Model: Look at it from an IT Manager’s viewpoint; This is not a sales presentation, showing off “the cool stuff” in a new product; This is not a “What’s New in SSAS” presentation either; Don’t expect “click here – click there – voila!” We may not even show off any “Wizard” screens;

Introduction : 

5 Introduction Interview Structure: I’ll present a task and seek your opinion. You will share your thoughts with me and will tell me how you would approach that task. I will then show you how my ex-employee accomplished that task. Finally, you will have your turn to make comments and state your opinion: You may agree with the presented approach; You may agree and also extend it further according to your viewpoint; You may disagree and offer an alternative approach.

Final Goals : 

6 Final Goals Four Stages of an SSAS System: Vision Architecture Development of various pieces Periodic data load process

At the Receptionist’s Desk : 

7 At the Receptionist’s Desk

Advice from the Receptionist : 

8 Advice from the Receptionist As we walk down the hallway to the conference room for your interview, let me tell you a little bit about Ben’s Analysis Services project: The Stethoscope The Thermometer The Wheel The Charts

Interview Question #1 : 

9 Interview Question #1 How many servers do you need to implement an Analysis Services system? A) One server can host everything we need B) At least two (for DWH / Data Mart and AS) C) At least three (for DEV, QA, and Production) D) It depends … Could be more

Interview Question #1 : 

10 Interview Question #1 How many servers do you need to implement an Analysis Services system? A) One server can host everything we need B) At least two (for DWH / Data Mart and AS) C) At least three (for DEV, QA, and Production) D) It depends … Could be more

Interview Question #2 : 

11 Interview Question #2 What do you need to do before building the 1st Cube? A) Open the Cube Wizard B) Run ETL to populate a Data Mart C) Plan the measures and the schemas D) Setup SSIS and SSAS

Interview Question #2 : 

12 Interview Question #2 What do you need to do before building the 1st Cube? A) Open the Cube Wizard B) Run ETL to populate a Data Mart C) Plan the measures and the schemas D) Setup SSIS and SSAS

Interview Question #3 : 

13 Interview Question #3 What’s your definition of Analysis Services?

Interview Question #4 : 

14 Interview Question #4 What’s the value of ONE RECORD in a SQL Server table?

At the Heart of ETL … : 

15 At the Heart of ETL … Avoid Fundamental Mistakes or Mis-conceptions! Do not overlook the “measures” which are the heart of the solution and jump on building the databases and the cubes. An analytical solution is all about numbers. A beautiful chart does not guaranty useful results. AS data is not like customer address table. There’s virtually no room for retroactive data corrections.

At the Heart of ETL … : 

16 At the Heart of ETL … Focus on the Target – Aerial Photos Won’t Help! At the heart of the ETL process, look for data sources that are accessible and agreeable to both users and architects. Start with a stethoscope that picks up one heart beat or a thermometer that reads one body temperature accurately and consistently.

Ground Rules : 

17 Ground Rules Any Solution Must Answer a Well-known Problem A solution that doesn’t tell you which problem it is solving is not worth our time. The technology tools, like builders and wizards, are only part of the solution. Re-use Existing Solutions Do not re-invent the wheel. Use the right-size wheel according to the car manufacturer’s specifications. Think End-to-End

Enough Lecturing! : 

18 Enough Lecturing!

Problem Statement : 

19 Problem Statement Here’s a summary of system requirements: The DBA at a SQL Server shop with dozens of servers needs to monitor disk space utilization of these servers. Servers are both within firewalls and in the DMZ. Monitoring intervals are not fixed. Every server may not necessarily be available when the monitor job runs.

Problem Statement : 

20 Problem Statement Additional Notes and Requirements: Both Free-space and Used-space amounts need to be monitored (in MB and GB). Free-space and Used-space DELTA amounts (from last monitor date to the current) are needed as well. If a server was not available on the last monitor date, its DELTA amount needs to be calculated from its most recent available date until current date.

Feasibility Analysis & Some Critical Questions : 

21 Feasibility Analysis & Some Critical Questions Before Drawing a Roadmap and Planning the Stops on our Trip … Is Server Disk-space Planning a good project for Analysis Services? How would you compare and contrast this to a typical Sales Forecast model? How would our model behave if we delete a 100 GB database, or move it to a different server? What if we zip / archive some of the folders?

The Road Map : 

22 The Road Map Processing Stages: Collect data about server space information and store in a staging database; Perform additional calculations as needed; Populate fact tables in the Data Mart; Update / add to dim tables in the Data Mart; Transfer data from Data Mart fact & dim tables to Analysis Services database (“Process”); Update user interface data (e.g. Refresh Excel Pivot Tables);

But Wait! : 

23 But Wait!

Interview Question #2 : 

24 Interview Question #2 What do you need to do before building the 1st Cube? A) Open the Cube Wizard B) Run ETL to populate a Data Mart C) Plan the measures and the schemas D) Setup SSIS and SSAS

Interview Question #2 : 

25 Interview Question #2 What do you need to do before building the 1st Cube? A) Open the Cube Wizard B) Run ETL to populate a Data Mart C) Plan the measures and the schemas D) Setup SSIS and SSAS

1st Star Schema: DM_Ben_Freespace : 

26 1st Star Schema: DM_Ben_Freespace

1st Star SchemaFreespaceFact : 

27 1st Star SchemaFreespaceFact CREATE TABLE [dbo].[FreespaceFact]( [Freespace_id] [int] IDENTITY(1,1) NOT NULL, [server_fk] [int] NULL, [drive_fk] [int] NULL, [cycle_fk] [int] NULL, [mb_free] [int] NULL, [gb_free] [int] NULL

1st Star SchemaCycleDim : 

28 1st Star SchemaCycleDim CREATE TABLE [dbo].[CycleDim]( [cycle_id] [int] IDENTITY(1,1) NOT NULL, [cycle] [int] NULL, [cycle_date] [datetime] NULL, [year_number] [int] NULL, [month_number] [int] NULL, [day_number] [int] NULL, [week_number] [int] NULL, [day_of_the_week] [int] NULL

1st Star SchemaServerDim : 

29 1st Star SchemaServerDim CREATE TABLE [dbo].[ServerDim]( [server_id] [int] IDENTITY(1,1) NOT NULL, [name] [nchar](50) NULL, [location] [nchar](50) NULL, [ip] [nchar](15) NULL, [stage] [nchar](10) NULL

1st Star SchemaDriveDim : 

30 1st Star SchemaDriveDim CREATE TABLE [dbo].[DriveDim]( [drive_id] [int] IDENTITY(1,1) NOT NULL, [server_id] [int] NOT NULL, [server_name] [nchar](50) NULL, [drive_letter] [nchar](1) NOT NULL

2nd Star Schema: DM_Ben_Usedspace : 

31 2nd Star Schema: DM_Ben_Usedspace

2nd Star Schema UsedspaceFact : 

32 2nd Star Schema UsedspaceFact CREATE TABLE [dbo].[UsedspaceFact]( [usedspace_id] [int] IDENTITY(1,1) NOT NULL, [server_fk] [int] NULL, [drive_fk] [int] NULL, [cycle_fk] [int] NULL, [file_fk] [int] NULL, [size] [nvarchar](18) NULL, [size_kb] [bigint] NULL, [size_mb] [bigint] NULL, [size_gb] [bigint] NULL, [maxsize] [nvarchar](18) NULL, [growth] [nvarchar](18) NULL, [usage] [varchar](9) NULL

2nd Star SchemaFileDim : 

33 2nd Star SchemaFileDim CREATE TABLE [dbo].[FileDim]( [file_id] [int] IDENTITY(1,1) NOT NULL, [server_fk] [int] NULL, [server_name] [sql_variant] NULL, [drive_fk] [int] NULL, [drive_letter] [nvarchar](1) NULL, [DatabaseName] [nvarchar](128) NULL, [DB_fileid] [smallint] NULL, [DatabaseFileName] [nvarchar](128) NULL, [filename] [nvarchar](260) NULL, [filegroup] [nvarchar](128) NULL

The Road Map : 

34 The Road Map Processing Stages: Collect data about server space information and store in a staging database; Perform additional calculations as needed; Populate fact tables in the Data Mart; Update / add to dim tables in the Data Mart; Transfer data from Data Mart fact & dim tables to Analysis Services database (“Process”); Update user interface data (Refresh Excel Pivot Tables);

The Road Map : 

35 The Road Map Processing Stages: Collect data about server space information and store in a staging DB; Perform additional calculations as needed; Populate fact tables in the Data Mart; Update / add to dim tables in the Data Mart; Transfer data from Data Mart fact & dim tables to Analysis Services database (“Process”); Update user interface data (Refresh Excel Pivot Tables);

Data Collection : 

36 Data Collection

Data Collection : 

37 Data Collection

Data Collection : 

38 Data Collection

Data Collection : 

39 Data Collection

Data Collection : 

40 Data Collection

Data Collection : 

41 Data Collection

Data Collection : 

42 Data Collection X X

Data Collection : 

43 Data Collection Take 1 Write a couple of stored procedures Define each target server as a linked server Define logins for each target linked server Run the stored procedures against each target linked server Continue with loading the fact and dimension tables, the cubes, and the Excel pivot tables

Data Collection : 

44 Data Collection Take 1

Data Collection : 

45 Data Collection Take 1

Data Collection : 

46 Data Collection Take 1

Data Collection : 

47 Data Collection Take 1

Data Collection : 

48 Data Collection Take 1 – Pros and Cons Pros: It worked! Opportunity to refine those stored procedures

Data Collection : 

49 Data Collection Take 1 – Pros and Cons Cons: Too many manual steps Problems with access to linked servers Extra work to add a new servers

Data Collection : 

50 Data Collection Take 2 Try to automate some of the process; Look for a reusable “wheel” … Don’t re-invent!

Data Collection : 

51 Data Collection Take 2 Try to automate some of the process; Look for a reusable “wheel” … Don’t re-invent!

Data Collection : 

52 Data Collection The Wheel

Data Collection : 

53 Data Collection The Wheel

Data Collection : 

54 Data Collection Additions to the Wheel

Data Collection : 

55 Data Collection Additions to the Wheel

Data Transfer : 

56 Data Transfer From Data Mart to Analysis Services Design Time through 1st Full Transfer: Easier to develop Longer to run Cyclical Updates: Shorter run-time Complex development logic to identify what to transfer and how

Data Delivery to End-Users : 

57 Data Delivery to End-Users X X

Data Delivery to End-Users : 

58 Data Delivery to End-Users

Data Delivery to End-Users : 

59 Data Delivery to End-Users Reporting Services: Part of “The Wheel” – SSIS & SSRS Excel Pivot Tables: 2003 Version 2007 Version 2010 Version

Interview Question #4 : 

60 Interview Question #4 How do you add SSRS to a new laptop? Initial SSRS setup RS Configuration Manager Create a project Add Shared Data Sources (rds) Add reports to the project (rdl) Change each report’s data source to a Shared Data Source Deploy project

Presentation : 

61 Presentation

Questions and Answers : 

62 Questions and Answers

Additional References : 

63 Additional References SQL Server Magazine Article – February 2008 http://www.sqlmag.com/Articles/ArticleID/97840/97840.html?Ad=1 SQL Server 2008 Analysis Services Step by Step – Scott Cameron Data Mining with Microsoft SQL Server 2008 – Jamie MacLennan Foundations of SQL Server 2005 Business Intelligence - Lynn Langit Practical Business Intelligence with SQL Server 2005 - John C. Hancock and Roger Toren A Decision Maker's Overview of Business Intelligence in SQL Server 2005 – Ben Aminnia www.sql.la

Contact Information : 

64 Contact Information Emails: ben@sql.la president@sql.la sqlsig@ladotnet.org ben@pointercorp.com Websites: www.sql.la www.pointercorp.com www.vipletters.com www.takeatest.net www.ladotnet.org

Thank You! : 

65 Thank You!