Business Intelligence for Casino (Gaming) Industry and its use cases

Casino industry is relatively small industry when compared to other large industries like Healthcare, Retail, and Manufacturing.  However any given casino,  during its daily operation is capable of collecting massive amounts of data related to people, devices, and activity (gaming).

The information systems (usually called Casino Management Systems) which help casinos with daily operations have come a long way- thanks to efforts and investments from industry leading service providers like IGT, Bally, Aristocrat, Konami, etc.  These systems along with supplementary systems are capable of capturing operations data (data describing activity, people, and devices) across almost all the touch points possible.  With this capability of capturing such volume of data comes a challenge of harnessing the intelligence this data has to offer and build a competitive advantage within the industry.

The need to build these Decision Support Systems has been acknowledged by Casino industry and a significant amount of efforts and investments have been made in the last couple of decades.  In this regard, I would like to outline some of the possible business use cases of these DSS (now called Business Intelligence (BI) Systems which are by definition are slightly better and improved than age old DSS).  The following provides an abstract categorization of reporting and analytics use cases for a typical BI system in a Casino organization.

• Customer Trends and Analyses
• Revenue Trends and Analyses
  • Casino
  • Food & Beverage
  • Hotel
  • Other
• Reinvestment Trends and Analyses
• Team Performance Trends and Analyses

In the near future I would discuss about Critical Success Factors of a good BI implementation and adoption in a Casino organization.

Why Dimensional Modelling?

In context of building a BI (Business Intelligence)/Reporting/Analytics systems in organizations, often there are people involved who are challenged by this question of Why do we dimensional model? Can’t we achieve whatever is required with what (read OLTP DB) we already have? How different is a dimensional model from a transactional system database (3NF)?

Before I answer the questions of why your organization would need a dimensional model, let me briefly discuss what is a dimensional model.

What is a Dimensional Model?

A dimensional model is a relational database which consists of fact tables and dimension tables primarily.  Fact tables typically feed on data from transaction tables from transactional system while dimension tables feed on look up tables (aka reference tables) from transactional system.  A note here is that most of the transactional systems’ databases are in third normal form (aka 3NF). Apart from fact tables and dimension tables there are also Summary tables, Fact-less fact tables (aka event tables) included in most of the DW implementations.

With this basic information let us try to answer the big question(s):

Why a dimensional model?

A dimensional data model is a relatively de-normalized data model optimized for analytics and reporting purposes.  This is exactly the reason why almost all data warehouses and data marts are modeled based on dimensional modeling.  Not satisfied with the length of this short answer and are you looking for more reading? Continue to read..

On one hand we all know and deal with a 3NF (3^rd Normal Form) transactional databases (Eg: A banking application database). For these databases, the requirement is to handle a large volume of database transactions.  Hence the tables need to be adequately normalized so that each transaction detail can be registered with as little data as possible while fully retaining transaction information. The mantra here is dealing with less data is good. Any overhead data beyond what is required need to be eliminated (just transaction IDs, entity IDs and Amounts related to a transaction).

On the other hand we use reporting and analytics systems as well (Eg: Bank/Utilities account statements, Stock market indices).  For these databases, the requirement is to have capacity to return all the information that a user might look for when he interacts with this database.  The transaction volume for reporting systems is relatively very low compared to a transactional system.  Also in many cases, a copy of report generated once can be used for a relatively prolonged period of time without refreshing for every request.  Unlike transactional system, the usage and emphasis is not on capturing a transaction but the requirement is reporting the captured transaction (by OLTP).  Hence there will be a overhead of necessity to provide labels and descriptions for all transactions and entities (vs. just IDs in case of a transactional system).

In summary, transactional systems (OLTP) which are in 3NF are optimized for capturing data.  Reporting and Analytics systems (OLAP) which are in dimensional model are optimized for reporting data.

SQL Server 2012 Top Features

What are top features of Microsoft SQL Server 2012?

I asked SQL Server community, a really really active group on Twitter, about their choice of top features for SQL Server 2012 using #sqlhelp hash tag.  The following is the list of features (in no particular order) that I got in reply.

• Always On
• File Tables
• Contained Databases
• SQL Server 2012 Licensing
• Support for Server Core
• Availability Group
• Column Store Indexes
• File Table
• Extra Warnings in Execution Plans
• Improved Intellisense
• XEvents

The following are the twitter replies that I’ve received when I posted to #sqlhelp

@Pieter_VanhoveMy top 3 AlwayOn, File tables and contained databases #sqlhelp

@fatherjack well worth noting is the new licensing structure. If you are buying new h/w then consider your CPU type + count #sqlhelp

@retracement Support for Server Core *is* imho the *biggest* reason to move to SQL 2012 (but depends on your requirements) #sqlhelp

‏@SQLife #sqlhelp too long of a list for 140 chars: AlwaysOn AG, columnstore indexes, FileTable, contained db, and many many more

@SQLRockstar I like the extra warnings in execution plans and the improved intellisense in SSMS. Also XEvents got easier to use. #sqlhelp

 

Effect of Debt Crisis on IT Staffing (Contractors and Full-time)

In the past few days phrases like “Debt Crisis”, “Debt Ceiling”, “Federal Budget Deficit” are the common occurrences in the news.  So what is this exactly?

What is 2011 US Debt Crisis?

In basic terms, you incur debt when your expenses are greater than your revenues.  To give you a clear picture through numbers:

FY 2010 US Revenues: USD 2.1 Trillion

FY 2010 US Expenditure: USD 3.3 Trillion

What is the difference amount? It is USD 1.2 Trillion and that’s the US government’s annual debt.

US government’s projected expenditure for FY 2011 is USD 3.7 Trillion and an estimated USD 1.6 Trillion debt.

How much does US government spend and what does it spend on?

As mentioned, in FY 2010 US expenditure was USD 3.3 Trillion.  This expenditure can be broken down in to two major categories:

Mandatory Spending (self-explanatory):  USD 2.1 Trillion. (63%)

• Social Security Payments
• Medicare and Medicaid Benefits
• Interest payments on existing debt
• National Highways construction and maintenance
• etc

Discretionary Spending (US government can decide not to spend on this category): USD 1.26 Trillion. (37%)

• Department of Defense
• Department of Veterans Affairs
• Other discretionary spending

FY 2010 Revenue Breakdown:

• $900 Bn - Federal Income Tax revenue.   (This is that amount of money for which you, me, that guy at Mc Donald’s drive-in window, and everyone in USA who earns an income legitimately would pay as income tax to government).
• $860 Bn – Retirement contributions.  (There are several sub-categories here.  Let’s keep this simple and say that this is the amount of tax deferred dollars that people contribute towards retirement).
• $190 Bn – Corporate Income Taxes. (These are the taxes paid by corporations conducting business in USA).
• $140 Bn – Other Revenue Sources. (These sources include Gift Taxes, Custom Duties, and other miscellaneous receipts for government).
• $66 Bn – Excise Taxes. (Excise taxes are taxes that are paid on products that are produced and traded domestically).

So the above items sum up to approximately USD 2.1 Trillion of revenues.

Now the question is where does the deficit USD 1.2 Trillion does come from?

Simple- just like any common man, government seeks “DEBT“!  In return US government offers treasury bonds and IOUs to the Creditors.

[US Treasury bonds are considered as the safest (lowest risk) investment opportunities for both domestic and international investors.  No wonder they are rated AAA. Creditors will be happy too to lend money to USA in return of US treasury bonds which helps their money grow safely and a decent interest rate.  At any time, there are many eager takers for US Treasury bonds.]

You might be wondering if such is the demand for US Treasury bonds, isn’t it easy to find creditors and seek more debt?

Yes, it is easy for US government to raise debt once it decides to raise debt ceiling (debt ceiling is maximum amount proposed by House and authorized by Senate).  The challenge is democratic and republican congressman and senators arriving at consensus on that decision to raise debt ceiling.

For latest updates on this consensus process you can immediately rush to CNN, New York Times, Huffington Post, HotAir.

How does it Impact me as an IT Contractor or Employee

It will impact you directly:

• If you are employed/contracted by a US Federal Government Agency like EPA, VA, etc.
• If you are expecting any of your social security tax refunds or if you are dependent on social security checks.

It will impact you indirectly:

• If your organization/employer cater to US Federal government or its agencies or if majority of revenues come from Federal government.
• If your organization’s services are catered towards beneficiaries of Federal funding like Social Security, Medicaid or Medicare payments.

Feel free to comment and provide feedback.

Notes on Microsoft SQL Server System Databases: Master DB, Model DB, MS DB, Temp DB

What are SQL Server System Databases?

System databases are auto created when SQL Server is installed and there are 4 major types of system databases:

1. Master DB
2. MSDB
3. Model DB
4. Temp DB

Less known auto generated system databases are:

• Distribution DB
• MS SQL System Resource

Each of the above databases is used for certain purposes.

What is Master DB?

Master DB as says is truly a “master” database.  It keeps the SQL Server running.  Master DB keeps track of information about all the system and user defined databases hosted on that particular instance of Microsoft SQL Server.  Simply put, Master DB is where most of the metadata of that SQL Server Instance is processed.  Good thing is that this metadata in Master DB can be accessed for obtaining useful information especially regarding performance of that particular instance of SQL Server.

It is highly suggested to back up Master DB before and after a major change to the instance.

What is MSDB?

MSDB is the job keeper and runner.  MSDB holds metadata about the SQL Agent Jobs, SSIS jobs, Service Broker, and DB Mail.

What is Model DB?

Model DB serves as a default template for all databases that will be created on SQL Server. If changes are made to this Model DB, all the new databases that are created from then on that instance are also created along with those changes.

What is Temp DB?

Temp DB, on a very high level acts as a connection agent for all the users connecting to the databases on that particular instance.  Temp DB holds temporary objects while a connection is active and these objects are removed when the
connection goes offline.

Take a look at Narayana Vyas’s post on this topic for more information.

Useful Links – Top SQL Server resources on the web.

In the following video Kevin Kline discusses useful SQL Server Resources on the web. I have put all of them together for you to click through and bookmark.

In addition, I also recommend to regularly keep track of Brent Ozar’s Blog and Pinal Dave’s SQL Authority for very useful SQL Server related notes.

• Microsoft SQL CAT (Customer Advisory Team)
• Microsoft TechNet
• Microsoft MSDN
• DevX.com
• SQLServerPedia.com
• Quest SQL Server (Q&A)
• Quest Backstage (Give aways, Marketing, Fun)
• SQLMag.com
• SearchSQLServer.com
• SQLTeam.com
• SQL-Server-Performance.com
• SQLServerSecurity.com
• SQLIS.com
• SQLDTS.com

I still recommend to check out Kevin Kline’s video below.

SQL Server 2008 Notes Part-3: Instances, Licensing and Security Considerations

SQL Server 2008 Notes Part-1: SQL Server 2008 Editions and Explanation

SQL Server 2008 Notes Part-2: SQL Server 2008 Hardware and Software Requirements

What are SQL Server Instances

Instances are seperate sessions of SQL Server running on a machine with different instance names.  Though common libraries and other basic resources are shared across instances,  in most regards instances are pretty much independent of each other.  One of the many instances running on a machine can be brought offline/online without affecting the state of other instances.

Why should I use SQL Server Instances?

In most cases, separate instances are used for DB Engine, Analysis Services and Reporting Services.

Instances are helpful to consolidate multiple physical DB servers into one DB server with multiple instances.

Instances are a highly affective method to separate authentication/security requirements.

Microsoft SQL Server 2008 Licensing

Server plus device CAL/Server plus user CAL

SQL Server 2008 offers separate server licenses (in SQL Server 2008 Workgroup edition, SQL Server 2008 Standard edition and SQL Server 2008 EE) in conjunction with per device or per user client access licenses (CALs).  Server plus CAL licenses are useful to customers who use SQL Server 2008 in non-web-based scenarios.

What is a Server License?

A server license is required for every OS environment on which SQL Server 2008 or any of its components are running.

What is a Device CAL?

A SQL Server device CAL is required in order for a device to access or use services or functionality of SQL Server 2008.  Useful when multiple users per device.

What is a User CAL?

A SQL Server User CAL is required for a user to access or use the services or functionality of SQL Server 2008.  Useful when multiple devices per user.

Processor License

A processor license is required for each processor installed on each OS environment running SQL Server or any of its components (SSAS/SSIS/SSRS).  This license includes access for an unlimited number of users or devices to connect from either inside or outside the firewall.  Customers do not need to purchase additional CALs when licensed under the per processor model.

What if my environment has quad core/six core processors?

Multicore processors, which consist of multiple processing execution units or cores on one chip, are seen as a promising way to boost computing power.  Microsoft charges same amount per processor regardless of how many cores are in the processor.

During failover setup, a server is designated as the passive server.  A passive server does not need a license, provided that the number of processors in the passive server is equal or less than the active server. The active server should be back up in 30 days else a separate license is required for the second server.

Security Considerations

The Windows account that is specified for the service can be a local computer or a domain account.

The most common choice for the SQL Server service account is a domain user account.

This uses Windows authentication to setup and connect to SQL Server 2008 and maybe the preferred account if the service must interact with network services.

The local service account is a special, built-in account that has the same level of access to resources as members of the Users group.

SQL Server Services that are configured as a local service account can access network services as a null session with no credentials.

Note that the SQL Server Agent does not support the local service account.

Like the local service account, the network service account also has the same level of access to resources as members of the Users group.

However, it will use the credentials of the computer account when accessing network resources.

Microsoft recommends not using the network service account for the SQL Server and SQL Server Agent services.

Network service is a shareable account, and is appropriate for use as a SQL Server service account only if you can ensure that no other service uses the account that is installed on the computer.

It is recommended that a domain user account with minimal rights is used.

SQL Server 2008 Notes Part-2: SQL Server 2008 Hardware and Software Requirements

SQL Server 2008 Notes Part-1: SQL Server Editions and Explanation

What are memory/RAM requirements and recommendations for SQL Server 2008?

Minimum Memory is 512MB of RAM for all versions of SQL Server 2008 with a recommended 2GB of RAM.

Express Edition requires a minimum of 256 MB RAM and a recommendation of 1GB RAM.

32 bit versions of SQL Server 2008 requires a minimum of 1 GHZ and 64 bit requires a minimum of 1.4 GHZ.  Recommended processor speed is 2Ghz or faster.

DB Engine which includes the data files, Replication, and Full-Text search requires a minimum of 280 MB of disk space.  SSAS requires 90 MB.  SSIS and SSRS require a minimum of 120 MB each.  The client components that are used to implement and maintain SQL Server 2008 requires 850 MB of free disk space.

What are all the components that are installed along with SQL Server 2008?

• .NET Framework 3.5
• SQL Server 2008 Native Client
• SQL Server 2008 Setup support files

What are the Pre-requirements for SQL Server 2008 installation?

Requires Microsoft Windows Installer 4.5 or later and Microsoft Data Access Components (MDAC) 2.8 SP1 or a later version.  Microsoft IE 6 SP1 is required for all SQL Server 2008 installation for Microsoft Management Console (MMC), SSMS and BIDS.

Which VM environments are supported for SQL Server 2008?

SQL Server is supported in VM environments that run on the Hyper-V role in MS Windows Server 2008 SE, MS Windows Server 2008 EE and DC Edition.

OS Support notes for SQL Server 2008

Except SQL Server 2008 X64 EE and SQL Server 2008 X64 SE, all editions can be installed on any Windows X64 in a Windows-On Windows (WOW64) configuration- 32 bit subsystem of a 62 bit server- that enables 32 bit applications to execute natively in 32 bit mode.

All Windows 2008 Servers are both with and without Hyper-V installed.

There is a 64 bit edition of SQL Server 2008 Express edition for 64 bit OS.

64 bit Itanium processor systems support SQL Server 2008 Enterprise 64 bit IA64 on Windows Server 2008 Itanium edition, Windows Server 2003 SP2 64 bit Itanium EE and DC Editions.

SQL Server 2008 Notes Part-3: SQL Server 2008 Instances, Licensing and Security Considerations

SQL Server 2008 Notes Part-1: SQL Server Editions and Explanation

SQL Server 2008 Compact Edition: Free, embedded DB ideal for building stand-alone and occasionally connected apps for mobile devices, desktops and web clients on all Windows platforms

SQL Server 2008 Express Edition: Free and it is Integrated with Visual Studio. Ideal for non-professional developers and hobbyists.

SQL Server 2008 Web Edition: Low TCO option for web hosters and web sites that provide scalability and manageability capabilities for small to large scale web properties.

SQL Server 2008 Workgroup Edition: Ideal for branch location DB because it provides a reliable data management and reporting platform that includes secure, remote synchronization and management capabilities.

SQL Server 2008 Developer Edition: Includes all functionality of SQL Server 2008 Enterprise edition, but is licensed for use as a development and test system.

SQL Server 2008 Standard Edition: Designed for department-level applications. This is a complete data platform that provides ease of use and manageability.  Consider if you do not require the levels of scalability and performance offered by SQL Server 2008 Enterprise Edition.  Provides multi-instance support for 16 instances.

SQL Server 2008 Enterprise Edition: Most comprehensive version of SQL Server 2008 that is designed for high-level scalability and performance. Provides same functionality of SQL Server 2008 Standard edition and in addition

• Partitioning
• Data Compression
• Resource Governor
• 50 instances (25 instances on a failover cluster)
• Database Snapshots
• Hot-add memory and CPUs
• Mirrored Backups
• Backup Compression
• Transparent Data Encryption
• Fine-grained auditing
• Peer-to-Peer replication
• Distributed partition views
• Parallel index operations
• Change data capture
• Star join query optimization
• Data-driven subscriptions

SQL Server 2008 Notes Part-2: SQL Server 2008 Hardware and Software Requirements

Links for the Week- Friday, May 1 2009

Brent Ozar – How to get a Jr. DBA job:  Brent Ozar provides very useful guidance on getting a Jr. DBA job in this three part series.

Part-1,Part-2,Part-3

Mitch Haile’s Geek Home Office: A photo trip to Mitch Haile’s home office.  Its a dream set-up that can be a source of inspiration to million things.

SQL Server Pedia: SQL Server Pedia is a collaborative wiki about SQL Server.  It is a very useful resource on almost all SQL Server technologies and the wiki is wonderfully put together by prominent SQL Server users.