Monday, August 23, 2010

Working with named calculations

http://searchsqlserver.techtarget.com/feature/Working-with-named-calculations

Named calculations are additional virtual columns on the tables in your DSV. This allows you to mine derived information in your data without having to change your source data. Anamed calculation consists of name, a SQL expression containing the calculation, and an optional description. The calculation can be any valid SQL expression. If you are not a SQL expert, here are some types of expressions that are useful in many data mining projects.

Figure 3.5 Completed MovieClick data source view
Arithmetic Operations
Standard SQL supports +, -, * , /, and % (modulo). For example you could create a Profit named calculation with the following formula:
[Sales Price] – [Item Cost]
Mathematical Functions
Mathematical functions are useful, especially when data in a column does not follow a uniform distribution. The SQL functions ABS, LOG, SIGN, and SQRT are particularly useful. Here are two examples:

  • To flatten out an exponentially increasing variable:
    LOG([Sales Quantity])

  • To create a flag for over/under budget quarters:
    SIGN([Actual Expenses] – [Budgeted Expenses]) Compositing Expressions
    Often, the hypothesis you want to test depends on a variable that is a combination of two of the variables you already have. For example, it may not be interesting that a person is married or has children, but the combination of the two may provide valuable information. A composite expression for this situation could look like this:
    [Marital Status] + ' ' + [Has Children]
    CASE Expressions
    CASE expressions are an extremely flexible way to create meaningful variables for data mining. The CASE expression allows you to assign results based on the evaluation of one or more conditions. Useful applications of CASE are to change value labels, manually discretize columns, reduce the number of valid states, and convert an attribute from a nested table to the case level.

  • To change value labels:

  • CASE [Category]
                   WHEN 1 THEN 'Food'
                   WHEN 2 THEN 'Beverage'
                   WHEN 3 THEN 'Goods'
         END CASE
  • Manually discretize a column:

  • CASE
                 WHEN [Age] < 20 THEN 'Under 20'
                 WHEN [Age] <= 30 THEN 'Between 20 and 30'
                 WHEN [Age] <= 40 THEN 'Between 30 and 40'
                 ELSE 'Over 40'
         END
  • To reduce the number of valid states:
    CASE [Marital Status]
                 WHEN 'Married' THEN [Marital Status]
                 WHEN 'Never Married' THEN [Marital Status]
                 ELSE 'Other'
         END

  • To convert an attribute from a nested table to a case table:

  • CASE
               WHEN EXISTS
                      (SELECT [Movie] FROM [Movies]
                             WHERE [Movie]='Star Wars' AND
                                                [Movies].[CustomerID]=[Customers].[CustomerID])
                             THEN 'True'
                 ELSE 'False'
         END
    This would be done, for instance, when you wanted to convert a nested attribute to a case-level attribute. Note that if you still want to use the nested table in the model, you will have to use a named query to filter the attribute from the nested table, as described in the next section.
    Creating a Named Calculation on the Customers Table
    To create a named calculation to discretize and reduce the number of states in the Num Bedrooms column:
    1. Right-click the Customers table and select Create a named calculation.
    2. Enter the calculation name Bedrooms and optionally enter a description.
    3. Enter the following expression:
    CASE
                  WHEN [Num Bedrooms] = 1 THEN 'One'
                  WHEN [Num Bedrooms] <= 3 THEN 'Two or Three'
                  WHEN [Num Bedrooms] >= 4 THEN 'Four or more'
                 ELSE 'None'
          END
    Upon closing the dialog box, the DSV Designer will validate your expression and return any applicable errors. Once you have successfully created your calculation you can see the results by right-clicking the table and selecting Explore Data.

    Extract RDL's from SSRS

    http://www.sqldbatips.com/showarticle.asp?ID=62


    Overview
    Reporting Services Scripter is a .NET Windows Forms application that enables scripting and transfer of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules), Subscriptions (normal and data driven) and server side report parameters.
    Download Reporting Services Scripter (2.0.0.17)
    View the Readme file


    Key Features

    • Quickly and easily extract all RDL from a Report Server
    • Automatically generates scripts to load reports, datasource's, resources, linked reports and folders with all their associated properties to enable report migration between servers with no manual intervention e.g. avoids having to try and copy execution options between servers manually using Report Manager which is prone to mistakes and not as repeatable or efficient as scripting
    • Automatically generates command files to load scripted items onto a new server
    • Automatically transfer items from one server to another (including from SQL2000 to SQL2005 to SQL2008)
    • Generate scripts for Shared Schedules
    • Generate Scripts for System and Item Level Roles
    • Generate Scripts for Normal and Data Driven Report Subscriptions
    • Easily generate loader scripts to load existing RDL files e.g. if RDL is kept under source control then it can just generate the scripts to load the reports rather than extract the report definition as well
    • Automatically backs up reports before overwriting them
    • Easily generate scripts from the command line
    • Reverse engineer Visual Studio Report Projects from a Report Server

    Tested Environments

    • Windows 2000, XP, Vista
    • Windows Server 2003, 2008
    • SQL ServerReporting Services 2000, 2005, 2008


    Requirements
    In order to run Reporting Services Scripter you need to have the .NET Framework 1.1 installed. In order to be able to run the generated command files or use the Transfer mode of deployment you will need the Reporting Services Management Tools installed, specifically the command line utility RS.EXE (this is version specific and the correct versions for your environment are required for full functionality)

    Deserialization failed: The 'DataType' attribute is not declared. Line 3020, position 20

    http://connect.microsoft.com/VisualStudio/feedback/details/405739/vs-2008-rs-2008-error-deserialization-failed-the-datatype-attribute-is-not-declared

    The workaround
    the bug:
    <-ParameterValues>
             <-ParameterValue>
                <-Value DataType="Integer">1
                <-Label>All
             <-/ParameterValue>
             <-ParameterValue>
                <-Value DataType="Integer">0
                <-Label>T/R
             <-/ParameterValue>
             <-ParameterValue>
                <-Value DataType="Integer">-1
                <-Label>SCD
             <-/ParameterValue>
            <-/ParameterValues>

    solution (remome all : DataType="Integer"):

    <-ParameterValues>
             <-ParameterValue>
                <-Value>1
                <-Label>All
             <-/ParameterValue>
             <-ParameterValue>
                <-Value>0
                <-Label>T/R
             <-/ParameterValue>
             <-ParameterValue>
                <-Value>-1
                <-Label>SCD
             <-/ParameterValue>
            <-/ParameterValues>

    Paypal: How to Delete Administrative eMail address

    you need to go to: https://www.paypal.com/us/cgi-bin/webscr?cmd=_profile-logins-change-admin

    (You may need to log into your PP account). Click on the drop down list of email address (or add a new one) and then click onto continue.
    Note: if you are trying to change your administrative address to an address that is currently your primary address, you will have to change your primary email address first.

    爱,婚姻,性及其他

    1。 创世纪说:女人是男人身上取下的肋骨 夫妻本为一体。 牙齿和舌头还有打架的时候呢 更何况是两个各有思想的人呢?所以当夫妻之间吵架的时候 心中默念他/她是我的骨中骨,肉中肉。。。
    2。 彼此接纳,帮助和相爱的关系。传道书说:两个人总比一个人好,因为二人忙碌同得美好的果效。若是跌倒,这人可以扶起。他的同伴若是孤身跌倒,没有别人扶起他来,这人就有祸了。再者,二人同睡,就都暖和。一人独睡,怎能暖和呢?(早在几千年的圣经就道出了两人在一起的真 谛)
    3。 关于性关系: 从我们左手戴上婚戒的那天起就不要用带着婚戒的手再去碰其他异性,因为性关系是上帝所赐给夫妻二人的生育,享乐和连结的恩典。这种恩典只属于夫妻二人。
    4。 爱是什么?对爱的诠释:  歌林多前书说:爱是恒久忍耐,又有恩慈。爱是不嫉妒。爱是不自夸,不张狂。不作害 羞的事。不求自己的益处。不轻易发怒。不计算人的恶。不喜欢不义。只喜欢真理。凡事包容,凡是相信。凡事盼望。凡事忍耐。爱是永不止息。
    5。 关于离婚: 丈夫对妻子的背叛。他说一个女人给他打电话说,她老公背着她在外面有别的女人不要她了 之后女人痛哭。他说丈夫对妻子的背叛是对妻子最大最大的伤害。从结婚那天起 丈夫对妻子就有了责任 一定要爱妻子。牧师说还没听说哪个男人在外面拼搏是为了街角的妓女,都是为了家庭 为了给妻子 孩子提供更好的生活。 箴言说:要喜悦你幼年所娶的妻

    Temporary Solution to Solve iphone 3G with iOS 4.0x lag issue

    Little trick I have been doing for all my iPhones, and have noticed a small speed increase (more so on 3G) is disabling spot light search. Everyone I have done it for has noticed a difference.

    iOS 3.X: settings > general > home > search results
    iOS 4.X: settings > general > spotlight search

    Uncheck all options, restart your phone. You should notice a bit of a difference.