This is a “spill” of many notes for many courses that need to end up in more specific pages, but at least the links can be searched for now.enc

[see the 20466 notes at the end of this document that need to be integrated into their proper places]

————-Querying class notes————-

0===============
This is the URL for Microsoft SQL Server sample databases on Codeplex,
<|>
http://msftdbprodsamples.codeplex.com/
<|>
To download the T-SQL 2012 database
<|>
https://www.microsoftpressstore.com/store/training-kit-exam-70-461-querying-microsoft-sql-server-9780735666054#downloads
<|>
This may be a suitable ERD:
<|>
https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLL%20Files%20to%20Xfer/ERDs%20%26%20System%20Views/TSQL2012ERD.png
Transact-SQL Reference (Database Engine)
<|>
https://msdn.microsoft.com/en-us/library/bb510741.aspx
http://docs.microsoft.com/en-us/sql/t-sql/language-reference
<|>
Transact-SQL Syntax Conventions-Transact-SQL
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/transact-sql-syntax-conventions-transact-sql
<|>

Transact-SQL Reference (Database Engine)
https://docs.microsoft.com/en-us/sql/t-sql/language-reference
<|>

References on ANSI SQL v. real database engines

Which Database uses pure ANSI SQL? Is it MySQL, Oracle, PostgreSQL, or SQL Server?
https://www.quora.com/Which-Database-uses-pure-ANSI-SQL-Is-it-MySQL-Oracle-PostgreSQL-or-SQL-Server

ANSI SQL Standards Explained For Database Administrators
https://www.whoishostingthis.com/resources/ansi-sql-standards/

Database Engines and ANSI SQL Compliance
https://stackoverflow.com/questions/3602617/database-engines-and-ansi-sql-compliance

Introduction to Microsoft SQL Server 2014
<|>
[Connecting to a remote server in a T-SQL Script] sp_addlinkedserver (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms190479.aspx
<|>

Introduction to T-SQL Querying

CASE (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms181765.aspx
<|>

Module: JOINs (Querying Multiple Tables)
<|> It may be helpful to think of JOINing as a method to add columns to the result set in view of data exisiting in different columns, due to normalization.
<|>
In view of the relational databases being normalized we must query multiple tables to add meaningful columns back into our query results. So, JOINing adds columns to our queries.
<|>
Other resources on JOINs:
<|>
http://www.sqlservercentral.com/articles/T-SQL/67941/
<|>
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
<|>
http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
<|>

Module: Sorting and Filtering Data

One example programmatically using OFFSET/FETCH
<|>
http://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch
<|>
…and here’s another
http://stackoverflow.com/questions/187998/row-offset-in-sql-server
<|>
Module: Working with SQL Server Data Types

Keep in mind that “IF” is a statement, and IIF() is a function.
Choose can be used to easily implement a condition with more than two conditions.
<|>
CHOOSE (Transact-SQL)
<|>
http://msdn.microsoft.com/en-us/library/hh213019.aspx
<|>
IIF (Transact-SQL)
<|>
http://msdn.microsoft.com/en-us/library/hh213574.aspx
<|>
IF…ELSE (Transact-SQL)
<|>
http://msdn.microsoft.com/en-us/library/ms182717.aspx
<|>

Resource Database
https://msdn.microsoft.com/en-us/library/ms190940.aspx

Periodic Table of Dynamic Management Objects
<|>
http://thesqlagentman.com/periodic-table/

Advanced topic: See Jeff Moden’s article(s) on Tally Tables at SQLServerCentral.com
<|>
SQL Dev URL, The “Numbers” or “Tally” Table: What it is and how it replaces a loop by Jeff Moden
<|>
http://www.sqlservercentral.com/articles/T-SQL/62867/
<|>
The SQL Server Numbers Table, Explained – Part 1
<|>
https://www.mssqltips.com/sqlservertip/4176/the-sql-server-numbers-table-explained–part-1/
<|>
SQL Querying / Dev URL, The SQL Server Numbers Table, Explained – Part 2 by Aaron Bertrand

<|>

https://www.mssqltips.com/sqlservertip/4177/the-sql-server-numbers-table-explained–part-2/
<|>
Module – Data Types
Collation notes:
A Collation defines text-based sorting and comparison rules, such as whether or not to pay attention to case, accents, and other sensitivities, and takes into account the alphabet sort order and read order.
<|>
Collation can be assigned to the root of the Instance during installation, to a database when it is created, to a text-based column within a table, and collation can be overriden with a COLLATE clause within a query.
<|>
To see where the Collation decision is made during the installation of an Instance see here:
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step13b-ServerConfigPage-CollationTAB.png
<|>
Bear trap picture, https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLLPictureLibrary/Bear-Trap.jpg

Data Type Conversion (Database Engine)
<|>
https://msdn.microsoft.com/en-us/library/ms191530.aspx
<|>
CAST and CONVERT (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms187928.aspx
<|>
Data Type Precedence (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms190309.aspx
<|>

Get Started with Full-Text Search
<|>
https://msdn.microsoft.com/en-us/library/ms142497(v=SQL.120).aspx
<|>
Full-Text Search
<|>
https://msdn.microsoft.com/en-us/library/ms142571(v=SQL.120).aspx
<|>
Query with Full-Text Search
<|>
https://msdn.microsoft.com/en-us/library/ms142583(v=SQL.120).aspx
<|>
Module – Subqueries
<|>
EXISTS (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms188336.aspx
<|>
Difference between EXISTS and IN in SQL? [Stackoverflow]
<|>
http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql
<|>
Using EXISTS [SQLTeam.com]
<|>
http://www.sqlteam.com/article/using-exists
<|>

Module – Using DML to Modify Data [Data Manipulation Language]
<|>

Sequence Numbers
<|>
https://msdn.microsoft.com/en-us/library/ff878058.aspx
<|>

————-Database Administration class notes————-

——–Intro Module——–
What makes a good database administrator?
Being likable. Being willing to be not liked.

Being diplomatic; being able to create and maintain relationships.
Attention to detail.
React quickly.
Cool under pressure.
Take ownership.

Be proactive.

Run book documentation (additions)

Standard build configurations. Non-default settings. Passwords.

Why Instances?
For now, a “Collation” is a rulebook for sorting and comparing text-based data and takes into consideration, among other factors, alphabet, case-sensitivity (potentially), and accent sensitivity (potentially).
————Software Tools related to SQL Server————
SQLCMD usage:
<|>
https://technet.microsoft.com/en-us/library/ms180944%28v=sql.110%29.aspx
<|>
How to use the DAC:
<|>
Diagnostic Connection for Database Administrators [DAC]
<|>
https://msdn.microsoft.com/en-us/library/ms189595(v=sql.120).aspx
<|>

https://technet.microsoft.com/en-us/library/ms178068%28v=sql.105%29.aspx

http://www.e-squillace.com/Tech/Misc/Tricks/TextEditorShortcuts.htm

Using PowerShell to Manage SQL Server

See the references listed in my “NH OLL Important Resources” file you downloaded earlier for free PowerShell ebook resources.

Here are the High Availability options with SQL Server (these can be used separately or together, depending on circumstances):
1) Log Shipping
2) Replication
3) AlwaysOn Failover Cluster Instances
4) Database Mirroring [deprecated]

5) AlwaysOn Availability Groups (replacement to Mirroring)

——–Installation——–
The Upgrade Advisor has been replaced by the SQL Server Migration Assistant
<|>
https://docs.microsoft.com/en-us/sql/ssma/sql-server-migration-assistant
<|>
Service Account Selection screenshot in SQL Server 2012:
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step13a-ServerConfigPage-SvcAcctsTAB.png
<|> and <|>

http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step13aa-ServerConfigPage-SvcAcctsTAB-AcctsAssigned.png

Data Directories selection during SQL Server 2012 installation:
<|>

http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step14b-DBEngineConfigPage-DataDirsTAB.png————

Collation selection during SQL Server 2012 installation:
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step13b-ServerConfigPage-CollationTAB.png
<|>
Components that rely on Internet Explorer:
“BIDS”, or “Business Intelligence Development Studio” has been renamed to “SSDT”, or “SQL Server Data Tools”.
————Regarding Storage
RAID tutorial:
<|>
http://www.acnc.com/raid
<|>
Download locations for SQLIOSIM:
<|>
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/10/06/sqliosim-available-for-download.aspx
Download location for SQLIO:
<|>

https://www.microsoft.com/en-us/download/details.aspx?id=20163

Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO)
<|>
https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223
<|>

SQL Server Browser Service (Database Engine and SSAS)
<|>
https://msdn.microsoft.com/en-us/library/hh510203.aspx
<|>
{Diagram} How Do Computers Find Services on Other Computers?
<|>
http://www.e-squillace.com/tech/techdiagrams/How_do_computers_identify_services_on_servers%C2%BF.png
————under “Managed Service Account————
Configure Windows Service Accounts and Permissions
<|>
https://msdn.microsoft.com/en-us/library/ms143504.aspx
<|>
Install SQL Server 2014 from the Command Prompt
<|>
https://msdn.microsoft.com/en-us/library/ms144259.aspx
<|>
MSDN reference, “Install SQL Server 2014 Using a Configuration File”
<|>
https://msdn.microsoft.com/en-us/library/dd239405.aspx
<|>
Install SQL Server 2014 Using SysPrep
<|>
https://msdn.microsoft.com/en-us/library/ee210664.aspx
<|>
SQL Server FineBuild:
<|>
https://github.com/SQL-FineBuild/v3.4/releases
<|>
http://sqlserverfinebuild.codeplex.com/
<|>

Setup Role screenshot:
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step8-SetupRole.png
<|>
SQL Server Version and Build List
<|>
http://sqlserverbuilds.blogspot.com
<|>
Some links on virtualization:
<|>
http://www.brentozar.com/archive/2014/09/the-second-concern-for-sql-server-virtualization-backup/
<|>
http://www.davidklee.net/articles/virtualization-articles/
<|>
[For what it’s worth…]
http://blog.lusis.org/blog/2014/06/13/no-konami-for-operations/
<|>
http://thomaslarock.com/2013/04/doing-it-wrong-virtualizing-sql-server/
<|>
Provisioning a New SQL Server Instance – Part One
Before you even install SQL Server, there a number of preparatory steps you need to take in order to get a new machine with a fresh copy of the operating system completely ready to install SQL Server properly. This is to maximize performance, reliability, and security.

https://www.simple-talk.com/sql/database-administration/provisioning-a-new-sql-server-instance-part-one/

Provisioning a New SQL Server Instance – Part Two
So how should you install and configure SQL Server 2012 properly? Glenn Berry completes his two-part series by explaining the steps needed to complete the preparation and do the actual installation.

https://www.simple-talk.com/sql/database-administration/provisioning-a-new-sql-server-instance-part-two/

Provisioning a New SQL Server Instance – Part Three
Once you’ve installed and configured SQL Server 2012, there are some tasks that should be done to ensure that maintenance, monitoring and alerting systems are in place to keep the instance running smoothly. Glenn Berry explains how.

https://www.simple-talk.com/sql/database-administration/provisioning-a-new-sql-server-instance-part-three/

—-Working with Databases and Storage—–
Free ebook: SQL Server Transaction Log Management by Tony Davis and Gail Shaw
<|>
https://www.simple-talk.com/books/sql-books/sql-server-transaction-log-management-by-tony-davis-and-gail-shaw/

RAID tutorial:
<|>
http://www.acnc.com/raid
“As well as expanding the size of the transaction log..”
Truncate means to remove “water” from the bucket, not change the bucket size.
<|>
Script file reference:
<|>
“Monitoring Space Usage in TempDB or any DB.sql”
<|>
Script file reference:
<|>
“Alter Database chg several database options.sql”
<|>
DBCC SHRINKDATABASE (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms190488.aspx
<|>
DBCC SHRINKFILE (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms189493.aspx
<|>
—-Backups & DR—–
Free ebook:
<|>
https://www.simple-talk.com/books/sql-books/sql-backup-and-restore/

Database Instant File Initialization
<|>
https://technet.microsoft.com/en-us/library/ms175935%28v=sql.120%29.aspx
<|>
SQL Server Backup Terminology Part 1: Media Sets & Backup Sets
==::||::==
https://www.bobpusateri.com/archive/2015/11/sql-server-backup-terminology-part-1-media-sets-backup-sets-2/
==::||::==
Recovery Models (SQL Server)
<|>
https://msdn.microsoft.com/en-us/library/ms189275.aspx
<|>

SQL Server Transaction Log Management by Tony Davis and Gail Shaw
<|>
https://www.simple-talk.com/books/sql-books/sql-server-transaction-log-management-by-tony-davis-and-gail-shaw/
<|>
Change the Target Recovery Time of a Database (SQL Server) {relates to CHECKPOINTs}
<|>
https://msdn.microsoft.com/en-us/library/hh403416.aspx
<|>

A UNC path refers to a shared folder, Universal Naming Convention path. This comes in the form of:
<|>
\ServerName\SharedFolderName

My nickname for “Copy Only” backups is “touch-free carwash”.

Reference:
<|>

http://msdn.microsoft.com/en-us/library/ms191495%28v=sql.110%29.aspx

Script file reference:
<|>
“Backup – Restore – sp_AddDumpDevice demo.sql”
“BACKUP DATABASE complex script using a mixture of FILE and FILEGROUP specifiers.sql”

Backup Compression (SQL Server)
<|>
https://msdn.microsoft.com/en-us/library/bb964719.aspx

Script file reference:
<|>
“Backup Verification Scripts ie RESTORE HEADERONLY.sql”
<|>
Backup History and Header Information (SQL Server)
<|>
https://msdn.microsoft.com/en-us/library/ms188653.aspx
<|>
RESTORE Statements – VERIFYONLY (Transact-SQL)
<|> https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-verifyonly-transact-sq
l <|>
RESTORE Statements – LABELONLY (Transact-SQL)
<|> https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-labelonly-transact-sql
<|>
RESTORE Statements – HEADERONLY (Transact-SQL)
<|> https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql
<|>
RESTORE Statements – FILELISTONLY (Transact-SQL)
<|> https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-filelistonly-transact-sql
<|>

—-Restore & Recovery topics—–
“Another advanced option is the ability to restore an individual page…”
Also, query the MSDB.dbo.suspect_pages system table; it will contain a list of pages that failed to read properly, up to 1,000 listed pages.

Start SQL Server in Single-User Mode
<|>
https://msdn.microsoft.com/en-us/library/ms188236.aspx
<|>
[When you have a corrupt MODEL database you need to start only the Master database using a Trace Flag]
Trace Flags (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms188396.aspx
<|>
RESTORE (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms186858.aspx
<|>
Restore the master Database (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms190679.aspx
<|>
Rebuild System Databases
<|>

https://msdn.microsoft.com/en-us/library/dd207003.aspx

—-Data Transfer topics—–
MSDN: dtexec Utility
<|>
https://msdn.microsoft.com/en-us/library/hh231187.aspx
Execute Package Utility (DtExecUI) UI Reference
<|>
https://msdn.microsoft.com/en-us/library/ms180378.aspx

bcp Utility
<|>
https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15
<|>
BULK INSERT (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms188365.aspx

—-Monitoring topics—–
Tools >> Facts >> Conclusions >> Changes
SQL Server Data Collection
<|>
https://msdn.microsoft.com/en-us/library/bb677179.aspx

[Recent Expensive Queries section of Activity Monitor]
Free ebook on SQL Server Execution Plans
<|>
https://www.simple-talk.com/books/sql-books/sql-server-execution-plans,-second-edition,-by-grant-fritchey/
<|>
Free ebook on Dynamic Management Views:
<|>
https://www.simple-talk.com/books/sql-books/performance-tuning-with-sql-server-dynamic-management-views/
<|>

—-Tracing & Profiling topics—–
Free ebook on Profiler:
<|>
https://www.simple-talk.com/books/sql-books/mastering-sql-server-profiler/
<|>
Database Engine Tuning Advisor links:
<|>
About the Tuning Log
http://msdn.microsoft.com/en-us/library/ms189242.aspx
<|>
Fine Tuning Your Database Design
<|>
http://www.simple-talk.com/sql/sql-server-2005/fine-tuning-your-database-design-in-sql-2005/
<|>
Determining Whether or Not Events Can Be Tuned
http://msdn.microsoft.com/en-us/library/ms187447.aspx
<|>

<|>

Extended Events
<|>
https://msdn.microsoft.com/en-us/library/bb630282.aspx
<|>
Start and Use the Database Engine Tuning Advisor
<|>
https://msdn.microsoft.com/en-us/library/ms174202%28v=sql.120%29.aspx
<|>
Management Data Warehouse
<|>
https://msdn.microsoft.com/en-us/library/bb677306.aspx
<|>
Data Collection
<|>
https://msdn.microsoft.com/en-us/library/bb677179.aspx
<|>
System Data Collection Set Reports
<|>
http://msdn.microsoft.com/en-us/library/cc280385.aspx
<|>
Memory Optimization Advisor
<|>
https://msdn.microsoft.com/en-us/library/dn284308.aspx
<|>
Native Compilation Advisor
<|>
https://msdn.microsoft.com/en-us/library/dn358355.aspx

—-Security topics—–
[In more general terms, the resource on which the action…]
“Principals are assigned Permissions to Securables”.
[Provide each principal with only the permissions they actually need]
Also called, “The Principal of Least Privilege.”

[When you grant permissions to a principal…]
Script file reference:
<|>
“CASCADE Permissions Test.sql”

SQL Server authentication options decided during installation:
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step14a-DBEngineConfigPage-SrvrconfigTAB-Before.png

Actually, the system stored procedure is sp_helplogins (which gives the SID of every login). We would use this on the source system and then user Create Login…WITH SID on the destination system.

For a full list of server/instance level permissions:
<|>

http://msdn.microsoft.com/library/ms188659.aspx

Database Engine Fixed Server and Fixed Database Roles [excellent graphics]
<|>
http://social.technet.microsoft.com/wiki/contents/articles/2024.database-engine-fixed-server-and-fixed-database-roles.aspx

[A better way of dealing with it is to avoid the problem overall…]
To create a user with a specific SID:
CREATE LOGIN (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms189751.aspx
When we use the ALTER AUTHORIZATION statement the new owner (principal) must be an assigned login.
<|>
Script file reference:
<|>
ALTER AUTHORIZATION.sql
<|>
By default GUEST access is disabled/revoked, so, it must be explicitly granted in order to work.
<|>
Application Roles
<|>
https://msdn.microsoft.com/en-us/library/ms190998.aspx
<|>
—-Ongoing Database Maintenance topics—–
Helpful maintenance and monitor code solutions…
<|>
Ola Hallengren’s award winning “SQL Server Maintenance Solution:
<|>
https://ola.hallengren.com/
<|>
Adam Machanic’s “Who is Active” (most recently v.11.11)
<|>
http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx
<|>
Brent Ozar’s “sp_Blitz® – Free SQL Server Health Check Script”
<|>
http://www.brentozar.com/blitz/
<|>

Don’t forget to check out some of Brent’s other solutions like “sp_BlitzIndex”.

List of DBCC commands:
<|>

http://msdn.microsoft.com/en-us/library/ms188796.aspx

{regarding the SQL Agent Service} The default setting after installation is for SQL Server Agent to be started manually [screenshot]:
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step13aa-ServerConfigPage-SvcAcctsTAB-AcctsAssigned.png

Tables which are clustered are supported by a clustered index. This index contains a list of unique and sorted values within pages. The pages are logically ordered, and the rows within the page physically ordered.

—-Auditing topics—–
A note from my student Joseph Rainone:
Hi, George I noticed something In Lab 12 when setting up an Audit the lab calls for a maximum 1MB File size. When you enter in the 1MB it throws an error!If you research the error the maximum size must be 2MB. Once you correct it you can continue forward with the lab. Not sure if it was just me but wanted to point it out to you just the same.
—-Automation (Jobs) topics—–
In order to create new Job CATEGORIES you must right click on the JOBs node in SSMS and choose “Manage Job Categories”. The user interface is weird.
To define a Fail Safe Operator, right click on the SQL Server Agent, get Properties, and go to the Alert System page.
==:||::==
Job ownership can be changed using sp_Update_Job
https://msdn.microsoft.com/en-us/library/ms188745.aspx
==:||::==
msdb Database
https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-ver15
==:||::==

Note that a user must have permission to use a Proxy Account before they can specify the Proxy Account in a job step. By default, only members of the sysadmin fixed server role have permission to access all Proxy Accounts.

Permissions to access a Proxy Account can be granted to three types of security principals:
SQL Server logins
Server roles
Roles within the msdb database.
[Additional reading….system tables which store SQL Server Agent data….]
SQL Server Agent Tables (Transact-SQL)
<|>
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/sql-server-agent-tables-transact-sql?view=sql-server-ver15
<|>
(List of) SQL Server Agent Stored Procedures (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms187763.aspx
<|>
Interesting…when you go to assign a custom user as the context in which to run a T-SQL job step you must first define a Login (of course) but then define a User, and that in the Master database instead of the MSDB database, which is what I would expect.
<|>

[“There is a built-in set of proxy accounts…]
Actually, there’s a built in set of proxy CATEGORIES, not proxy ACCOUNTS.]
Script file references:
<|>
“JOB – Create Operators, Jobs, Schedules, Job Steps and Alerts.sql”
<|>
“Backup Three System Databases in one JOB.sql”
<|>
“Create_Logins_Creds_Proxies_combo.sql”
<|>
‘CREATE CREDENTIAL for AgentProxy Accounts.sql”
<|>
“CREATE LOGINs for Agent Proxy Accounts.sql”
<|>
“sp_Add_PROXY for SQLAgentProxy.sql”
<|>
“Managing Proxy Accounts.sql”
<|>
“Assign a JOB an OWNER.sql”
Not mentioned, but access is provided by using sp_Grant_Login_to_Proxy, as specified here: https://msdn.microsoft.com/en-us/library/ms187338.aspx
Windows Servers can add an SMTP server feature. Most network server operating systems support an SMTP component one way or another.
<|>
For ENTERPRISE class job management there are some third party products:
MinionWare
http://www.minionware.net/enterprise-management/?mc_cid=4e7a4b86c7&mc_eid=e52dcab7f8
and
JAMS
SQL Server Job Management Across the Enterprise with JAMS Job Scheduler
https://www.mssqltips.com/sqlservertip/3131/sql-server-job-management-across-the-enterprise-with-jams-job-scheduler/
<|>

You can also point to an SMTP Server/Exchange Server.
—-Alerts topics—–
sysmail_delete_mailitems_sp (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms190293.aspx?f=255&MSPPError=-2147217396
<|>
sysmail_event_log (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms178014.aspx
<|>

Script file references:
<|>
“FUN w Error Functions [SQL2005+].sqll”
<|>
“sp_AddMessage examples.sql”
<|>
“Create Multiple Agent Operators.sql”
<|>
Objects, in the Microsoft world, usually have associated “Properties” and “Methods”.

To define a Fail Safe Operator, right click on the SQL Server Agent, get Properties, and go to the Alert System page.
[pertaining to Operators]
Texting Mobile Phone Services via email addresses:

Verizon cell#@vtext.com
AT&T cell#@mobile.att.net
Cingular cell#@mycingular.com
Nextel cell#@messaging.nextel.com
T-Mobile cell#@tmomail.net
Sprint cell#@messaging.sprintpcs.com
Tracfone cell#@cingularme.com

Alltel PhoneNumber@message.alltel.com
US Cellular PhoneNumber@email.uscc.net
Virgin Mobile PhoneNumber@vmobl.com

Script file references:
<|>
“Fun w Error Functions.sql”, and another,
“Alerts & Notifications Script.sql”
“Create Multiple Agent Operators.sql”
“sp_AddMessage examples.sql”
“Configure_Database_Mail [mostly Tim Ford’s code].sql”

————-Data Warehousing / Warehouse / SSIS / Integration Services
class notes————-

—-Intro to Data Warehousing topics—–

[Data warehousing is a solution that organizations….]
A data warehouse should represent a single, accurate, version of the truth.

[- A centralized source of business data for analysis and decision-making….]
Keep in mind, just because someone has good information doesn’t mean they’ll make a good decision!

[It is generally agreed, however, that a data warehouse is a centralized store of business data….]
Tools >> Facts >> Conclusions >> Changes

[-A data warehouse. A relational database designed to….]
Consider your data warehouse and, more broadly, your business intelligence solution as, “The single, accurate, version of the truth.”
The data warehouse is the “final resting place” of extracted, cleansed, and transformed data useful for BI solutions.

[However, a data warehousing project often requires a deeper understanding of the key business objectives ….]
As a BI developer….think like a “suit” (decision maker); work like a “nerd”.

[SQL Server as a Data Warehousing Platform]
I liked this picture from the previous courseware version:
<|>
https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLLPictureLibrary/SQL%20Server%20As%20a%20Data%20Warehousing%20Platform%20%5BMOC%20%2020463C%5D.png
<|>
Here are the SQL Server High Availability technologies:
<|>
1) AlwaysOn Failover Clustering Instances
<|>
2) Log Shipping
<|>
3) Database Mirroring (on the way out…)
<|>
4) AlwaysOn Availability Groups
<|>
5) Database Replication
<|>
Database Mirroring is an officially deprecated technology and AlwaysOn Availability Groups are its successor.
<|>
SQL Server High Availability Technologies diagram:
<|>
http://www.e-squillace.com/tech/techdiagrams/SQL_High_Availability_Technologies.htm
<|>
[In addition to a server-level high-availability solution, you must also consider….]
Paul S. Randal from SQLSkills.com would recommend defining two disaster recovery related objectives: RPO and RTO. RPO = Recovery Point Objective, RTO = Recovery Time Objective.

[In many cases your, data sources will be relational databases for which you can use an OLEDB or ODBC provider.
When we make connections to database engines (database servers) we MUST use/specify a database API (such as OLE-DB, or ODBC) for making those connections. (API = Application Programming Interface). We can also use SNAC (SQL Native Access Client) if our source data is Microsoft SQL Server.

[A significant part in the creation of a data warehouse solution is ….]
…Sometimes up to 70% or more of the entire BI development process is consumed by ETL and DW development!

[Module 1 Lab, “Exploring a Data Warehousing Solution]
Geo: Don’t forget to point out the Lab Answer Key location.
<|>
Also, provide an overview of the lab and the master packages that will be run to stage and load the data warehouse.
<|>
https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLLPictureLibrary/Data%20Warehousing%20Solution%20Architecture%20using%20SQL%20Server%20%5BMOC%5D.png
—-Planning Data Warehouse Infrastructure topics—–
[The server and hardware infrastructure for a Business Intelligence (BI) solution is a key consideration in any BI project.]
The two mistakes related to hardware allocation are over-purchasing and under-purchasing, neither of which are desirable.

[Planning the infrastructure for a SQL Server-based data warehousing solution requires an understanding of how the various SQL Server components work together, and how their typical workloads use hardware resources.]
Consider the following points when considering data warehouse workloads (almost directly from the 10777A MOC):
<|>
Queries typically scan large numbers of rows.
<|>
Data warehouses contain relatively static data.
<|>
Nonclustered indexes can decrease performance. Although nonclustered indexes can speed up queries that return a small number of rows, for queries that return large datasets nonclustered indexes can reduce response times because of the random I/O scans that their use generates.
<|>
Partitioning can improve query response times.

[-Availability Requirements. These include when the system…]
Five 9s (Five Nines) of Availability and more (Wikipedia)…
<|>

[-{Single Server BI Architecture…SQL Server Analysis Services. Used to provide analytical data models…]
Possibly two or three Instances of SSAS:
One in Multidimensional and Data Mining mode, one in Tabular mode, and another in PowerPivot for SharePoint mode.

[-SQL Server Reporting Services…]
And also the possibility of SharePoint Server Enterprise Edition with all three of its tiers, client, application, and data.
<|>
https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLL%20Files%20to%20Xfer/Articles/SharePoint/svs-single-farm-sharepoint-services-2013.pdf

SharePoint Foundation 2013 – download:
<|>
http://www.microsoft.com/en-us/download/details.aspx?id=35488
<|>

<|>

[Depending on business requirements, you may also choose to install SQL Server Data Quality Services on the server to support…]
…and also Master Data Services.

[-Separating data warehouse and ETL workloads from analytical and reporting workloads. If…]
Many architectural options are available.

[{under the topic of Scale Out} SQL Server Analysis Services. Create a read-only copy of a multidimensional database]
-SQL Server Analysis Services
There are some very clever HA/scalability options for SSAS.
Scale-Out Querying for Analysis Services with Read-Only Databases
https://msdn.microsoft.com/en-us/library/ff795582%28v=SQL.100%29.aspx
[Under High Availability topics, -AlwaysOn Availability groups…]
AlwaysOn Availability Groups is a technology rooted in Database Mirroring, which was introduced in SQL Server 2005, but is now deprecated.
<|>
TechNet: “Reporting Services with AlwaysOn Availability Groups (SQL Server)”
<|>
http://msdn.microsoft.com/en-us/library/hh882437.aspx
[The optimal storage hardware solution for a data warehouse depends on several factors]
{Diagram} File System Design Ideas
<|>
http://www.e-squillace.com/tech/techdiagrams/DCBB_File_System_Design_Options.png

[-Use the fastest disks you can afford….]
“SQLIO Disk Subsystem Benchmark Tool “
<|>
http://www.microsoft.com/en-us/download/details.aspx?id=20163
<|>
“How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem”
<|>
https://support.microsoft.com/kb/231619/en-us
[under Considerations for Storage Hardware, -Use RAID 10, or minimally RAID 5….]
For a visual / animated RAID tutorial from JetStor see:
<|>
http://www.acnc.com/raid
[-Consider a dedicated storage area network…]
SAN Architecture diagram:
<|>
https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLLPictureLibrary/SAN%20Architecture%20-%20Sari%20dotNet%20HighAvailability.jpg

—-Designing and Implementing a Data Warehouse (the database) topics—–
[Lesson 1: Data Warehouse Design Overview]
“Design” includes modeling, then logical then physical design.
<|>
Logical design =
List of tables, their columns, and data types. Logical design also includes how the tables are related to each other.
<|>
Physical design involves the use of storage, files, filegroups, indexing, table partitioning and compression, and possibly the use of in-memory technologies.

[Before designing individual database tables and relationships, it is important to…]
The columns in a Dimension table (Dim table) are referred to as “Attributes”.
<|>
The rows in a Dim table are referred to as “Members”.
<|>
Some Attributes can be arranged into “Hierarchies”, such as:
Time: Year -> Month -> Day
Products: Major Category -> Minor Category -> specific Product
(Bike -> Mountain Bikes -> 52″RedMTNManiac”.
<|>
Hierarchies have “Levels”.

[These relationships make it possible to aggregate the sales order measures by the attributes of a product
Analytical examples (from 10777A MOC):
:::Sales revenue by salesperson
:::Sales revenue by customer
:::Sales revenue by a time period such as fiscal quarter
:::Profit by product line
:::Profit by region
:::Order quantity by product
:::Cost by product

[The method is largely based on the data warehouse design patterns identified and documented by Ralph Kimball and the Kimball Group]
Bill Inmon is another thought leader in data warehouse design.
[5. Prioritize business processes and define a dimensional model for each

Identify the grain.]
Which means…identify the “grain” or granularity (level of detail) of the fact data, which impacts the design of the fact table(s) and dimensions.

Using Table and Index Partitioning (an Enterprise Edition feature)
https://msdn.microsoft.com/en-us/library/ms162136%28v=sql.120%29.aspx
{Diagram} Table Partitioning
<|>
http://www.e-squillace.com/tech/techdiagrams/SQL_Partitioned_Table_Architecture.png
<|>
Table Partitioning References:
<|>
http://technet.microsoft.com/en-us/library/ms191174.aspx
<|>
http://technet.microsoft.com/en-us/library/ms160743.aspx
<|>
http://technet.microsoft.com/en-us/library/ms161546.aspx
<|>
[Eventually, the simple diagram will be refined to the point where…]
Data Modeling tools:
<|>
TOAD Freeware:
<|>
http://www.toadworld.com/m/freeware/default.aspx?Redirected=true
<|>
Open ModelSphere
<|>
http://modelsphere.org/

Columnstore indexes […]

Note Only supported in Enterprise Edition in SQL 2012 and 2014:
<|>
http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.110%29.aspx#Data_warehouse
<|>
Create Clustered Columnstore Index (SQL 2014 only, includes list of restrictions)
<|>
http://msdn.microsoft.com/en-us/library/dn511016.aspx
<|>
Create Columnstore Index (SQL 2012 and SQL 2014, includes list of restrictions)
<|>
http://msdn.microsoft.com/en-us/library/gg492153(v=sql.110).aspx
<|>
Niko Neugebauer’s website (expert on Columnstore indexes)
<|>
http://www.nikoport.com/
<|>

Data files and filegroups […]
Note {Diagram} Files and Filegroups
<|>
http://www.e-squillace.com/tech/techdiagrams/DCBB_Files_&_FileGroups.png
<|>
Database Partitioning Management {Codeplex}
<|>
http://partition.codeplex.com/
<|>
SQL Dev URL, Table Partitioning in SQL Server – The Basics by Cathrine Wilhelmsen
<|>
http://www.cathrinewilhelmsen.net/2015/04/12/table-partitioning-in-sql-server/
<|>
Automate Sliding Window Partition Maintenance: Part III {Series} by Hugh Scott, 2010-12-28
<|>
http://www.sqlservercentral.com/articles/Partitioning/71657/
<|>
Indexes […]
Note {Diagram} Constraints v. Indexes
<|>
http://www.e-squillace.com/tech/techdiagrams/SQLServerIndexDesignMatrix.png
[Most databases use indexes to maximize query performance, and planning them is an important part of the database design process. Before […]]
Note Index design is both science AND art, and, is a balancing act between overhead and benefit.

MSDN reference, “Columnstore Indexes Described”
<|>
https://msdn.microsoft.com/en-us/library/gg492088%28v=sql.120%29.aspx
<|>
[SQL Server 2014 Enterprise edition supports data compression at both page and row level.]
<|>
Data Compression
<|>
https://msdn.microsoft.com/en-us/library/cc280449%28v=sql.120%29.aspx
<|>
Data Compression

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression
Relevant diagram:
<|>
http://www.e-squillace.com/tech/techdiagrams/SQLServer_MDF_DatabaseFile_Internals.png

—-Creating an ETL Solution with SSIS topics—–
[{common techniques for choosing the approach for your ETL solution} -Transact-SQL]
SELECT…INTO… [but this would create a new table]
<|>
INSERT…SELECT…
<|>
BULK INSERT…
<|>
MERGE…
<|>
[What is SSIS?]
SQL Server Data Tools – Business Intelligence for Visual Studio 2013 (SSDT BI)
<|> http://blogs.msdn.com/b/analysisservices/archive/2013/03/06/sql-server-data-tools-business-intelligence-for-visual-studio-2012-released-online.aspx
<|> Microsoft SQL Server Data Tools – Business Intelligence for Visual Studio 2013 <|> http://www.microsoft.com/en-us/download/details.aspx?id=42313
Install SQL Server Data Tools (SSDT) for Business Intelligence for Visual Studio 2013 <|> https://msdn.microsoft.com/en-us/library/jj856966%28v=sql.120%29.aspx
[{In addition to the SSIS Windows service, SSIS includes: -SSIS Designer]
We have the Visual Studio shell + Business Intelligence development templates = “SQL Server Data Tools”, or SSDT. The former name was “Business Intelligence Development Studio”, or “BIDS”. <|> Visual Studio Community Edition: <|> http://www.visualstudio.com/en-us/products/visual-studio-community-vs
[-Wizards]
Package Migration Wizard / Package Upgrade Wizard
<|>
Package Configuration Wizard
<|>
Package Deployment Wizard

[-Command-line tools]
SSIS command line tools: 1) DTUTIL.exe, and another, 2) DTEXEC.exe, and, I suppose, 3) DTEXECUI.exe.
[SSIS Projects and Packages […] ]
Note This new deployment model impacts Development, Deployment, AND, Production.
Control Flow = workflow and contains TASKs and CONTAINERs connected by PRECEDENCE CONSTRAINTs.
<|>
Data Flow = defines a pipeline consisting of at least one SOURCE, at least one DESTINATION, and possibly one or more TRANSFORMATIONs (collectively called COMPONENTs), connected by a DATA FLOW PATH.
[SQL Server 2000 DTS Packages]
http://pragmaticworks.com/Products/DTS-xChange

[To upgrade SSIS packages that were built using SQL Server 2005……..]
Upgrade Integration Services Packages
<|>
https://msdn.microsoft.com/en-us/library/cc280546.aspx

[The workflow of tasks is referred to as its control flow]
[•Creating an SSIS package with a data flow that extracts a sampling of…..]
Use the “Percentage Sampling” or “Row Sampling” SSIS Transformation components.
[regarding profiling…]
Typical lie believed by client sources of data: “My data is complete and clean.”
I heard a great nickname for “Profiling Source Data” which is “hunting for treasure and land mines.”

[You can specify multiple profile requests….]
Reference on the Data Profiling Task and profile requests:
<|>
https://www.simple-talk.com/sql/ssis/sql-server-2008–ssis-data-profiling-task/
<|>
http://www.sqlservercentral.com/scripts/Code+Generation/90864/
<|>
[14.Add a Value Inclusion Profile Request profile type……]
Value Inclusion Profile Request Options (Data Profiling Task)
<|>
http://msdn.microsoft.com/en-us/library/bb934044.aspx
<|>
[…but the core of any ETL control flow is the Data Flow task…]
The “star” of the ETL “opera” is the Data Flow Task.
<|>https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLLPictureLibrary/Pavarotti2%20Star%20of%20the%20Opera%20-%20r174283_660113.jpg

OLE DB Destination (documentation reference, which talks about the FASTLOAD option)
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-destination?view=sql-server-2017

[•The table, view, or query used to extract the data…]
RULE:
<|>
From BI Developer, Dave Rodabaugh (Columbus, OH), “Always accept pain as early in the development cycle as possible.”
[Data Transformations {subheading}]
MSDN – “Integration Services Transformations”
<|>
https://msdn.microsoft.com//en-us/library/ms141713.aspx
<|>
[Row Transformations]
Note The Row Transformations, in general, are “non-blocking” transformations.
[Union All]
Union All Transformation
<|>
https://msdn.microsoft.com/en-us/library/ms141020(v=sql.120).aspx
<|>
[Merge]
Merge Transformation
<|>
https://msdn.microsoft.com/en-us/library/ms141703.aspx

[Merge Join]
Merge Join Transformation
<|>
https://msdn.microsoft.com/en-us/library/ms141775.aspx
[-Optimize queries]
RULE: Always query for the LEAST amount of data that makes sense.
https://www.developer.com/db/top-10-methods-to-improve-etl-performance-using-ssis.html
Network packet size and SSIS
https://merlecarr.wordpress.com/2013/09/09/ssis-tuningpacket-size/
http://microsoft-ssis.blogspot.com/2013/04/performance-best-practice-network.html

[…use the IsSorted property of the…]
Sort Data for the Merge and Merge Join Transformations and the use of the “IsSorted” property…
<|>
https://msdn.microsoft.com/en-us/library/ms137653.aspx
OLE DB Command Transformation
<|>
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/ole-db-command-transformation
<|>

—-Implementing Control Flow in an SSIS Package topics—–
Alternate names for “Control Flow”: “Workflow”, and the “General Contractor”. This is because Control Flow designates the “what” (Tasks & Containers) and the “when” (the workflow order).
[Control Flow Tasks {subheading}]
BOL List of Tasks
<|>
https://msdn.microsoft.com/en-us/library/ms139892%28v=sql.120%29.aspx
<|>

BOL ~ List of Task References:
<|>
https://msdn.microsoft.com/en-US/library/ms139892.aspx
<|>
Nice picture showing an organization of Control Flow Tasks (from previous courseware version):
<|>
https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLLPictureLibrary/Control%20Flow%20Tasks%20%5BMOC%2020463C%5D.png
[Execute Process]
The Execute Process Task will execute a .cmd, .bat, or a .exe file, a program intended to run with no user interface.
[WMI Tasks]
WMI Data Reader Task
<|>
https://msdn.microsoft.com/en-us/library/ms141744.aspx
<|>
WMI Event Watcher Task
<|>
https://msdn.microsoft.com/en-us/library/ms141130.aspx
[You can define precedence constraints for one of the following…]
These precedence constraints are relative to an “upstream” task or container and a “downstream” task or container.

Also talk about EXPRESSION DRIVEN constraint workflow. For example, what if I only want a downstream executable to run on the 8th of the month…then use an expression:
DATEPART(”dd”, GETDATE() ) == 8

<|>

Here’s another expression we could use to only run a downstream executable on a Tuesday or Thursday (3rd or 5th day of the week):
<|>
DATEPART(“dw”, GetDate() )== 3 || DATEPART(“dw”, GetDate() )== 5
[You can execute each package independently, as well…]
Remember, this was done twice in the Module 1 lab. We had a Stage Data.dtsx master package, and a Load Data.dtsx master package.
[Creating a Package Template {subheading}]
Codeplex is Microsoft’s Open Source Website
<|>
http://www.codeplex.com/
<|>
Below is a list of free, downloadable ETL Frameworks on CodePlex:
<|>
http://ssisetlframework.codeplex.com/
<|>
http://etlframework.codeplex.com/
<|>
http://chefetl.codeplex.com/

<|>

<|>

BIML
<|>
http://bimlscript.com/
<|>
BIML Tutorial:
<|>
http://www.sqlservercentral.com/articles/BIML/100552/
<|>
[Lesson 2: Creating Dynamic Packages]
By building dynamic packages we implement the development principles of “encapsulation” or “abstraction” or “modularity”. So, aim to implement this design principle, “Write once, run many.”
<|>
See also resources on “BIML” and “BIMLScript”:
<|>
SSIS / BIML URL, What is BIML? by Andy Leonard
<|>
http://www.sqlservercentral.com/articles/BIML/100552/
<|>
http://bimlscript.com/GetStarted/AboutBimlScript
[Variables {subheading}]
Integration Services (SSIS) Variables
<|>
https://msdn.microsoft.com/en-us/library/ms141085%28v=sql.120%29.aspx
[Note: For a full list of system variables…]
SSIS System Variables
<|>
https://msdn.microsoft.com/en-US/library/ms141788%28v=sql.120%29.aspx
<|>
[-Implement conditional logic…]
You saw an example of implementing conditional logic using an expression within a Precedence Constraint.
[Parameters {subheading}]
Parameters are new in SQL Server 2012 and are used to pass values EXTERNALLY into SSIS. And, their values cannot change during execution. However, you can assign a Variable value = Parameter Value and then change the Variable value. Clever!
Correction [You cannot set the scope in the Variable pane, it is determined by the object selected when you create the variable.]
FALSE: That’s the way it USE to be. Now, the Package scope is selected by default but it can be changed using a button.
[An SSIS Expression]
See also: The Expression Task, and, this:
There is a useful tool free from Codeplex called the SSIS Expression Editor & Tester which is especially useful for earlier versions of BIDS (prior to 2012) that didn’t have the Expression Task:
<|>
http://expressioneditor.codeplex.com/
[-Disable a logical subset of…]
Disable or execute a logical subset of workflow.
When you think of “transactions”, think “all, or nothing”.
<|>
Are you familiar with the TCL category of SQL statements?
<|>
http://www.e-squillace.com/tech/techdiagrams/SQLStatementCategories.png

Public note added last year by GEORGE SQUILLACE
[Configuring Failure Behavior]
The focus of this “Configuring Failure Behavior” diagram is on the interior Task, and its properties relative to the container and the PKG.
<|>
Set Package Properties {MSDN}
<|>
https://msdn.microsoft.com/en-US/library/ms137749%28v=sql.120%29.aspx
<|>
Restart Packages by Using Checkpoints
<|>
https://msdn.microsoft.com/en-us/library/ms140226.aspx
<|>

[…Supported, the task will not participate..]
INCORRECT, WILL participate. “I’ll do whatever Dad does.”

—-Debugging and Troubleshooting SSIS Packages topics—–
[…or by using the dtexec or dtutil utilities to generate a dump file. These..]
Generating Dump Files for Package Execution
<|>
http://msdn.microsoft.com/en-us/library/hh231095.aspx
<|>
[…in the sysssislog system table in a…]
Actually, the dbo.SysSSISLog table in whatever database we designate as the logging database, such as a custom database just for SSIS logging. This table will show up under the “System Tables” category, and only after the package is executed.
[-Implement event handlers to execute…]
Here’s my nickname for Event Handlers =
“Scoped, Conditional, Control Flow”
[However, the OnError and OnTaskFailed events are commonly used…]
“OnError” versus “OnTaskFailed” Event Handler in SSIS by Munish Bansal
<|>
https://munishbansal.wordpress.com/2009/02/06/%E2%80%9Conerror%E2%80%9D-versus-%E2%80%9Contaskfailed%E2%80%9D-event-handler-in-ssis/
[Handling Data Flow Errors]
Data Flow Errors – The story of misfit rows: truncation problems, and mismatched data types.
[…can specify different actions for truncations and errors…]
In fact, error handling can/must be specified column by column, and error type by error type (two error types exist).
—- Implementing a Data Extraction Solution topics—–

[Overview of Data Warehouse Load Cycles]
https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLLPictureLibrary/Overview%20of%20DW%20Load%20Cycles%20%5BMOC%2020463C%5D.png
[If this is not possible, you can load inferred members as a minimal placeholder…]
An alternate term for “inferred members” is “late arriving Facts”.
[Updating Dimension Members]
Rows in Dimension tables are referred to as “Members”. Columns in Dim tables are referred to as “Attributes”.
[How to Perform Transformations {table}, under the “Scenario” column, “Replacing NULL values]
I could use ISNULL(), COALESCE(), or a CASE expression for NULL replacement.
[Store a Primary Key and Checksum]
CHECKSUM (Transact-SQL)
<|>
https://msdn.microsoft.com/en-us/library/ms189788.aspx
<|>
“Collisions” are way less likely with the HASHBYTES (Transact-SQL) function.
<|>
https://msdn.microsoft.com/en-us/library/ms174415.aspx
<|>
Free Checksum Transformation from SQLIS.com:
<|>
http://sqlis.com/sqlis/post/Checksum-Transformation.aspx
<|>
[When using Change Tracking, a best practice…]
Free ebook by Kalen Delaney on transaction concurrency, which relates to transaction isolation levels:
<|>
https://www.simple-talk.com/books/sql-books/sql-server-concurrency-locking,-blocking-and-row-versioning/ <|> Talks about transaction isolation levels.
[Lab: Extracting Modified Data]
This lab takes much longer than the 60 minute allocation.
—-Module 8: Loading Data into a Data Warehouse topics—–
SQL Server Temporal Tables vs Change Data Capture vs Change Tracking – part 2
https://www.mssqltips.com/sqlservertip/5212/sql-server-temporal-tables-vs-change-data-capture-vs-change-tracking–part-2/

SQL Server: Similarities and Dissimilarities between Change Tracking and Change Data Capture in SQL Server
http://www.sqlservergeeks.com/sql-server-similarities-and-dissimilarities-between-change-tracking-and-change-data-capture-in-sql-server/

[Trace flag 610 was introduced in SQL Server 2008 and controls logging behavior for indexed tables.]
SQL 2014 Trace Flags
<|>
http://msdn.microsoft.com/en-us/library/ms188396.aspx
[{within a demo} …and then uses an OLE DB Command transformation to update the Deleted column…]
OLE DB Command Transformation
<|>
https://msdn.microsoft.com/en-us/library/ms141138.aspx
<|>
[Additional Reading: For more information about non-logged and…]
THIS is the correct URL on non-logged and minimally-logged operations, NOT the one listed…
<|>
https://msdn.microsoft.com/en-us/library/dd425070.aspx
[The Merge Statement]
Merge (Transact-SQL)
==::||::==
https://msdn.microsoft.com/en-us/library/bb510625.aspx

About Change Data Capture (SQL Server)
==::||::==
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server

About Change Tracking (SQL Server)
==::||::==
https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server
==::||::==

—-Data Quality Services topics—–
[DQS is installed from the SQL Server 2014 installation media…]
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step9a-FeatureSelection-SQLServerFeatureInstall.png
<|>

—-Module 10: Master Data Services topics—–
Master Data Services is available only with the following editions of SQL Server 2014:
SQL Server Feature Matrix by Edition
<|>
http://msdn.microsoft.com/en-us/library/cc645993.aspx#MDS
<|>
SQL Server 2014 Developer Edition (now free, March 31, 2016)
<|>
https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/
<|>

—-Module 11: Extending SQL Server Integration Services topics—–
SSIS Community Tasks and Components
<|>
http://ssisctc.codeplex.com/

—-Module 12: Deploying and Configuring SSIS Packages topics—–
[…such as an XML file or Microsoft SQL Server database…]
Other package configuration “repositories” were: 1) registry keys [one value at a time] and 2) Windows OS environment variables [one value at a time].
(List of) Views (Integration Services Catalog)
<|>
https://msdn.microsoft.com/en-us/library/ff878135.aspx
<|>
(List of) Stored Procedures (Integration Services Catalog)
<|>
https://msdn.microsoft.com/en-us/library/ff878099.aspx
<|>
Why is a .BACPAC file so small compared to a .bak file of the same database?
https://dba.stackexchange.com/questions/137993/why-is-a-bacpac-file-so-small-compared-to-a-bak-file-of-the-same-database
<|>

—-Module 13: Consuming Data in a Data Warehouse topics—–

————-[Analysis Services] Data Models and Reports class notes————-

—-About this Course topics—–
[Student Prerequisites, – An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.]
Think like a “suit”, but work like a “nerd”.
Determine the Server Mode of an Analysis Services Instance
<|> https://docs.microsoft.com/en-us/sql/analysis-services/instances/determine-the-server-mode-of-an-analysis-services-instance
[Module 2, “Creating Multidimensional Databases”]
Cube Security
Set Read-Contingent permissions on calculated measures
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/grant-custom-access-to-cell-data-analysis-services?view=sql-server-2017#set-read-contingent-permissions-on-calculated-measures

[Module 5, “Introduction to MDX”]
MDX = Multidimensional Expressions. In the same that relational databases are queried using “SQL”, multidimensional databases are queried using MDX.
[Module 6, “Enhancing a Cube”]
Cubes can be enhanced via: 1) Cube Actions, 2) KPIs, 3) Translations, and 4) Perspectives
[Module 8, “Introduction to DAX”]
In the same that multidimensional models are queried using “MDX”, tabular models are queried using DAX (Data Analysis Expressions).
[Microsoft SQL Server 2014 (on 20466C-MIA-SQL only)]
There are three Instances of the Database Engine:
MIA-SQL, MIA-SQL\SQL2, and MIA-SQL\SQL3
There are three Instances of Analysis Services:
MIA-SQL [Multidimensional], MIA-SQL\PowerPivot [PowerPivot for SharePoint], and MIA-SQL\SQL2 [Tabular]
—-Module 1: Introduction to Business Intelligence and Data Modeling topics—–
[However, it is common to build analytical data models on top of the…]
And these analytical data models built on top of a data warehouse focus on interactive analysis as opposed to a more standing analysis approach that reporting solutions address.
[Most businesses use software applications to process business operations…]
Heck, my mower shop uses an information system.

[The data warehouse is the central data repository on which all reporting and analysis is based.]
A data warehouse = single, accurate version of the truth. Not as easy as it sounds. What constitutes something as simple as a “sale”? When P.O. is signed? When order is shipped? When order is paid?
This approach reflects the dimensional model methodology promoted by Ralph Kimball, and …]
http://www.kimballgroup.com/
<|>

[ethodology is often referred to as a bottom-up approach.
An alternative data warehouse design, popularized by Bill Inmon, is the Corporate Information Factory (CIF) model]
<|>
http://www.inmondatasystems.com/
<|>
[Although the Kimball and Inmon methodologies, in their pure form, are designed for BI solutions that distribute data across multiple departmental data marts, it is common for organizations to begin with a Kimball-style data mart for a business subset that eventually expands into]
Craig Utley’s SSAS development series formerly at LearnMSBI.com, now at YouTube.com
<|>
https://www.youtube.com/playlist?list=PLmdiM07fIiBghc8ORem_-HZXwZqIRNzln
<|>
…and especially BI Basics 104, Why Business Intelligence Projects Fail (and what you can do about it)
<|>
https://www.youtube.com/watch?v=0oMzpNI_Ipg&index=4&list=PLmdiM07fIiBghc8ORem_-HZXwZqIRNzln
<|>
[You can use a multidimensional data model to create an Analysis Services database that contains…]
The architectural hierarchy is:
Instance of SSAS installed in Multidimensional and Data Mining mode, containing…

=1 Database, containing…
=0 Cubes, and/or
=0 Mining Models
[Consequently, most BI solutions include an element that generates reports. ]
A library of both strategic, and tactical reports are useful to workers of all kinds, from shop floor workers to information workers to executive management.
<|>
Check out this resource comparing strategy v. tactics,
<|>
http://www.dummies.com/how-to/content/strategic-planning-strategy-vs-tactics.html
<|>
[Microsoft® SQL Server® 2014 provides the core data services for a BI solution. These services include:]
SQL Server 2012 Installation – Feature Selection:
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2012/SQL2012_RTM_Install_Steps_files/Step9a-FeatureSelection-SQLServerFeatureInstall.png
[SQL Server 2014 is available in the following core editions:]
SQL Server 2014 Feature Comparison Matrix:
<|>
https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.120%29.aspx
<|>
See also SQL Server Developer Edition:
<|>
[-PerformancePoint Services. PerformancePoint Services enables BI developers to create dashboards and scorecards that…]
Microsoft purchased “ProClarity” whose technology is now incorporated into SharePoint via PerformancePoint Services.
[Microsoft Visio®. Visio is a diagramming tool that…]
All of the diagrams on this page of my website are made with Visio:
http://www.e-squillace.com/tech/techdiagrams/
[Statistics show that a surprisingly high number of BI projects fail in organizations throughout the world.]
Remember the earlier references from “LearnMSBI”. Thanks, Craig (Utley)!
[The challenge of obtaining “buy-in” from business users is…]
“Everyone is in sales.” -Luc Serriere
Spouse
Parent
Neighbor
Co-worker
…
—-Module 2: Creating Multidimensional Databases topics—–
Analysis Services Tutorials (SSAS)
<|>
https://msdn.microsoft.com/en-us/library/hh231701.aspx
<|>
Source code control solutions:
<|>
Team Foundation Server
<|>
http://www.visualstudio.com/TFS
Erick Sink’s free ebook on source code control software
<|>
http://www.ericsink.com/vcbe

Options for [Multidimensional] Analysis Services Development (3)

Online Mode (File >> Open >> Analysis Services Database)
Project Mode (File >> New >> Project >> Analysis Services Project)
[reverse engineer] (File >> New >> Project >> Import from Server)
Options for [Tabular] Analysis Services Development (3)
Analysis Sevices Tabular Project
Import from PowerPivot
Import from Server (Tabular)
SQL Server Data Tools for BI (SSDT) = Visual Studio shell program + Business Intelligence development-specific TEMPLATES.
[For example, a simple cube could have a Sales Revenue measure and Time, Product, and Customer dimensions]
“Measures” are numeric values (quantities or currency amounts) that we want to aggregate (summarize/SUM, but other aggregates apply also).
[Most database systems are designed for Online Transaction Processing (OLTP). Many small updates and inserts are happening in real time
OLTP workload pattern = small, random, reads and writes. OLAP workload pattern = large, sequential, READs.
[The Concepts of Multidimensional Analysis]
Picture of a Cube:
<|>
https://dl.dropboxusercontent.com/u/4219804/OLL%20Files/OLLPictureLibrary/Cube_MSDN_IC136050.gif
<|>
[Enhancements to the schema from the underlying data sources, including…]
One of two things is true; either the BI developer has permissions in data sources to create objects like views and make changes such as adding computed columns…or they don’t. If the answer is “no” then SQL Server Data Tools for BI (SSDT for BI) permits creation of like objects.
Also, always accept pain as early in the development cycle as possible. ~ Dave Rodabaugh.
[Software Requirements for Multidimensional Analysis]
Hardware and Software Requirements for Installing SQL Server
<|>
http://msdn.microsoft.com/en-us/library/ms143506.aspx
<|>
[Creating a Data Source]
Create a Data Source (SSAS Multidimensional)
<|>
http://msdn.microsoft.com/en-us/library/ms175455.aspx
<|>
Set Impersonation Options (SSAS – Multidimensional)
<|>
https://msdn.microsoft.com/en-us/library/ms187597.aspx

[DEMONSTRATION, Modify a Data Source View step]
Here’s another possible expression:
AGE = DateDiff(yyyy,Birthdate,GetDate())
[A cube is a multidimensional structure containing dimensions and measures]

Microsoft SSAS product samples URL including multidimensional and tabular model projects:
<|>
http://msftdbprodsamples.codeplex.com/releases/view/55330
<|>
[Options for Creating a Cube]
The “top down” approach defines the cube shape first (Measures, Dimensions and Attributes), then creates the relational warehouse structure to support the cube, to be populated by ETL later.
The “bottom up” approach (way more typical) begins with a data source, then data source view, then identifies tables in the DSV which as Fact Tables and/or Dimension Tables (and the desired columns) to be included in the cube structure.

[Use the Cube Wizard to create a cube quickly and easily:]
In the end, a Cube is essentially an XML document…much more easily initially created by a wizard, and further developed by GUI interfaces!

[The Cube Wizard shows you how to specify the data source view and…]
SQL 2008 Cube building screenshots (still applicable):
From SQL 2008 on up the Cube Wizard uses the “minimalist approach” to cube building (thanks, Craig Utley), whereas SQL 2005 used an “everything and the kitchen sink” approach.
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2008/SSAS/SQL2008_CubeWiz_BU/CubeWiz_BottomUp_SQL2008.htm
<|>
[You can either use a Server Time dimension or a dimension table containing time data]
Create a Date type Dimension
==||==
https://msdn.microsoft.com/en-us/library/ms174884.aspx
==||==
[Before browsing cube data in the Cube Browser tab, the cube must be deployed.]
Deployment =
Validate (xml data) +
Copy (xml documents to an SSAS Server instance) +
Process (create file structures, partitions, and aggregations)

[After you install an instance of SSAS, only members of the server role have server-wide permissions to perform any task within it.]
SSAS Security webinar delivered by Mr. Chris Webb,
<|>
http://pragmaticworks.com/Training/Details/Advanced-Tips-and-Tricks-for-SSAS-Multidimensional-Security
<|>
Remember this phrase: “Principals are assigned Permissions to Securables”.
[Lab: Creating a Multidimensional Database]
The third exercise states the requirement that:
<|>
You need to modify dimension attributes used for dates in your cube to ensure uniqueness across temporal periods. For example, the month of January is not unique because it occurs in every year. You must modify the month attribute so that its key is based on both month and year, and then specify which of these key columns should be used when displaying the attribute name.”
<|>
…but in the lab neither of these requirements are addressed.
[Add a calculation named Full Name to the Customer table. Use the following MDX expression:…]
Actually, I believe in the DSV this would be a T-SQL expression.

Roles and Permissions (Analysis Services)
<|>
https://msdn.microsoft.com/en-us/library/bb500251.aspx
and
Grant custom access to dimension data (Analysis Services)
<|>

https://msdn.microsoft.com/en-us/library/ms175366.aspx (See step 5 under “Basic Dimension Security”.)

—-Module 3: Working with Cubes and Dimensions topics—–
Dimension Attribute Properties Reference
<|>
https://msdn.microsoft.com/en-us/library/ms174919.aspx
<|>
The default error handling instruction file is found here:
<|>
c:\program files\microsoft sql server\MSAS10.MSSQLServer\OLAP\Config\MSMDSRV.ini
<|>
Microsoft® SQL Server® 2008 MDX Step by Step (Step by Step Developer)
<|>
https://www.amazon.com/Microsoft-SQL-Server-2008-Step/dp/0735626189/ref=sr_1_1?ie=UTF8&s=books&qid=1277334041&sr=1-1
<|>

[Analysis Services dimensions contain attributes that correspond to columns in dimension tables.]
Terms:
Table -> Dimension or Measure Group (conditional)
Column (in a Dim table) -> Attribute
Hierarchy (natural, and others)
Level (of a Hierarchy)
Row (in a Dim table) -> Member
Fact Table -> Measure Group
Fact -> Measure

[The two dimension storage modes in Analysis Services are…]
Dimension Designer in SSDT >>
Select a Dimension (Attributes pane, upper left) >>
StorageMode property.

[The Cube Wizard and Dimension Wizard create attributes for a dimension.]
In Dimension Designer (Database Dim)
Select an Attribute

[To control output from attributes, you can define the column that uniquely identifies attribute values]
Dimension Designer (Database Dim)
Select an Attribute
+Key Column
–Source

—	–TableID
–Datatype
–Datasize
…
[Initially all dimensions have a type of Regular, but…]
Dimension Designer >>
(Database Dim selected) >>
Type property (Basic section)

[Parent-Child Hierarchies]
Attempt to avoid Parent-Child Hierarchies.
<|>
Codeplex tool: Analysis Services Parent-Child Dimension Naturalizer
(read the part about “why this tool can be useful”)
<|>
http://pcdimnaturalize.codeplex.com/
<|>

[under the “Ragged Hierarchies” section, these are formed from different dimension table columns rather than from a self-referencing relationship…]
The HideMemberIf property is the property of a “Level” (of a user-defined hierarchy).
==||==
From Expert Cube Design
“The alternative to using a parent/child hierarchy is to build a regular user hierarchy and then hide certain members in it to make it look ragged by setting the HideMemberIf property of a level.

[The HideMemberIf property makes a regular hierarchy ragged. There are five possible values for this property:]
The HideMemberIf property is a property of a LEVEL in a hierarchy.

[There are several important hierarchy properties:]
These are properties of an Attribute.
[The IsAggregatable property defines whether an All level is created.]
The “IsAggregatable” property is a property of an Attribute in the Attributes pane of the Dimension Structure TAB.
Default = true. Advanced category.
[The Attribute HierarchyOrdered property…]
Default = true. Misc category.
[The Attribute HierarchyOptimizedState property…]
Default = FullyOptimized.

[{under the AttributeHierarchyEngabled property} This is useful if the attribute is providing detail, but]
Use the AttributeHierarchEnabled property = False for picture columns, email addresses and phone numbers, that is, columns that won’t be used in user-defined hierarchies, and won’t be used for slicing and dicing.
[Attribute Relationships]
Video 109 at:
<|>
https://www.youtube.com/watch?v=slnlWEeNZ9I
<|>

[{under “Sorting Attributes”} You can choose any attribute by which to sort the hierarchy.]
Properties of an Attribute
OrderBy property
Values:
–Key
–Name
–AttributeKey
–AttributeName

There is also an OrderByAttribute property.

[Some hierarchies have no natural levels, so…]
Example:
DimCustomer in Dimension Designer
–properties of Yearly Income Attribute
–|–DiscretizationMethod property

—-Module 4: Working with Measures and Measure Groups topics—–

Visual Studio custom number formatting:
<|>
http://msdn.microsoft.com/en-us/library/59bz1f0h%28vs.90%29.aspx

—-Module 5: Introduction to MDX topics—–
MDX is used for the following: 1) Calculated Measures, 2) Key Performance Indicators (KPIs) [Goal, Status, and Trend calculations], 3) Conditions of an Action, 4) Named Sets, 5) Security Assignments, 6) MDX queries and 7) datasets in SSRS
<|>
Free software tool: MDX Studio:
<|>
http://www.sqlbi.com/tools/mdx-studio/
<|>
LearnMSBI.com MDX videos, #19 and #21
<|>
http://learnmicrosoftbi.com/Videos/tabid/75/Default.aspx
<|>
MSDN; Tuples
<|>
http://msdn.microsoft.com/en-us/library/ff487118.aspx
<|>
“The tuple is formed by a combination of dimension members, as long as there are no two or more members that belong to the same hierarchy.”
<|>
From MDSN, SELECT Statement (MDX)
<|>
http://msdn.microsoft.com/en-us/library/ms146002.aspx
<|>
MDX Function Reference at MSDN:
<|>
https://msdn.microsoft.com/en-us/library/ms145970.aspx
<|>
Consider the use of this CodePlex tool:
<|>
http://olappivottableextend.codeplex.com/
<|>

<|>

Former consultant with Microsoft Consulting Services,

Craig Utley

http://LearnMicrosoftBI.com

Now, all the videos have been moved to YouTube.

SSAS URL, Learn Microsoft BI by Craig Utley development video URLs
<|>
https://www.youtube.com/playlist?list=PLmdiM07fIiBghc8ORem_-HZXwZqIRNzln

Basics 104 Why BI Projects Fail
<|>
https://www.youtube.com/watch?v=0oMzpNI_Ipg&index=4&list=PLmdiM07fIiBghc8ORem_-HZXwZqIRNzln

==========

http://SQLBI.com

Marco Russo
Alberto Ferrari
Chris Webb

Expert Cube Development ….
<|>

https://www.packtpub.com/networking-and-servers/expert-cube-development-microsoft-sql-server-2008-analysis-services

SSAS documentation link:
<|>
https://docs.microsoft.com/en-us/sql/analysis-services/analysis-services?view=sql-server-2017
<|>
RAID tutorial:
<|>
http://www.acnc.com/raid
<|>
SSAS Performance Monitor Counters
<|> https://docs.microsoft.com/en-us/sql/analysis-services/instances/performance-counters-ssas?view=sql-server-2017
<|>
https://blogs.msdn.microsoft.com/sqldev/2011/03/05/ssas-performance-monitor-counters/
<|>
FIX: Performance Counters are missing after the installation of SSAS 2017 in tabular mode
<|>
https://andyhogg.wordpress.com/2014/10/03/repairing-dropped-ssas-performance-counters
<|>
https://support.microsoft.com/en-us/help/4056328/performance-counters-are-missing-after-the-installation-of-ssas-2017

Visual Studio custom number formatting: <|> http://msdn.microsoft.com/en-us/library/59bz1f0h%28vs.90%29.aspx

Free ebook:
Version Control by Example
<|>
https://ericsink.com/vcbe/index.html

SSAS URL, “SQL Server Analysis Services Multidimensional vs Tabular” by Mark Frawley (Tallan’s Blog)
<|>
https://blog.tallan.com/2016/12/09/sql-server-analysis-services-multidimensional-vs-tabular/

Multidimensional v. Tabular:
<|>
https://msdn.microsoft.com/en-us/library/hh212940.aspx
<|>
http://www.jamesserra.com/archive/2012/04/sql-server-2012-multidimensional-vs-tabular/
<|>
https://christianwade.wordpress.com/2013/11/09/multidimensional-or-tabular/
<|>
http://joshuafennessy.com/2012/02/25/multi-dimensional-or-tabular-which-model-to-use/
<|>
https://msdn.microsoft.com/en-us/library/hh994774.aspx <|>

============

“Tools for DAX and Tabular Developers”
<|>
http://www.sqlbi.com/articles/tools-for-dax-and-tabular-developers/

“DAX Guide” from SQLBI
<|>
https://dax.guide/

“DAX FORMULA REFERENCE GUIDE DOWNLOAD”
<|>
https://enterprisedna.co/dax-formula-reference-guide-download/?__s=qm6sqqqmfzaqs6srmjtc

BIDS helper
https://bideveloperextensions.github.io/

—-Module 6: Enhancing a Cube topics (KPIs, Actions, Perspectives, Translations)—–

Translations in Multidimensional Models (Analysis Services)
<|>
https://msdn.microsoft.com/en-us/library/hh230908.aspx

—-Other Multidimensional topics that were in the 6234 course—–
Deploying and Securing an Analysis Services Databases
Multidimensional Model Solution Deployment
<|>
https://msdn.microsoft.com/en-us/library/ms174869.aspx
<|>
There are five SSAS Deployment Techniques

Visual Studio / SSDT
a. No Automation/ all manual
b. Require deployment permissions for the Dev
c. Performing a BUILD creates the ProjectName.asdatabase file in the \bin subfolder
Deployment Wizard (from Start menu, “Deployment Wizard”, or locate the following executable:
a. The Microsoft.AnalysisServices.Deployment.exe file (in the system path) launches the Deployment Wizard
b. The wizard requires the .asdatabase file (also called “Database File”), produced from a BUILD operation.
c. The wizard can be used to create an .xmla file (a.k.a. Deployment Script). See next section.
d. Can be run in interactive mode or command prompt mode
XML/A Script
a. The script can be generated from SSMS by right clicking on an already deployed database and choose Script Database As… or,
b. Requires an .xmla file (a.k.a. Deployment Script or Deployment Utility)
c. The script can be executed in SSMS
d. The file can be edited to customize deployment
e. Use this method to add additional logic or replace some object names with variables.
Synchronize Database Wizard
a. Initiated by right-clicking on the “Databases” node within the target SSAS connection in SSMS.
b. The source and destination servers/instances cannot be the same.
c. Provides one-way synchronization.
Backup and Restore
a. A basic deployment method.
b. No abilityto modify the target database before deployment
c. No ability to incrementally update objects.
<|> <|> Processing Analysis Services Objects
<|>
https://msdn.microsoft.com/en-us/library/ms174860(v=sql.130).aspx
<|>
Processing Options and Settings (Analysis Services)
<|>
https://msdn.microsoft.com/en-us/library/ms174774.aspx
Objects that can be Processed:
Database
Cube
Dimension
Measure Group
Partition of a Measure Group
Mining Structure and Mining Model
Maintaining Multidimensional Solutions
Free ebook, Mastering SQL Server Profiler
<|>
https://www.simple-talk.com/books/sql-books/mastering-sql-server-profiler/

—-Module 7: Implementing an Analysis Services Tabular Model topics—–
Great reference, Data Model specification and limits (like max rows per worksheet in Excel)
<|>
https://support.office.com/en-us/article/Data-Model-specification-and-limits-19AA79F8-E6E8-45A8-9BE2-B58778FD68EF
<|>

—-Module 8: Intro to DAX topics—–

—-Module 9: Implementing Reports with SSRS topics—–
Microsoft’s Reporting Services Team Blog
<|>
https://blogs.msdn.microsoft.com/sqlrsteamblog/

—-Module 10: Enhancing Reports with SSRS topics—–
Dynamic ToolTip expression property of an image object that could be used for an Image (day greeting):
=”Happy ” & WeekdayName(DatePart(“w”, Today() )) & ” to you!”

Subreports (Report Builder and SSRS)
<|>
https://msdn.microsoft.com/en-us/library/dd239314%28v=sql.120%29.aspx
<|>

—-Module 11: Managing Report Execution and Delivery topics—–

Data Alerts (MSDN):
<|>
https://technet.microsoft.com/en-us/library/gg492252%28v=sql.110%29.aspx

—-Module 12: Delivering BI with SharePoint PerformancePoint Services topics—–

—-Module 13: Performing Predictive Analytics with Data Mining topics—–
Book, Data Mining with Microsoft SQL Server 2008
<|>
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0470277742.html
<|>
Data Mining references:
<|>
http://www.e-squillace.com/tech/techreference/SQLBI/SQLBI.htm#Data_Mining
<|>
Screenshots on the creation of a Data Mining solution:
<|>
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2005/SSAS/ImplDataMining/ImplDataMining.htm
<|>

Rule: Always maintain the smallest library of reports possible
. <|>
Use Parameters to make reports more dynamic & to reduce the overall # of reports required. “Write once, run many.”
<|>
Outstanding, free, one hour recorded webinar by Mike Davis of Pragmaticworks: Using Parameters in SQL Server Reporting Services
<|>
http://pragmaticworks.com/training/details/webinar-284
<|>

————-Updating Skills to SQL 2016 class notes————-

—-Module 2: What’s New in SQL Server Performance—–
Columnstore Indexes Guide
<|>
https://msdn.microsoft.com/en-us/library/gg492088.aspx
<|>
Other related BOL topics:
<|>
Columnstore Indexes Versioned Feature Summary – includes what’s new.
<|>
Columnstore Indexes Data Loading
<|>
Columnstore Indexes Query Performance
<|>
Get started with Columnstore for real time operational analytics
<|>
Columnstore Indexes for Data Warehousing
<|>
Columnstore Indexes Defragmentation
<|>

Introduction to Memory-Optimized Tables
<|>
https://msdn.microsoft.com/en-us/library/dn511014.aspx
<|>
Monitoring Performance By Using the Query Store
<|>
https://msdn.microsoft.com/en-us/library/dn817826.aspx
<|>
Live Query Statistics
<|>
https://msdn.microsoft.com/en-us/library/dn831878.aspx
<|>
(from MSSQL Tiger Team [SQLCAT]) https://blogs.msdn.microsoft.com/sql_server_team/query-progress-anytime-anywhere/
<|>
Format Query Results as JSON with FOR JSON (SQL Server)
<|>
https://msdn.microsoft.com/en-us/library/dn921882.aspx
<|>
Temporal Tables
<|>
https://msdn.microsoft.com/en-us/library/dn935015.aspx
<|>

—-Module 3: What’s New in SQL Server Security—–
Always Encrypted Cryptography
<|>
https://msdn.microsoft.com/en-us/library/mt653971.aspx
<|>
Row-Level Security
<|>
https://msdn.microsoft.com/en-us/library/dn765131.aspx
<|>
Dynamic Data Masking
<|>
https://msdn.microsoft.com/en-us/library/mt130841.aspx
<|>

—-Module 4: What’s New in SQL Server Availability and Scalability—–
Overview of Always On Availability Groups (SQL Server)
<|>
https://msdn.microsoft.com/en-us/library/ff877884.aspx
<|>
Basic Availability Groups (Always On Availability Groups)
<|>
https://msdn.microsoft.com/en-us/library/mt614935.aspx
<|>
Working with tempdb in SQL Server 2005
<|>
https://technet.microsoft.com/en-us/library/cc966545.aspx
<|>

—-Module 5: What’s New in SQL Server Reporting and BI—–
What’s new in Reporting Services (SSRS)
<|>
https://msdn.microsoft.com/library/ms170438.aspx
<|>
Report Server ExecutionLog and the ExecutionLog3 View
<|>
https://msdn.microsoft.com/en-us/library/ms159110.aspx
<|>
Troubleshooting Reports: Report Performance
<|>
https://technet.microsoft.com/en-us/library/bb522806(v=sql.105).aspx?f=255&MSPPError=-2147217396#Render.aspx
<|>

—-Module 6: What’s New in SQL Server Data Access—–
PolyBase Guide
<|>
https://msdn.microsoft.com/en-us/library/mt143171.aspx
<|>

Polybase (Microsoft’s acquisition of data appliance company DATAllegro)
<|>
https://msdn.microsoft.com/en-us/library/ff878135.aspx
<|>
https://www.mssqltips.com/sqlservertip/4080/introduction-to-polybase-in-sql-server-2016–part-1/
<|>
http://www.sqlshack.com/sql-server-2016-polybase-tutorial/
<|>
http://www.desertislesql.com/wordpress1/?p=676
<|>
http://searchsqlserver.techtarget.com/feature/How-to-get-started-with-PolyBase-in-SQL-Server-2016
<|>
Polybase videos:
http://www.bing.com/videos/search?q=sql+2016+polybase+tutorial&view=detail&mid=B8163DF694A1747F9E5CB8163DF694A1747F9E5C&FORM=VIRE
<|>
http://www.bing.com/videos/search?q=sql+2016+polybase+tutorial&view=detail&mid=9CD95FDD6C30B50F6DF39CD95FDD6C30B50F6DF3&FORM=VIRE
<|>

—-Module 7: New and Enhanced Features in SQL Server OLAP—–

—-Module 8: What’s New for SQL Server in the Cloud—–

————-Analyzing Data with Power BI class notes————-

Power BI Dashboard v. Report

Dashboard vs Report; Differences At a Glance – Power BI
http://radacad.com/dashboard-vs-report-when-where-why-which-to-use

============

Power BI Dashboard vs Report
https://www.educba.com/power-bi-dashboard-vs-report/

==============

Power BI – Dashboards vs. Reports
https://www.skillslogic.com/blog/dashboards-data-warehousing/power-bi-dashboards-vs-reports

==============

Power BI Reports vs Dashboards
https://www.powerbitutorial.org/dashboards/power-bi-reports-vs-dashboards/

===========
Power BI – Sharing a Report v. Dashboard

Share your Power BI dashboards and reports with coworkers and others
https://docs.microsoft.com/en-us/power-bi/service-share-dashboards

Dashboard Sharing, and Manage Permissions in Power BI; Simple, but Useful?
http://radacad.com/dashboard-sharing-and-manage-permissions-in-power-bi-simple-but-useful

YouTube, HOW TO SHARE POWER BI REPORTS AND DASHBOARD
https://www.youtube.com/watch?v=_jrnqOVKyuk

sharing dashboards vs reports
https://community.powerbi.com/t5/Desktop/sharing-dashboards-vs-reports/td-p/28238

Sharing Power BI Reports and Dashboards

https://go.christiansteven.com/bi-blog/power-bi/sharing-power-bi-reports-and-dashboards

DAX Cheatsheet for PowerPivot by Jamie McAllister (list of every DAX function hyperlinked to MSDN)
<|>
http://www.the-north.com/sharepoint/post/2013/06/10/DAX-Cheatsheet-for-PowerPivot

Power BI URL, Dynamic PowerBI Reports Via DAX! by Ryan Durkin with slicers and a .pbix file link
<|>
http://www.bipatterns.com/dynamic-powerbi-reports-via-dax/

DAX URL, “Tools for DAX and Tabular Developers”
<|>
http://www.sqlbi.com/articles/tools-for-dax-and-tabular-developers/
DAX Studio
DAX Formatter
DAX Editor
DAX Patterns
SSAS Tabular Translator
Vertipaq Analyzer
BIDS Helper
BISM Normalizer (trial edition)
OLAP PivotTable Extensions
Power Pivot Utilities
Workbook Size Optimizer

http://community.powerbi.com/t5/Service/How-can-I-create-and-deploy-a-custom-visualization/td-p/939

https://powerbi.microsoft.com/en-us/documentation/powerbi-custom-visuals-add-to-report/

Power BI Custom Visuals Class (Module 01 – Custom Visuals Introduction)

Managing Business Intelligence Operations class notes————-

SSIS Reporting Pack ssisreportingpack
https://archive.codeplex.com/?p=ssisreportingpack
Monitoring 201: Fundamentals of IT Monitoring (whitepaper from SolarWinds, vendor agnostic)
https://www.mssqltips.com/sql-server-whitepaper/154/monitoring-201-fundamentals-of-it-monitoring/
https://www.mssqltips.com/whitepapers/SolarWinds-Monitoring-201.pdf

Provisioning SQL Databases (20765, currently) – class notes————-

Module 7 – Planning to Deploy SQL Server on Microsoft Azure
One of the demonstrations in this module requires PowerShell integration with Azure, which requires the “Microsoft Web Platform Installer Package” being already installed, and IT’S NOT ALREADY INSTALLED. Use this URL to download and install the package:
==||==
http://go.microsoft.com/fwlink/p/?linkid=320376&clcid=0x409
==||==
Installing the Azure PowerShell Service Management module
==||==
https://docs.microsoft.com/en-us/powershell/azure/install-azurerm-ps?view=azurermps-5.0.0
==||==
[This article may also be helpful] How To Connect to Microsoft Azure with PowerShell
==||==
https://redmondmag.com/articles/2016/01/25/connect-to-microsoft-azure-with-powershell.aspx
==||==
Map of Azure data centers in Azure Regions
==||==
https://azure.microsoft.com/en-us/regions/

==||==

20466 class notes
BI Developer Extensions for Visual Studio 2017 (BIDS Helper), a Visual Studio plug-in
==||==
https://bideveloperextensions.github.io/downloads/

Monitoring Report Execution – SCRUBS archive on Codeplex
==||==
https://archive.codeplex.com/?p=scrubs

Screenshot, SSAS Configuration [install modes]
==||==
http://www.e-squillace.com/tech/screenshotgallery/SQL/SQL2016/SQL2016_Install_screenshots/Step16a-AnalysisServicesConfig_ServerConfigTAB_default.PNG
==||==
Recommended book, Microsoft® SQL Server® 2008 MDX Step by Step
==||==
https://www.amazon.com/Microsoft-SQL-Server-2008-Step/dp/0735626189/ref=sr_1_1?ie=UTF8&s=books&qid=1277334041&sr=1-1

         ==||==

Recommended book, Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model
==||==
https://www.amazon.com/gp/product/0735658188/ref=s9_psimh_gw_p14_d0_i2?pf_rd_m=ATVPDKIKX0DER&pf_rd_s=center-2&pf_rd_r=172BVG7CVFH36BHV406W&pf_rd_t=101&pf_rd_p=1389517282&pf_rd_i=507846
==||==
Documentation reference, MDX Query and Slicer Axes – Specify the Contents of a Query Axis
==||==
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/mdx/mdx-query-and-slicer-axes-specify-the-contents-of-a-query-axis

OLAP PivotTable Extensions – Add-in for Excel:
==||==
http://olappivottableextend.codeplex.com/
==||==
Actions in Multidimensional Models
==||==
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/actions-in-multidimensional-models?view=sql-server-2017

===========
SSAS URL, “Tabular vs Multidimensional models for SQL Server Analysis Services” by Daniel Calbimonte
<|>
https://www.mssqltips.com/sqlservertip/4154/tabular-vs-multidimensional-models-for-sql-server-analysis-services/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20181220
<|>
SSAS URL, “SQL Server Analysis Services Multidimensional vs Tabular” by Mark Frawley (Tallan’s Blog)
<|>
https://blog.tallan.com/2016/12/09/sql-server-analysis-services-multidimensional-vs-tabular/
<|>
Multidimensional v. Tabular:
<|>
https://msdn.microsoft.com/en-us/library/hh212940.aspx
<|>
http://www.jamesserra.com/archive/2012/04/sql-server-2012-multidimensional-vs-tabular/
<|>
https://christianwade.wordpress.com/2013/11/09/multidimensional-or-tabular/
<|>
http://joshuafennessy.com/2012/02/25/multi-dimensional-or-tabular-which-model-to-use/
<|>
https://msdn.microsoft.com/en-us/library/hh994774.aspx
<|>

Tools for DAX and Tabular Developers
<|>
https://www.sqlbi.com/articles/tools-for-dax-and-tabular-developers/
<|>
The DAX language – DAX Guide {SQLBI.com}
<|>
https://dax.guide/

Power BI Bookmark file {with links to many DAX learning resources}
<|>
https://bit.ly/2OZpXNC

Roles and Permissions (Analysis Services) {this is the parent of many security topics and links}
==||==
https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/roles-and-permissions-analysis-services?view=sql-server-2017