SSAS How-To: Create Dynamic Time Calculations To Reuse Over Any Measure

Rather than making individual time calculations for each measure in an SSAS cube (i.e. YTD, Prior YTD, YTD Variances, etc), one can use a more dynamic approach to time calcs applicable to all measures and save a great amount of dev time and overhead in the process.

1. Create Named Query in DSV:

SELECT      1 AS DateCalculationKey, ‘Selected Date’ AS DateCalculation
UNION
SELECT      2, ‘YTD’
UNION
SELECT      3, ‘PY YTD’
UNION
SELECT      4, ‘YTD Chg’
UNION
SELECT      5, ‘YTD Chg %’

2. Create the dimension and add it to the Dimension Usage tab of the cube (no relations to the fact will exist)

3. Add the MDX script calculations:

SCOPE (

[Date].[Calendar Hierarchy].MEMBERS);

[Date Calculations].[Date Calculations].[YTD] =
AGGREGATE
(

    {[Date Calculations].[Date Calculations].[Selected Date]} *
     PERIODSTODATE
     (
       [Date].[Calendar Hierarchy].[Calendar Year],
       [Date].[Calendar Hierarchy].CURRENTMEMBER
     )
);

[Date Calculations].[Date Calculations].[PY YTD] =
AGGREGATE
(
   {[Date Calculations].[Date Calculations].[Selected Date]} *
   PERIODSTODATE
   (
      [Date].[Calendar Hierarchy].[Calendar Year],
      PARALLELPERIOD
      (
         [Date].[Calendar Hierarchy].[Calendar Year],
         1,
         [Date].[Calendar Hierarchy].CURRENTMEMBER
   )
)
);

END SCOPE;

SCOPE ([Date].[Calendar Hierarchy].[All]);

[Date Calculations].[Date Calculations].[YTD] = NULL;

[Date Calculations].[Date Calculations].[PY YTD]=NULL;

END SCOPE;

[Date Calculations].[Date Calculations].[YTD Chg]=[Date Calculations].[Date Calculations].[YTD]-

[Date Calculations].[Date Calculations].[PY YTD];

[Date Calculations].[Date Calculations].[YTD Chg %] =
IIF
(
[Date Calculations].[Date Calculations].[PY YTD] = 0,
NULL,
([Date Calculations].[Date Calculations].[YTD] - [Date Calculations].[Date Calculations].[PY YTD]) /
[Date Calculations].[Date Calculations].[PY YTD]
);

FORMAT_STRING([Date Calculations].[Date Calculations].[YTD Chg %]) = “#,0.0%”;

//Create current year and month in scripts:
CREATESET CURRENTCUBE.[Current Year]
AS {(strtomember(“[Date].[Calendar Hierarchy].[Calendar Year].["+ cstr(year(now()))+"]“))}, DISPLAY_FOLDER = ‘Named Sets’ ;

CREATESET CURRENTCUBE.[Current Month] AS
TAIL
(
NONEMPTY
(
[Date].[Calendar Hierarchy].[Month Name Year],
[Measures].[Fact Sales Count]
)
);

The result!

SSRS How-To: Simple Method to Pass MDX Multi-Select Parameters to MDX Datasets

SSRS Tip: Simplest Method to Pass MDX Multi-Select Parameters to MDX Datasets

I recently ran across a poorly covered topic in regards SSRS development using MDX parameters and datasets.  After some trail and error and visiting a number of online resources, I found the approach below to be the most straight forward.

To pass a multivalued MDX parameter to an MDX based dataset, perform the following routine:

1. Create the parameter dataset (Right click DataSet >> Add Dataset…) - Note: the MEMBER_CAPTION and UNIQUENAME enables the cleanest approach to assigning values to the parameter (Step 2). Also, we will be filtering out unwanted parameter values.

Step 1.1 – click ‘use dataset embedded in report’ 
Step 1.2 – select datasource 
Step 1.3 – click ‘Query Designer’ 
Step 1.4 – Click ‘Design Mode’ icon 
Step 1.5 – Enter query

WITH MEMBER [Measures].[ParameterCaption] AS [Fixed Asset Book].[Book ID].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue] AS [Fixed Asset Book].[Book ID].CURRENTMEMBER.UNIQUENAME

SET [Book ID] AS [Fixed Asset Book].[Book Id].ALLMEMBERS

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue]} ON COLUMNS , FILTER([Book ID], [ParameterCaption] <> “All” AND [ParameterCaption] <> “” AND [ParameterCaption] <> “N/A” AND [ParameterCaption] <> “Unknown”) ON ROWS FROM [FixedAssets]

Notice the full unique names in ‘ParameterValue’ Column…the use of these values will enable a StrToSet function later in our primary MDX dataset.

2. Create the parameter (on the left pane, right click ‘Parameters’, ‘Add Parameter…’)

 2.1 – From the General tab, add ’allow multiple selection’ if needed

 2.2 – Assign ParameterCaption as the label (will be displayed to the user on the report)and ParameterValue as the value (will be interpreted by SSRS when filtering report data)

3. Create the dataset (Right click DataSet >> Add Dataset…)

Step 3.1 – click ‘use dataset embedded in report’  
Step 3.2 – select datasource  
Step 3.3 – click ‘Query Designer’  
Step 3.4 – Click ‘Design Mode’ icon  
Step 3.5 – Enter query to include the WHERE (StrToSet(@BookID, CONSTRAINED)) clause – this will enable the MDX to interpret the @BookID multiselect object (from step 1) as dynamic memberset.  
Step 3.6 – Click the ‘Query Parameters’ icon  
Step 3.7 – Create the BookID parameter assignment

 

4. You’re all set!

 

 

HP Announcement: PDW V2 Appliance Features

HP Announcement: PDW V2 Appliance Features

New features for the HP’s Enterprise Data Warehouse (EDW) V2 appliance for SQL Server PDW have been announced!

Highlights:

• Lower price point (backup and landing zone servers not longer required)
• Increased CPU power (16 vs V1′s 12 cores per compute node)
• Increased Memory (256 mb vs V1′s 96 mb per server)
• Increased storage/scalability (support for up to 5 Petabytes of disk space vs V1′s 610Tb)
• Infiniband Data Movement Speeds: FDR (14x bandwidth) vs V1′s QDR (4x bandwidth)
• Microsoft V2 enhancements include “direct attached” SAS JBOD storage, Hyper V, Mirroring, and Server 2012 Storage Spaces

Parallel Data Warehouse (PDW) Tip: Proof of Concept (POC) Guidelines

As with any other project our objective is to drive PDW POC success!  To clear and cultivate the playing field for successful POC engagements, we must, at a minimum, consider the following guidelines.

Guideline 1: Identify Existing Roadblocks and De-scope
Major POC challenges can manifest themselves in many different forms i.e. inability to seed data, dealing with sub-par data warehouse architectures, wrangling poorly concieved T-SQL/ETL architectures, etc.  If these challenges exist in the current production environment and are the primary driver behind the PDW POC initiative, approach with caution as these core issues can take more time to address when compared to minor issues such as lack of an SSIS framework, data delivery issues, or inability to generate very large data volumes.  If show-stoppers exist, try either de-scoping the issues/components from the PDW altogether or try to find a sustainable work-around.

Guideline 2: Gather Accurate Business and Technical Requirements
This should be fairly standard as the client will have a core dataset and/or processes that they would like see run on the PDW appliance coupled with an ‘ideal’ runtime. Keep the deliverables manageable and implement the SMART methodology (Specific, Measureable, Achievable, Realistic, Timescale). Be sure to gather base requirements for documentation, data volumes, environmental requirements, etc.   For example, the client may want to stage both a dev and test environment, they may only want a schemas worth of tables/data brought over from an existing  SMP database (Note: PDW does not support schemas), or the client may want to bring over 2 years of history rather than 10 years.  Keep historical data requirements to a minimum, as it may be difficult to find extraction windows from existing source systems. Similarly, data loads into the PDW via dwloader command or SSIS destination (slower than dwloader) can take a significant amount of time and may run over the course of X number of days depending on source system availability and data volumes.  Maybe most importantly, assess the current environment, data volumes, and processing windows to manage realistic expectations! Although there have been instances where specific loading routines can result in 100X improvement in load times, this is not always the case and this factor should not be used as a baseline.

Guideline 3: Streamline Testing Requirements
When addressing the testing phase, do not overload the POC activities with extraneous testing/recon requirements and keep the scope of testing and data validation to a minimum. Simplify the testing criteria to critical dataset load times vs. data volumes.  The success criteria should be clear i.e. Company GECKO has defined success as loading 2Tb of prior day policy activity data for 7 consecutive days in under 30 minutes each day, or Company STERNXM defines success as loading 20 regional subscriber datasets via flatfile concurrently in under 2 hours once a week.  Also, the client may be interested in testing ad-hoc query and data loads while the appliance is actively processing large data load requests. At a minimum, the testing process will address the following :

• Data Load
• Data Expansion
• Query Performance
• Query Concurrency
• Mixed Workload Concurrency

Guideline 4:  Resource Planning and Communication
While keeping project activities within scope, ensure that the team has at least the minimum number of dedicated resources to the project.  In-house members of a POC delivery team usually include at minimum: DBA, Business Analyst, and a Data Developer engineer. Keep in mind, most streamlined POC engagements wrap up within a 2-3 week timeframe and if the delivery requirements are modified (heaven forbid) , to say,  include more complex PDW processing routines or include additional table loads, always ensure that you remain communicative and provide updates/recommendations to resource budgets and overall POC timelines.  Additionally, provide daily email updates and try to incorporate simple graphical representations of project progress (i.e. RAG Traffic lights). While never under-estimating, pad for the unexpected and ensure that time is added to the plan to account for contingencies.

Guideline 5: Partnership with Microsoft PDW COE
In a recent PDW implementation, I was involved with a few PDW-specific challenges applicable to query optimization, data loading, and appliance software upgrades.  As a result of both the clients and the consulting company’s partnership with the Microsoft COE, the downtime associated with these  challenges were minimized. Partnership led to smoother and more efficient PDW POC delivery as the COE was ready and willing to recommend and demonstrate solutions that aligned to MS COE best practices . Ultimately, the COE recommendations and actions directly benefited system usability and sustainability and ensured a successful POC delivery.

I would like to build a collection of guidelines within this post to benefit future PDW POC implementations!  If you have an idea or want to expand on one of these guidelines, feel free to share your personal experiences and/or workarounds to common POC challenges.

I want to thank the following contributors of the PDW community for providing insight that led to the formulation of the POC guidelines above:

Matt Goswell
James Herring
Ralph Kemperdick
Derek Comingore
Matthew Winter

Visio 2010 Workaround: Application Hangs when opening ‘Database Model Diagram’ template

I received the following error today when attempting to use Visio’s ‘Database Model Diagram’ template under category ‘Software and Database’.

The program VISIO.EXE version 14.0.6122.5000 stopped interacting with Windows and was closed. To see if more information about the problem is available, check the problem history in the Action Center control panel.  Process ID: 3fa8  Start Time: 01cdad7f36f7bba9  Termination Time: 0  Application Path: D:\Program Files\Microsoft Office\Office14\VISIO.EXE  Report Id: 9c9eef0a-1972-11e2-9a12-005056995c65

Sure enough, per the seemingly improbable suggestion made in the post below, Visio did not hang after changing the default printer to ‘Microsoft XPS Document Writer’. Don’t make the i mistake I did and change the default printer to ‘Microsoft XPS Document Writer (redirected 7)’.

http://answers.microsoft.com/en-us/office/forum/office_2010-visio/visio-2010-not-responding-when-opening-database/64b2b582-53f0-41f2-bb47-d3a3f36aeb82

As it turns out,  Remote Desktop (RDP) assigns remotely-mounted printers which can cause issues with Visio’s templates.

Below is similar to what you should see once you’ve assigned the correct default printer.

SQL SERVER - CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis

Reblogged from SQL Server Journey with SQL Authority:

• 
• 

Every weekend brings creative ideas and accidents brings best unknown secrets in front of us. Just a day while working with complex SQL Server code in SSMS I came across very interesting shortcut which I have never used before and instantly fell in love with it. It is totally possible that you are familiar with this but for me it was the first time and I was surprised that I did know know this short cut so far.

Read more… 184 more words

Where has this been all my working life? If you frequently find your self scouring complex (and sometimes needlessly complex) T-SQL code, simply place the cursor on the inside or outside of a parenthesis, and perform a CTRL+SHIFT+]. Sql Server Management Studio will then highlight the code for that particular SELECT block. There is some complex T-SQL out there in the wild (future consultants beware!), this will save you loads of scrolling and searching time and help you wrangle in pesky blocks of extended code. Crunch that code! - Sal

Parallel Data Warehouse (PDW) Basics: CREATE TABLE and CTAS Syntax

After forgetting the syntax for temp tables a couple of times, I decided to write a brief overview of the table creation syntax for Microsoft’s Parallel Data Warehouse (PDW) architecture.

One thing worth noting up-front, this post does not include considerations that should be made in determinining whether a table should be distributed vs replicated.

TEMPORARY TABLE BASICS

-Temp tables will reside in the tempdb (no surprise there), however, the ## global prefix as we see it on SQL Server SMP is not available.
-Temp tables can be either distributed or replicated
-Temp tables cannot have partitions, views, or indexes
-Table permissions cannot be changed
-Visible in only the current session
- CONTROL, INSERT, SELECT and UPDATE permissions are granted to the temp table creator.

Example: Distributed temporary table

CREATE TABLE testDB.dbo.#TableA (
   here int NOT NULL,
   we varchar(50),
   go varchar(50))
WITH
   ( LOCATION = USER_DB,
    DISTRIBUTION = HASH (here) );

NON-TEMP TABLE BASICS
-Indexes cannot be added (or dropped) after a table has been created (must use ‘CREATE TABLE AS SELECT’ aka ‘CTAS’ syntax if the developer wants to preserve the underlying data).
-All tables are created with page compression, there is no configurable option for this.
-When creating a distributed table, keep in mind that partitions will exist in the 8 distributions per compute node. So if you want the math on that, an db may have 10 year partitions on a server - in PDW, this translates to 10(partitions) * 8 (distributions) = 80 partitions on each compute node.  If you have a Parallel Data Warehouse (PDW) ‘Full-Rack’, there will be a total of 800 partitions (80 partitions per node * 10 nodes) …  interesting nonetheless!
-The default collation for the PDW appliance is Latin1_General_100_CI_AS_KS_WS – this is configurable, however, use caution if you plan on modifying a column collation as it can lead to collation errors when comparing strings.

Example: Replicated Table with Index

CREATE TABLE myTable (
   yearId int NOT NULL,
   some varchar(50),
   stuff varchar(50))
WITH 
    ( CLUSTERED INDEX (yearId) );

Example: Distributed Table with Multiple Partitions

CREATE TABLE TableA(
   yearId int NOT NULL,
   some varchar(50),
   stuff varchar(50)
WITH     ( DISTRIBUTION = HASH (yearId),     
PARTITION ( YearPurchased     
RANGE RIGHT FOR VALUES       
( 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012 )));

Example:  Adding a new distribution column and clustered index to an existing table using ‘Create Table as Select’ aka ‘CTAS’

CREATE TABLE TableTemp WITH ( 
    DISTRIBUTION = HASH (newDistributionColumn),
    CLUSTERED INDEX (id ASC) )
AS SELECT * FROM TableOriginal;
DROP TABLE TableOriginal
RENAME OBJECT [TableTemp] TO [TableOriginal]

Below is the base syntax for creating tables as provided by Microsoft:

  CREATE TABLE [ database_name . [ dbo ] . | dbo. ] table_name
(
{ column_name
[ COLLATE Windows_collation_name ]
[ NULL | NOT NULL ] }
[ ,...n ]
)[ WITH ( <table_option> [ ,...n ] ) ]

Create a new temporary table.

CREATE TABLE [ database_name . [ dbo ] . | dbo. ] #table_name
(
{ column_name
[ COLLATE Windows_collation_name ]
[ NULL | NOT NULL ] }
[ ,...n ]
)
WITH ( LOCATION = USER_DB [, <table_option>
[ ,...n ] ] ) [;]
::=
datetimeoffset [ ( n ) ]
| datetime2 [ ( n ) ]
| datetime
| smalldatetime
| date
| time [ ( n ) ]
| float [ ( n ) ]
| real [ ( n ) ]
| decimal [ ( precision [ , scale ] ) ]
| money
| smallmoney
| bigint
| int
| smallint
| tinyint
| bit
| nvarchar [ ( n ) ]
| nchar [ ( n ) ]
| varchar [ ( n ) ]
| char [ ( n ) ]
| varbinary [ ( n ) ]
| binary [ ( n ) ]
::={[ CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) ] | [ DISTRIBUTION = { HASH( distribution_column_name ) | REPLICATE } ] | [ PARTITION( partition_column_name RANGE [ LEFT|RIGHT ] FOR VALUES ( [ boundary_value [,...n] ] ) ) ] }

NOTE: When performing CTAS or re-creating tables in any way, you will need to create statistics on those tables upon loading.

Ideally, you should create stats on all the join columns, group by, order by and restriction. SQL Server PDW does not automatically create and update statistics on the Control node for every occasion when SQL Server creates or updates statistics on the Compute nodes:

– This will create stat for all columns on all objects

select ‘create statistics ‘ + b.name + ‘ on dbo.’ + a.name + ‘ (‘ + b.name + ‘)’ from sys.tables a, sys.columns b where a.object_id = b.object_id and not exists ( select null from sys.stats_columns where object_id in (select object_id from sys.stats_columns group by object_id having count(*)>=1) and object_id = b.object_id and column_id = b.column_id) order by a.name, b.column_id;