Monday, November 28, 2011

How to find date difference in SharePoint 2010 Infopath.


Expression to Find the Age based on D.O.B:



(substring(today(), 1, 4) - substring(DOB, 1, 4)) - 1 * (translate(substring(today(), 6, 5), "-", "") < translate(substring(DOB, 6, 5), "-", ""))

Tuesday, November 15, 2011

Basic MDX Queries

------------Restricting Cube Space/Slicing Cube Data---------

//The subcube name should be same as that of the cube

Create subcube [Adventure Works] AS select [Date].[Calendar].[Calendar Year].&[2004] on 0 from [Adventure Works]
select ([Measures].[Internet Sales Amount]) on 0,
{[Date].[Calendar].[Month].Members} on 1
from [Adventure Works]

drop subcube [Adventure Works]


------------------------------Member function-----------------------------------------------

//Ancestor(Member,Level)
select Ancestor([Product].[Product Categories].[Subcategory].[Locks],[Product].[Product Categories].[Category]) on 0 from [adventure works]

select Ancestor([Product].[Product Categories].[Product].[Cable Lock],2) on 0 from [adventure works]

//Descendants
SELECT [Measures].[Internet Sales Amount] ON COLUMNS, {DESCENDANTS([Geography].[Geography].[Country].[Germany], [Geography].[Geography].[City])} ON ROWS
FROM [Adventure Works]


//Cousin( Member_Expression , Ancestor_Member_Expression )
select COUSIN([date].[calendar].[july 2003], [cy 2004]) on 0 from [adventure works]
select cousin([Date].[Calendar].[Calendar Quarter].&[2004]&[3],[Date].[Calendar].[Calendar Year].&[2002]) on 0 from [adventure works]
select cousin([Product].[Product Categories].[Product].[AWC Logo Cap],[Product].[Product Categories].[Category].[Bikes]) on 0 from [adventure
works]


//LastChild
select ([product].[product model lines].LastChild) on 0 from [adventure works]


//LastSibling
select ([product].[product model lines].[model].[cycling cap].LastSibling) on 0 from [adventure works]


//FirstChild
select {[Product].[Product Categories].FirstChild} on 0 from [adventure works]
select {[Product].[Product Categories].[Category].[Bikes].FirstChild} on 0 from [adventure works]


//FirstSibling
select {[Date].[Calendar].[Month].[May 2000].FirstSibling} on 0 from [adventure works]
select {[Date].[Calendar].[Calendar Quarter].&[2004]&[2].FirstSibling} on 0 from [adventure works]
select {[Product].[Product Categories].[Category].[Bikes].FirstSibling} on 0 from [adventure works]


//Parent
select ([Measures].[Internet Sales Amount]) on 0, [product].[product model lines].[model].[taillight].parent on 1  from [adventure works]

//PrevMember

select ([Measures].[Internet Sales Amount]) on 0, [customer].[customer geography].[country].[Germany].PrevMember on 1  from [adventure works]

//Set.Item(Ordinal position)
select {[Date].[Calendar].[Calendar Quarter].&[2001]&[4].Children.Item(0)} on 0 from [adventure works]
select CROSSJOIN([product].[product model lines].[product line].members,  [measures].[sales amount]).Item(2) on 0 from [adventure works]


//Member.Lag(No of positions)
select {[Product].[Product Categories].[Category].[Bikes].Lag(1)} on 0 from [adventure works]


//Member.Lead(No of positions)
select ([product].[product model lines].[product line].[mountain].Lead(1)) on 0 from [adventure works]


//Members
select ([Measures].[Internet Sales Amount]) on 0, {[customer].[customer geography].[country].members} on 1  from [adventure works]


//NextMember
select ([Measures].[Internet Sales Amount]) on 0, {[customer].[customer geography].[country].[Australia].NextMember} on 1  from [adventure works]



----------------------------------Time Functions----------------------------------------------

//YTD
select {YTD([Date].[Calendar].[Month].&[2002]&[8])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]


//QTD
select {QTD([Date].[Calendar].[Month].&[2002]&[8])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]


//MTD
select {MTD([Date].[Calendar].[Date].&[20020310])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]


//ParallelPeriod
select {YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],-1,[Date].[Calendar].[Month].&[2003]&[5]))} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]


//PeriodsToDate
select {PeriodsToDate([Date].[Calendar].[Calendar Year],[Date].[Calendar].[Month].&[2003]&[5])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]

select {PeriodsToDate([Date].[Calendar].[Calendar Quarter],[Date].[Calendar].[Month].&[2003]&[5])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]


//OpeningPeriod
select {OpeningPeriod ([Date].[Calendar].[Month],[Date].[Calendar].[Calendar Quarter].&[2002]&[2])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]


//ClosingPeriod
select {ClosingPeriod ([Date].[Calendar].[Month],[Date].[Calendar].[Calendar Quarter].&[2002]&[2])} on 0,
{[Product].[Product Categories].[Category].Members} on 1
from [Adventure Works]
where [Measures].[Reseller Sales Amount]

Thursday, November 10, 2011

SQL Server 2012: New Certifications

  • Querying Microsoft SQL Server (70-461) [BI and DBA]
  • Administering a Microsoft SQL Server 2012 Database (70-462) [DBA]
  • Building Data Warehouses with Microsoft SQL Server 2012 (70-463) [BI]
  • Developing a Microsoft SQL Server 2012 Database (70-464) [DBA]
  • Designing Database Solutions for SQL Server 2012 (70-465) [DBA]
  • Implementing Data Models and Reports with Microsoft SQL Server 2012 (70-466) [BI]
  • Designing Business Intelligence Solutions with Microsoft SQL Server 2012 Platform (70-467) [BI]
  • Tuesday, November 1, 2011

    Setting the Protection Level of SSIS Packages

    Setting the Protection Level of Packages To protect the data in an Integration Services package, you can set a protection level that helps protect just sensitive data or all the data in the package. Furthermore, you can encrypt this data with a password or a user key, or rely on the database to encrypt the data. Also, the protection level that you use for a package is not necessarily static, but changes throughout the life cycle of the package. You often set one protection level during development and another as soon as you deploy the package.

    Do not save sensitive (DontSaveSensitive) - Suppresses the values of sensitive properties in the package when the package is saved. This protection level does not encrypt, but instead it prevents properties that are marked sensitive from being saved with the package and therefore makes the sensitive data unavailable to other users. If a different user opens the package, the sensitive information is replaced with blanks and the user must provide the sensitive information.

    Encrypt all with password (EncryptAllWithPassword) - Uses a password to encrypt the whole package. The package is encrypted by using a password that the user supplies when the package is created or exported. To open the package in SSIS Designer or run the package by using the dtexec command prompt utility, the user must provide the package password. Without the password the user cannot access or run the package.

    Encrypt all with user key (EncryptAllWithUserKey) - Uses a key that is based on the current user profile to encrypt the whole package. Only the user who created or exported the package can open the package in SSIS Designer or run the package by using the dtexec command prompt utility.

    Encrypt sensitive with password (EncryptSensitiveWithPassword) - Uses a password to encrypt only the values of sensitive properties in the package. DPAPI is used for this encryption. Sensitive data is saved as a part of the package, but that data is encrypted by using a password that the current user supplies when the package is created or exported. To open the package in SSIS Designer, the user must provide the package password. If the password is not provided, the package opens without the sensitive data and the current user must provide new values for sensitive data. If the user tries to execute the package without providing the password, package execution fails.
    Encrypt sensitive with user key (EncryptSensitiveWithUserKey) - Uses a key that is based on the current user profile to encrypt only the values of sensitive properties in the package. Only the same user who uses the same profile can load the package. If a different user opens the package, the sensitive information is replaced with blanks and the current user must provide new values for the sensitive data. If the user attempts to execute the package, package execution fails. DPAPI is used for this encryption.

    Rely on server storage for encryption (ServerStorage) - Protects the whole package using SQL Server database roles. This option is supported only when a package is saved to the SQL Server msdb database. It is not supported when a package is saved to the file system from Business Intelligence Development Studio.