Download - Do You Have the Time
Do You Have The Time
Michael P. AntonovichBlog: http://SharePointMike.WordPress.comIT PRO Camp Jacksonville 2012
Orlando Code Camp 2012 2
My Published Books• User’s Guide to the Apple ][ - 1983• FoxPro 2 Programming Guide – 1992• Debugging and Maintaining FoxPro – 1992• Using Visual FoxPro 3.0 – 1995• Using Visual FoxPro 5.0 – 1996• Office and SharePoint User’s Guide – 2007• Office and SharePoint User’s Guide – 2010
6/16/2012
Speaker at:Code Camp 2009, 2010, 2011, 2012 OrlandoSharePoint Saturday 2011 Tampa, 2012 OrlandoSQL Saturday - #1, #4, #8, #10, #15, #16, #21, #32, #38, #49, #62, #74, #79, #85, #86, #110, #130
Orlando Code Camp 2012 3
Basic Terminology• Aggregate – A mathematical function that allows you to
summarize values of an attribute• Dimension - A dimension is essentially a look-up table that may
define a hierarchy or drill-down path such as Year > Quarter > Month
• Measure – A measure is something that identifies a value• Fact – A fact is another term for a measure that contains
numeric data that can be grouped along one or more dimensional hierarchy
• Star Schema – All dimension tables radiate out from a single fact table
• Snowflake Schema – One fact table may relate to another fact table before relating to dimension tables. One dimension table can also have a related dimension table
• A Pivot table or chart is usually based around a single fact table
6/16/2012
Orlando Code Camp 2012 4
Structure of Analysis Services Cubes• Database must either be in a Star or Snowflake
model. This often means de-normalizing some tables or ‘flattening’ the data model. Essentially you need a central ‘fact’ table from which all supporting dimension data radiates.
• You must flatten many-to-many relationships into a single table with only the data that you need.
• Based on date data in your central fact table, build a date dimension table with the columns needed.
• Create a script to populate the date dimension table with data.
6/16/2012
Orlando Code Camp 2012 5
Original Data Schema for Vaccination Data
6/16/2012
Orlando Code Camp 2012 6
Build the Patient_Vaccinations Table-- Build the Patient_Vaccinations Table-- This will create a table of 3,462,140 rows SELECT PV.VisitID, PV.CountyID, PV.VisitDate, PV.RaceID, PV.LeadScreening, PV.PatientAge, PV.TargetedCaseMgmt, PV.THStepsMed, V.Vaccination, V.VCost INTO Patient_Vaccinations FROM PatientVisits PV JOIN Visits_Vaccinations VV ON PV.VisitID = VV.VisitID JOIN Vaccinations V ON VV.VaccinationID = V.VaccinationID ORDER BY 1,2
6/16/2012
Orlando Code Camp 2012 7
Patient Visits and Vaccinations Flattened
6/16/2012
Orlando Code Camp 2012 8
Calculate Date Range Needed
• You need to calculate the date range needed for your date dimension. I usually look for the earliest and latest dates in the fact table and round the dates to January 1st for the earliest date and December 31st for the latest date.
• DECLARE @Min_Date DATE;• DECLARE @Max_Date DATE;
• -- Get the smallest and largest date for start and end of date table• SET @Min_Date = (SELECT
CONVERT(DATE,'01/01/'+STR(YEAR(MIN(VisitDate)))) FROM dbo.PatientVisits);
• SET @Max_Date = (SELECT CONVERT(DATE,'12/31/'+STR(YEAR(MAX(VisitDate)))) FROM dbo.PatientVisits);
6/16/2012
Orlando Code Camp 2012 9
Write Code to Build Date Dimension• DECLARE @SQLSTR varchar(8000);
• --Delete Date Dimension table if it exists• SET @SQLSTR = 'IF OBJECT_ID(''' + @tblname + ''', ''U'') IS NOT NULL• DROP TABLE ' + @tblname• EXECUTE(@SQLSTR)
• --Create Date Dimension table• SET @SQLSTR = 'CREATE TABLE ' + @tblname +• ' ([PK_Date] [datetime] NOT NULL,• [Date_Name] [nvarchar](50) NULL,• [Year] [char](4) NULL,• [Calendar_Year] [char](13) NULL,• [Quarter_Of_Year] [char](1) NULL,• [Quarter_Of_Year_Name] [char](9) NULL,• [Year_Qtr] [char](6) NULL,• [Year_Qtr_Name] [char](15) NULL,• [Month] [char](2) NULL,• [Month_Name] [char](15) NULL,• [Year_Month] [char](10) NULL,• [Year_Month_Name] [char](20) NULL,• [Day_Of_Year] [char](3) NULL,• [Day_Of_Year_Name] [char](8) NULL,• [Day_Of_Month] [int] NULL,• [Week_Of_Year] [char](2) NULL,• [Week_Of_Year_Name] [char](7) NULL,• CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED • (• [PK_Date] ASC• )WITH (PAD_INDEX = OFF, • STATISTICS_NORECOMPUTE = OFF, • IGNORE_DUP_KEY = OFF, • ALLOW_ROW_LOCKS = ON, • ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]• ) ON [PRIMARY]'• EXEC(@SQLSTR)
6/16/2012
Orlando Code Camp 2012 10
Write Code to Populate Date Dimension
SET @Loop_Date = @Min_Date;
WHILE @Loop_Date <= @Max_Date BEGIN SET @SQLSTR = 'INSERT INTO ' + @tblname + ' VALUES (''' SET @SQLSTR = @SQLSTR + CAST(@Loop_Date as varchar(30)) + ''', ''' SET @SQLSTR = @SQLSTR + DateName(dw,@Loop_Date) + ', ' + DateName(m,@Loop_Date) + ' ' + RTRIM('0'+CAST(DatePart(d,@Loop_Date) AS char(2))) + ' ' + CAST(DatePart(yyyy,@Loop_Date) as char(4)) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + ''', ''' SET @SQLSTR = @SQLSTR + 'Calendar ' + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + ''', ''‘ SET @SQLSTR = @SQLSTR + CAST(DATEPART(q,@Loop_Date) as CHAR(1)) + ''', ''' SET @SQLSTR = @SQLSTR + 'Quarter ' + CAST(DATEPART(q,@Loop_Date) AS CHAR(1)) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + '-' + CAST(DATEPART(q,@Loop_Date) AS CHAR(1)) + ''', ''' SET @SQLSTR = @SQLSTR + 'Quarter ' + CAST(DATEPART(q,@Loop_Date) AS CHAR(1)) + ', ' + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + ''', ''' SET @SQLSTR = @SQLSTR + RIGHT('0'+RTRIM(CAST(DATEPART(m,@Loop_Date) As char(2))),2) + ''', ''' SET @SQLSTR = @SQLSTR + DATENAME(m,@Loop_Date) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + '-' + RIGHT('0'+RTRIM(CAST(DATEPART(m,@Loop_Date) As char(2))),2) + '-01' + ''', ''' SET @SQLSTR = @SQLSTR + DATENAME(m,@Loop_Date) + ' ' + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + ''', ''' SET @SQLSTR = @SQLSTR + RIGHT('00'+RTRIM(CAST(DATEPART(DayOfYear,@Loop_Date) AS char(3))),3) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + '-' + RIGHT('00'+RTRIM(CAST(DATEPART(DayOfYear,@Loop_Date) AS char(3))),3) + ''', ''' SET @SQLSTR = @SQLSTR + RIGHT('0'+RTRIM(CAST(DATEPART(dd,@Loop_Date) AS CHAR(2))),2) + ''', ''' SET @SQLSTR = @SQLSTR + RIGHT('0'+RTRIM(CAST(DATEPART(wk,@Loop_Date) AS char(2))),2) + ''', ''' SET @SQLSTR = @SQLSTR + CAST(DATEPART(yyyy,@Loop_Date) AS CHAR(4)) + '-' + RIGHT('0'+RTRIM(CAST(DATEPART(Wk,@Loop_Date) AS char(2))),2) + ''')‘
EXECUTE(@SQLSTR) --Increment loop date and see if at end SET @Loop_Date = DATEADD(Day,1,@Loop_Date) END;6/16/2012
Orlando Code Camp 2012 11
Final Data Schema for Vaccination Data
6/16/2012
Orlando Code Camp 2012 12
Analysis Services Demo
6/16/2012
Orlando Code Camp 2012 13
What is PowerPivot?
• PowerPivot is a data analysis tool• PowerPivot is a free download from Microsoft• PowerPivot lets you process millions of rows of data• PowerPivot uses as much memory as available &
multi-cores• PowerPivot lets you integrate data from multiple
sources• PowerPivot lets you explore, analyze and create
analysis• PowerPivot was originally called: Project ‘Gemini’
6/16/2012
Orlando Code Camp 2012 14
PowerPivot PreRequesites
• 32-or 64 bit Operating System• 1 GB of RAM, 2 GB recommended (or more)• Windows XP with SP3, Vista with SP1, Windows 7• .NET Framework 3.5 SP1 or better• Microsoft Office 2010
6/16/2012
Orlando Code Camp 2012 15
Why Use PowerPivot
• Fewer data requests since staff can slice and dice their own data
• More control over data other people can access
• Users can generate their own reports• PowerPivot gives SQL Server data access to
the masses
6/16/2012
Orlando Code Camp 2012 16
Pre-Check Your Data
• Use 1 and only 1 header row• All data in column should be consistently
formatted• No blank columns• No blank rows• Data columns must be 100% filled with
data
6/16/2012
Orlando Code Camp 2012 17
Order of Loading Data
• Always load the main transaction table first• This forces the fields to appear at the top of the PowerPivot Field List.• PowerPivot’s relationship detection expects the main table to be first.
• Import lookup tables in their hierarchical order
6/16/2012
Orlando Code Camp 2012 18
Types of DAX Calculations
• Simple Calculations• Calculated columns within table• Calculated columns between tables• Calculated columns to serve as links to other tables• Calculated columns are calculated for every row in the
table.
• Aggregate Calculations• Calculate unique measures • Aggregate measures are only calculated for the displayed
data in the Pivot table
6/16/2012
Orlando Code Camp 2012 19
DAX Functions• Comprised of 135 functions• 71 functions are similar to Excel functions• 69 have the same name – 2 do not• TEXT FORMAT• DATEDIF YEARFRAC
• 64 functions are unique to DAX• Aggregate data functions• Date related functions
6/16/2012
Orlando Code Camp 2012 20
Available DAX Date/Time functions• =DATE(<year>, <month>, <day>)• =DATEVALUE(<date_text>)• =DAY(<date>)• =EDATE(<start_date>, <months>)• =EOMONTH(<start_date>, <months>)• =HOUR(<datetime>)• =MINUTE(<datetime>)• =MONTH(<datetime>)• =NOW()• =SECOND(<datetime>)• =TIME(<hour>, <minute>, <second>)• =TIMEVALUE(<time_text>)• =TODAY()• =WEEKDAY(<datetime>)• =WEEKNUM(<date>, <return_type>)• =YEAR(<date>)• =YEARFRAC(<start_date>, <end_date>, <basis>)
6/16/2012
Orlando Code Camp 2012 21
Using FORMAT to get Names• Get the Year:
=YEAR(Patient_Vaccinations[VisitDate])
• Get Month Number:
=FORMAT(MONTH(Patient_Vaccinations[VisitDate]),"00")
• Get name of the month=FORMAT(Patient_Vaccinations[VisitDate],"MMM")
• Get name of the week day=FORMAT(WEEKDAY(Patient_Vaccinations[VisitDate]),
"dddd")
6/16/2012
Orlando Code Camp 2012 22
Other Calculated Columns
• Calculating Quarter of Year=INT((MONTH(Patient_Vaccinations[VisitDate])-1)/3)+1
• Calculating Quarter of Year Name="Qtr " &
INT((MONTH(Patient_Vaccinations[VisitDate])-1)/3)+1
• Get week number of year=WEEKNUM(Patient_Vaccinations[VisitDate])
6/16/2012
Orlando Code Camp 2012 23
PowerPivot Demo
6/16/2012
Orlando Code Camp 2012 24
Useful Links
• Download PowerPivot at• http://www.powerpivot.com/download.aspx
• PowerPivot Sample Data• http://powerpivotsampledata.codeplex.com
• PowerPivot Tutorial Data• http://technet.microsoft.com/en-us/library/ee835510.aspx
• DAX On-line Function Reference• http://technet.microsoft.com/en-us/library/ff452127.aspx• http://
social.technet.microsoft.com/wiki/contents/articles/powerpivot-dax-text-functions.aspx
6/16/2012
Orlando Code Camp 2012 25
GOT QUESTIONS
?
6/16/2012
Orlando Code Camp 2012 26
Thank YouDon’t forget to fill out your evaluations.
MICHAEL P. ANTONOVICH
[email protected] Book Site: http://sharepointmike.wordpress.com/
6/16/2012