MDX Learning Notes

Reading Microsoft SQL Server 2008 MDX Step by Step (great book)
-----------------------------------------------------------
On 0 = On Columns
On 1 = On Rows

The default properties returned are VALUE and FORMATTED_VALUE for  cell. To retreive additional properties by including CELL PROPERTIES keyword in end of query.

 Cell Property           Description
-----------------------------------------------------------
ACTION_TYPE -- A bitmask indicating the type of action(s) associated with the cell.
BACK_COLOR -- A bitmask indicating the background color to use when displaying the VALUE or
                               FORMATTED_VALUE property of the cell.
CELL_ORDINAL -- The ordinal number of the cell in the cell set.
FONT_FLAGS -- A bitmask indicating whether the cell’s font should be presented using italic,
                             bold, underline, or strikeout detailing.
FONT_NAME -- The name of the font to use when displaying the VALUE or FORMATTED_VALUE property of the cell.
FONT_SIZE -- The font size to use when displaying the VALUE or FORMATTED_VALUE
property of the cell.
FORE_COLOR -- A bitmask indicating the foreground color to use when displaying the VALUE or
                             FORMATTED_VALUE property of the cell.
FORMAT --  This is the same as the FORMAT_STRING property.
FORMAT_STRING  -- The format string used to create the value of FORMATTED_VALUE
                                      property of the cell.
FORMATTED_VALUE -- The character string representation of the VALUE property formatted per
                                            the FORMAT_STRING value.
LANGUAGE -- The locale against which the FORMAT_STRING will be applied.
UPDATEABLE --  A value indicating whether the cell can be updated.
VALUE -- The unformatted value of the cell.

For Partial Tuples order....
1. Default Member
2. All Member
3. First Member

A set is a more than one tuple enclosed in braces {}.
Tuples are separted by commas.
Shared hierarchality and shard dimensionality is a requirement for inclusion within the set.

Order( {Set}, Expression [, Flag])
Flags can be ASC (default) ascending
DESC descending
BASC ascending across all members ( sort by measure only)
BDESC descending across all members ( sort by measure only)

CmdExec (running a bat or executable file via SSIS)

I was having an issue of being able to run a package that prints in BIDS but not as a SQL Server Agent job.
Even though I sent everything as run as 32 bit.

Finally tried this.

used this application to print via command line in SSIS Script Task http://www.coolutils.com/TotalPDFPrinterX

Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.ComponentModelImports System.DiagnosticsImports System.IO
Process.Start(
System.Threading.Thread.Sleep(5000)
"C:\Program Files (x86)\Total PDF PrinterX\PDFPrinterX.exe", Chr(34) + complaint_folder + "\*.pdf" + Chr(34) + "-p" + Dts.Variables("varPrinterName").Value.ToString)
System.Threading.Thread.Sleep(5000)
Dts.TaskResult = ScriptResults.SuccessJob Tye (SQL Server Agent)
Operating System (CmdExec)
----------------------------------
"D:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"  /DECRYPT  "password" /FILE "D:\PRODUCTION_FOLDER\DEPLOYED_PACKAGES\PROD\RegulatoryPrint3.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF

VS2008 Integration Services Project error: Failed to save package file .... with error 0x8002801D "Library not registered."

 run
regsvr32 msxml3.dll
and
regsvr32 msxml6.dll

Get message saying dll regestered.
SSIS works fine

Automate Windows Server defragmentation

E drive is the drive you wish to defragment

Put this in a bat file and run a scheuled task pointing the bat file.
defrag.exe -v e: >c:\temp\logfile.txt

To check the status you can look how long, etc in the log file under the Advanced /  View Log of the scheduled task  menu window.

Spaced Used by a table in a specific database.

Shows you how to get the SQL Server database table size
EXEC SP_SPACEUSED 'TABLE NAME'

Shows you how to get the size of a database
EXEC SP_SPACEUSED

Shows you how to get the all tables size from database
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

–--create temp table to store the result
CREATE TABLE #temptable
  (
     tablename    VARCHAR(100),
     numberofrows VARCHAR(100),
     reservedsize VARCHAR(50),
     datasize     VARCHAR(50),
     indexsize    VARCHAR(50),
     unusedsize   VARCHAR(50)
  )

---Inserting into temp table
INSERT INTO #temptable
            (tablename,
             numberofrows,
             reservedsize,
             datasize,
             indexsize,
             unusedsize)

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SELECT *
FROM   #temptable
---drop the temporary table
DROP TABLE #temptable

How to get RDL information from Reporting Services Report.

--FROM http://blogs.netconnex.com/2011/05/extracting-ssrs-report-rdl-xml-from.html
--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
   ,CASE Type
      WHEN 2 THEN 'Report'
      WHEN 5 THEN 'Data Source'
      WHEN 7 THEN 'Report Part'
      WHEN 8 THEN 'Shared Dataset'
      ELSE 'Other'
     END AS TypeDescription
   ,CONVERT(varbinary(max),Content) AS Content   
   FROM ReportServer.dbo.Catalog
   WHERE Type IN (2,5,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
      WHEN LEFT(Content,3) = 0xEFBBBF
        THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
      ELSE
        Content
    END AS Content
  FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
  SELECT
    ItemID,Name,[Type],TypeDescription
   ,CONVERT(xml,Content) AS ContentXML
  FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
   ItemID,Name,[Type],TypeDescription,ContentXML
  ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
  ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML 
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query

Disk System

What kind of database?
------------------------
OLAP - Sequential I/O,  Read intensive
OLTP - Random I/O, Read /Write intensive

Calculating the number of disks required
----------------------------------------
1. the required disk I/O per second
2. I/O per second capacity (IOPS) of the individual disks included

Required # Disks = (Reads/sec + (Writes/sec * RAID adjuster)) / Disk IOPS

Remember to separate the transaction log and data files.

RAID = Redundant Array of Independent Disks

RAID0 - zero redundancy RAID (not recommended)
RAID1 -  disk mirroring,  50% disk utilization level, write medium, read high
RAID5 - disk utilization  equals 1 - (1/N), write low, read high, minimum three drives

SAN - Storage Area Network
------------------------------------
*Multiple servers can connect to a SAN via special host bus adapter (HBA) cards
installed in each connecter server.
*Disks within the SAN are grouped together into logical unit numbers (LUNs)
and presented as required to connected servers. The server sees the LUN as a locally attached disk.
*Major benefits of a SAN is sharing disks among many servers to maximize usage.
*To get best performance dedicate the entire SAN to a single, mission critical database.

If your organization decides on a SAN storage system have a good working relationship with your SAN administrator.

Each physical disk in a SAN can be carved up into parts and used in the creation of separate LUNs.
As a result, LUNs from many servers can all be using different parts of the same physical disks.