tag:blogger.com,1999:blog-85850493881479738852024-03-19T04:48:35.538-04:00The Art of Business IntelligenceFendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.comBlogger49125tag:blogger.com,1999:blog-8585049388147973885.post-13634451281251629232013-09-26T20:38:00.001-04:002013-09-26T20:38:53.690-04:00Load Data from Stored Procedure in TimeXtender 2012<p><a href="http://www.timextender.com/">TimeXtender 2012</a> is a new data warehouse development tool that I’ve been using to rapidly increase the ETL Development. </p> <p>There are lots of different way to load data into stage area using TimeXtender 2012. If you are used to use store procedure to gather the related source data, you could still use it in TimeXtender with minimum modification.</p> <p>Here is an example:</p> <ul> <li><strong>Create</strong> a store procedure in the Staging area and <strong>deploy</strong> it.</li></ul> <p><a href="http://lh6.ggpht.com/-z3lewbkInvI/UkTTeK7FZfI/AAAAAAAAA94/0jJ6O-uCfPQ/s1600-h/image%25255B14%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-UUANj0FopF4/UkTTfJT9ZTI/AAAAAAAAA98/saI6Jve7LXo/image_thumb%25255B4%25255D.png?imgmax=800" width="244" height="197"></a></p> <p><a href="http://lh3.ggpht.com/-38b8vuG4sd4/UkTTfnNcrrI/AAAAAAAAA-I/cxm9Y6J6mg8/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-5KGc-QZBWbo/UkTTfzj9tkI/AAAAAAAAA-Q/sWbqADkeKBc/image_thumb%25255B3%25255D.png?imgmax=800" width="244" height="123"></a></p> <ul> <li>In the store procedure, Make sure you upsert the result dataset into a table . You may use <a title="http://technet.microsoft.com/en-us/library/ms188029.aspx" href="http://technet.microsoft.com/en-us/library/ms188029.aspx">INTO Clause (T-SQL)</a> to store the result.</li> <li>Create a Script Action to execute the stored procedure.</li></ul> <p><a href="http://lh5.ggpht.com/-BB_svrnmA88/UkTTgCiEXWI/AAAAAAAAA-Y/9aYX7n0WFl4/s1600-h/image%25255B8%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-r0EgINrdJCs/UkTTggcWxNI/AAAAAAAAA-g/eWsTcZt_524/image_thumb%25255B2%25255D.png?imgmax=800" width="244" height="177"></a></p> <p> <a href="http://lh3.ggpht.com/-VEMKTDB0rh0/UkTThMkiRaI/AAAAAAAAA-o/NxH5aqrFJU8/s1600-h/image%25255B17%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-rOIGLCGhPB8/UkTThTM3tNI/AAAAAAAAA-s/4vZRCKgecxA/image_thumb%25255B5%25255D.png?imgmax=800" width="244" height="133"></a></p> <ul> <li>Set the script action as either the <strong>Pre-Step</strong> or the <strong>Post-Step</strong> for the Business unit using the Business Unit <strong>Advanced</strong> property menu. </li></ul> <p><a href="http://lh5.ggpht.com/-sCEBK76HCMw/UkTTh01RoQI/AAAAAAAAA-0/g5tpylT6c20/s1600-h/image%25255B20%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-VvXEteTo6Ok/UkTTiVleGxI/AAAAAAAAA-8/adEYZD1tauM/image_thumb%25255B6%25255D.png?imgmax=800" width="244" height="97"></a></p> <p><a href="http://lh4.ggpht.com/-x4WvODLiNaw/UkTTjA47U6I/AAAAAAAAA_I/wydPuwQ58A0/s1600-h/image%25255B23%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-4Zgh5sb3qek/UkTTjg4xgWI/AAAAAAAAA_M/qTG_6Yxpm3Y/image_thumb%25255B7%25255D.png?imgmax=800" width="244" height="107"></a></p> <ul> <li>Create a <strong>Customer view</strong> in the business unit to read in the data from the result set table. Make sure you <strong>Synchronize the view fields</strong> after deploy the view to see the columns.</li></ul> <p><a href="http://lh4.ggpht.com/-2bdasfaaaKE/UkTTkMjbFpI/AAAAAAAAA_U/fHIjYqxlxjY/s1600-h/image%25255B26%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-aTepnf-lkqI/UkTTkR-CcgI/AAAAAAAAA_c/vVxYfeQ_XiI/image_thumb%25255B8%25255D.png?imgmax=800" width="244" height="132"></a></p> <ul> <li>After <strong>Deploy and Execute</strong> the business unit<strong>,</strong> you could right click the view and select preview to exam the result.</li></ul> <p><a href="http://lh4.ggpht.com/-eTvi6NSLf3I/UkTTk0B0QJI/AAAAAAAAA_o/LV_mEi9pHDU/s1600-h/image%25255B32%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-TIPvNPOzQVY/UkTTlUpNojI/AAAAAAAAA_w/dtQLhdwn8Dc/image_thumb%25255B10%25255D.png?imgmax=800" width="244" height="190"></a></p> <p><a href="http://lh3.ggpht.com/-Pu1aN1vxsnw/UkTTlzjvUkI/AAAAAAAAA_4/JUU9q4tHkkg/s1600-h/image%25255B29%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-tRUzVNMiuV4/UkTTmRqjbgI/AAAAAAAABAA/9ie5ZVxcQLs/image_thumb%25255B9%25255D.png?imgmax=800" width="244" height="149"></a></p> <ul> <li>Now you could use the stage data to create/populate the data warehouse table using<strong> drag and drop</strong> action.</li></ul> <p><a href="http://lh3.ggpht.com/-LgOGrXVaBVs/UkTTmhAQSWI/AAAAAAAABAE/EiqQ_YtNHIQ/s1600-h/image%25255B35%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-F448XDlWTHQ/UkTTnCiyMaI/AAAAAAAABAM/fQlAuw9UNNA/image_thumb%25255B11%25255D.png?imgmax=800" width="101" height="244"></a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com7tag:blogger.com,1999:blog-8585049388147973885.post-22478533269326885172013-09-05T15:30:00.001-04:002013-09-05T15:30:05.841-04:00Use SSRS Data Alert<p> </p> <p>SQL 2012 Reporting Services has data alerts capability. It is a data driven alerting solution. You could use it to inform the users about reporting data that is important to them at a relevant time through email or SMS format. </p> <h3></h3> <p>Here are the steps to create a data alert:</p> <ul> <li>Open the report that you want to create the data alert.</li> <li>Click on the <strong>New Data Alert</strong> on the <strong>Actions</strong> drop down menu</li></ul> <p><a href="http://lh5.ggpht.com/-vuRRnDAe7rI/Uijbcp_-CBI/AAAAAAAAA58/kmj3HjyiZTo/s1600-h/image%25255B5%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-x0xsd3UBQHQ/UijbeafQYYI/AAAAAAAAA6E/Y0y9VKq5cYE/image_thumb%25255B1%25255D.png?imgmax=800" width="218" height="244"></a></p> <ul> <li>The <strong>Data Alert Designer</strong> window will open</li></ul> <p><a href="http://lh3.ggpht.com/-QF_-ea4LHI8/UijbeoiYMAI/AAAAAAAAA6M/JbOSTX1aDnE/s1600-h/image%25255B27%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-eUgwYJyCoGk/Uijbgn9whTI/AAAAAAAAA6U/4GSJfRd6vjU/image_thumb%25255B9%25255D.png?imgmax=800" width="353" height="279"></a></p> <ul> <li>Edit the <strong>Alert Name</strong></li> <li>Create the <strong>Alert Rules</strong> that are applicable/interested by the users by clicking on the <strong>(Add rules…)</strong> on the right side pane. </li> <li>Select and set the data feed columns from the list that you will use to create the rule i.e. Low Water Count.Value </li></ul> <p><a href="http://lh4.ggpht.com/-Iyvx3M0kcW4/Uijbg92Ex1I/AAAAAAAAA6Y/kRkOssFRmJ0/s1600-h/image%25255B11%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-VjV4VhFw0-U/Uijbi1IfPQI/AAAAAAAAA6k/FDbKR0UgYC4/image_thumb%25255B3%25255D.png?imgmax=800" width="244" height="205"></a></p> <ul> <li>Set the threshold i.e. is greater than 30</li></ul> <p><a href="http://lh3.ggpht.com/-S_fDuQCWVU0/UijbjNFKQjI/AAAAAAAAA6s/71Xpi-O0O5Q/s1600-h/image%25255B14%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-MshMOWPanPQ/UijbjpGtJWI/AAAAAAAAA60/ty1lM_piY6Y/image_thumb%25255B4%25255D.png?imgmax=800" width="244" height="222"></a></p> <p><a href="http://lh3.ggpht.com/-djuuPvl5iCE/UijbjwgXM-I/AAAAAAAAA64/iwMk_GXuH-E/s1600-h/image%25255B17%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-1lQ8uwPiwq8/UijbkXOg7iI/AAAAAAAAA7E/G059fxw5FPg/image_thumb%25255B5%25255D.png?imgmax=800" width="244" height="134"></a></p> <ul> <li>You may change the option from <strong>Alert me if any data has:</strong> to <strong>Alter me if no data has:</strong></li></ul> <p><a href="http://lh5.ggpht.com/-ziqccLqsbus/Uijbkxal8BI/AAAAAAAAA7M/MKb8VRIeIPU/s1600-h/image%25255B20%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-9Lszc2BTUJs/UijblOTS7AI/AAAAAAAAA7U/U0y44sqkiUE/image_thumb%25255B6%25255D.png?imgmax=800" width="244" height="147"></a></p> <ul> <li>Set the the time frequency that you want the alert to be sent at <strong>Schedule settings </strong>session. Click on <strong>Advanced</strong> option to set the stop date or option for sending message only the alert result changed.</li></ul> <p><a href="http://lh5.ggpht.com/-K1-uMKcVOVw/UijbloOWmTI/AAAAAAAAA7c/t-yiVK_uivw/s1600-h/image%25255B23%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-wCfNyNMMWVA/Uijbl1RrrHI/AAAAAAAAA7g/_cu2iQlvX8Y/image_thumb%25255B7%25255D.png?imgmax=800" width="244" height="148"></a></p> <ul> <li>Set the message recipients and other information in the <strong>Email settings</strong> session.</li></ul> <p><a href="http://lh6.ggpht.com/-dVGMW_SpHcQ/UijbmQy4VFI/AAAAAAAAA7s/IlwsT0guch4/s1600-h/image%25255B30%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-CTO6qXaIBk4/UijboL8XRII/AAAAAAAAA70/aHRx3gsgD8k/image_thumb%25255B10%25255D.png?imgmax=800" width="244" height="165"></a></p> <ul> <li>Click on <strong>Save</strong> button to create the alert.</li></ul> <p> </p> <h3>Manage the Data Alert</h3> <p>Once the data alert been created, you could manage/edit it using Data Alert Manager. </p> <ul> <li>To open the data alert manager, right click on the report and click on <strong>the Manage Data Alert</strong> menu.</li></ul> <p><a href="http://lh4.ggpht.com/-3AVBmJbPmtE/UijboYS-_gI/AAAAAAAAA78/Bkq2oGpx-G4/s1600-h/image%25255B38%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-sMQh3l2HA4A/UijbozM035I/AAAAAAAAA8E/iMz5Y7PSrN0/image_thumb%25255B14%25255D.png?imgmax=800" width="328" height="200"></a></p> <ul> <li>Right click on the alert name, you could select <strong>Edit</strong> or <strong>Delete</strong> the alert to modify it.</li> <li>You could also select <strong>Run</strong> option to execute the data alert. </li></ul> <p><a href="http://lh6.ggpht.com/-jko-xuyd9Lg/UijbpFqB1-I/AAAAAAAAA8M/OhddatJDVgE/s1600-h/image%25255B34%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-iWh6iUAU04g/UijbpsOBcVI/AAAAAAAAA8U/NP7I5NheNf4/image_thumb%25255B12%25255D.png?imgmax=800" width="455" height="124"></a></p> <p>Data Alert Manager also provides alert histories and status for review. When condition met, the system will send out the e-mail message and you will see the Sent Alerts Count increased. </p> <p><a href="http://lh3.ggpht.com/-uz5bnYbkS0A/UijbpydH6RI/AAAAAAAAA8c/ACbVxGj8l7w/s1600-h/image%25255B49%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-IiydztmzBvw/UijbqepGKII/AAAAAAAAA8k/EPmRYqjt6F0/image_thumb%25255B19%25255D.png?imgmax=800" width="467" height="81"></a></p> <p>Here is a sample data alert email. </p> <p><a href="http://lh5.ggpht.com/-hrtYVTokwXY/Uijbq9pJyiI/AAAAAAAAA8s/gWersm8dkWc/s1600-h/image%25255B51%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-kyZkimpatQU/UijbrZb-I8I/AAAAAAAAA8w/QanEj3-r1Jc/image_thumb%25255B21%25255D.png?imgmax=800" width="481" height="230"></a></p> <p>It provides the actual data alert result and rules/report parameters used for the SSRS report. In addition, there is a hyperlink in the message for use to review the report detail as needed.</p> <h3></h3> <h3>Setup SMS Alert</h3> <p>If you like to send out the data alert to the mobile device with SMS format, you could use the SMS through email option. <a href="http://en.wikipedia.org/wiki/List_of_SMS_gateways" target="_blank">Different provider uses different SMS gateway.</a> Make sure you use the correct gateway address. For example, if you have AT&T mobile number, you could set the PhoneNumber@mms.att.net or phoneNumber@txt.att.net in the recipients address. When the data alert condition met, it will send a big text message to that phone number.</p> <p><a href="http://lh4.ggpht.com/-NK-g1WhRCUI/Uijbr5ykfsI/AAAAAAAAA88/jyyDGPw-Hg0/s1600-h/image%25255B54%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-3eMhAj0YBsk/UijbsR4bpXI/AAAAAAAAA9E/8Wb6lbJSMtA/image_thumb%25255B22%25255D.png?imgmax=800" width="244" height="117"></a></p> <p>Depended on the provider you used, it is likely to be several of them. Here is a sample:</p> <p><a href="http://lh4.ggpht.com/-J8_49KaNTZk/UijbsvPhEZI/AAAAAAAAA9M/Z3jfqEuK68w/s1600-h/image%25255B60%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh5.ggpht.com/-yVVH6igZeU8/Uijbs0nBVlI/AAAAAAAAA9U/SwwaAyF4CMc/image_thumb%25255B24%25255D.png?imgmax=800" width="133" height="244"></a></p> <h3>Requirements</h3> <p>To use the SSRS data alert, you must have the followings:</p> <ul> <li>SQL 2012 SSRS integrated mode. It could be integrated with SharePoint 2010 or SharePoint 2013.</li> <li>Enable SQL Server Agent</li> <li><a href="http://fendy-huang.blogspot.com/2013/05/setup-ssrs-email-delivery-option-using.html" target="_blank">Enable SSRS email delivery option.</a></li> <li>Need <strong>Manage Alert</strong> SharePoint permission to setup/manage the data alert.</li> <li>Use stored credentials or no credentials for report data source.</li></ul> <p><a href="http://lh4.ggpht.com/-SWVG7koi_cs/UijbtfEOAaI/AAAAAAAAA9c/twBWO8MXdWY/s1600-h/image%25255B2%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-KbvICcuxnPI/Uijbt3zvbiI/AAAAAAAAA9k/B9P_tec7K7E/image_thumb.png?imgmax=800" width="241" height="244"></a></p> <blockquote> <p>If the New Data Alert option is grayed out, the report data source must be configured to use either integrated security credentials or prompt for credentials. To enable the option, you must set the data source to use stored credentials or no credentials.</p></blockquote> <h3>Reference</h3> <p><a title="http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx" href="http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx">http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx</a></p> <p><a title="http://technet.microsoft.com/en-us/library/gg492252.aspx" href="http://technet.microsoft.com/en-us/library/gg492252.aspx">http://technet.microsoft.com/en-us/library/gg492252.aspx</a></p> <p><a title="http://en.wikipedia.org/wiki/List_of_SMS_gateways" href="http://en.wikipedia.org/wiki/List_of_SMS_gateways">http://en.wikipedia.org/wiki/List_of_SMS_gateways</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com6tag:blogger.com,1999:blog-8585049388147973885.post-32692586976217274992013-07-30T12:02:00.000-04:002013-08-23T12:04:45.998-04:00Use DAX Search Function to Retrieve Subset Data in SSRS Report<p>Most of you would be familiar with substring search in T-SQL. If you have a SSRS report using DAX, do you ever winder how to do the search using the pass in parameter?</p> <p>Here is the sample DAX query used to retrieve the Order Count and Sales Amount for Customers from Internet Sales:</p> <div id="codeSnippetWrapper"> <div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; width: 97.5%; background-color: #f4f4f4"><pre id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4">EVALUATE<br><br>SUMMARIZE ( <br><br>CALCULATETABLE(<br> <span style="color: #006080">'FactInternetSales'</span> <br> )<br>,DimCustomer[CustomerName]<br>,DimCustomer[Gender]<br>,"<span style="color: #0000ff">Order</span> Quantity", [<span style="color: #0000ff">Order</span> <span style="color: #0000ff">Count</span>]<br>,"Sales Amount", [<span style="color: #0000ff">Sum</span> <span style="color: #0000ff">of</span> Sales Amount]<br><br>)</pre><br></div><br></div><br /><div>Here is the sample result:</div><br /><div> </div><br /><div><a href="http://lh3.ggpht.com/-Km6jfcCG2Sc/UheGzEwXErI/AAAAAAAAA4w/CImp5BJWNKg/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-rUNP4RHxI4U/UheGzV31X7I/AAAAAAAAA44/EuNfwNGFlMc/image_thumb%25255B1%25255D.png?imgmax=800" width="343" height="179"></a></div><br /><div> </div><br /><div>If you only want to return certain customer, you will need to add a filter by pass in the Customer name as the report parameter. Here is the sample DAX query:</div><br /><div> </div><br /><div> </div><br /><div id="codeSnippetWrapper"><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; width: 97.5%; background-color: #f4f4f4"><pre id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4">EVALUATE<br><br>SUMMARIZE ( <br><br>CALCULATETABLE(<br> <span style="color: #006080">'FactInternetSales'</span>, DimCustomer[CustomerName] = @CustomerName<br> )<br>,DimCustomer[CustomerName]<br>,DimCustomer[Gender]<br>,"<span style="color: #0000ff">Order</span> Quantity", [<span style="color: #0000ff">Order</span> <span style="color: #0000ff">Count</span>]<br>,"Sales Amount", [<span style="color: #0000ff">Sum</span> <span style="color: #0000ff">of</span> Sales Amount]<br><br>)</pre><br></div><br></div><br /><div>Then you could get the result that has exact match to the customer name passed in.</div><br /><div> </div><br /><div><a href="http://lh4.ggpht.com/-DUdj-E1mZVY/UheG2J_cDPI/AAAAAAAAA5A/XkDkmUwpbDs/s1600-h/image%25255B7%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-yDIo4VOIbCg/UheG2gwe8zI/AAAAAAAAA5E/garlDfiAKn0/image_thumb%25255B3%25255D.png?imgmax=800" width="356" height="142"></a><br></div><br /><div>If you need to return all the customers that has name like “Peter”, you could use <a href="http://technet.microsoft.com/en-us/library/ee634235.aspx" target="_blank">DAX Search</a> function to retrieve the list. Here is the example:</div><br /><div> </div><br /><div id="codeSnippetWrapper"><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; width: 97.5%; background-color: #f4f4f4"><pre id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4">EVALUATE<br><br>SUMMARIZE ( <br><br>CALCULATETABLE(<br> <span style="color: #006080">'FactInternetSales'</span>, <br> IFERROR(<span style="color: #0000ff">Search</span>(@CustomerName, DimCustomer[CustomerName]), -1) > 0<br> )<br>,DimCustomer[CustomerName]<br>,DimCustomer[Gender]<br>,"<span style="color: #0000ff">Order</span> Quantity", [<span style="color: #0000ff">Order</span> <span style="color: #0000ff">Count</span>]<br>,"Sales Amount", [<span style="color: #0000ff">Sum</span> <span style="color: #0000ff">of</span> Sales Amount]<br><br>)</pre><br></div><br /><div> </div></div><br /><div>The above query equals to the following T-SQL:</div><br /><div id="codeSnippetWrapper"><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; width: 97.5%; background-color: #f4f4f4"><pre id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4"><span style="color: #0000ff">SELECT</span> <br>[CustomerName] ,[Gender], [<span style="color: #0000ff">Order</span> <span style="color: #0000ff">Count</span>],[<span style="color: #0000ff">Sum</span> <span style="color: #0000ff">of</span> Sales Amount]<br><span style="color: #0000ff">FROM</span> [dbo].[FactInternetSales] F <br><span style="color: #0000ff">INNER</span> <span style="color: #0000ff">JOIN</span> [dbo].[DimCustomer] C<br><span style="color: #0000ff">On</span> C.CustomerKey = F.CustomerKey<br><span style="color: #0000ff">WHERE</span> C.[CustomerName] <span style="color: #0000ff">like</span> @CustonerName<br><br><br></pre><br></div><br /><div id="codeSnippetWrapper"><br /><div id="codeSnippetWrapper">You can see the result as below. All the customers with name containing “Peter” got returned.</div></div><br /><div> </div><br /><div><a href="http://lh5.ggpht.com/-kSg4AareMuY/UheG3CVVrtI/AAAAAAAAA5Q/X9Qjdklt2fg/s1600-h/image%25255B19%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-Vdy3nXhUyHU/UheG4yAk6QI/AAAAAAAAA5Y/AIDVOFyFf-M/image_thumb%25255B9%25255D.png?imgmax=800" width="322" height="154"></a></div><br /><div> </div></div><br /><div>Sometime you may also want to handle the situation when user passed in Space or Blank parameter. Here is a technique you could use. When Blank customer name entered, the report will return all the customer without any filter.</div><br /><div id="codeSnippetWrapper"><br /><div id="codeSnippetWrapper" style="overflow: auto; cursor: text; font-size: 8pt; border-top: silver 1px solid; height: 226px; font-family: 'Courier New', courier, monospace; border-right: silver 1px solid; border-bottom: silver 1px solid; padding-bottom: 4px; direction: ltr; text-align: left; padding-top: 4px; padding-left: 4px; margin: 20px 0px 10px; border-left: silver 1px solid; line-height: 12pt; padding-right: 4px; max-height: 200px; width: 97.5%; background-color: #f4f4f4"><pre id="codeSnippet" style="border-top-style: none; overflow: visible; font-size: 8pt; border-left-style: none; font-family: 'Courier New', courier, monospace; border-bottom-style: none; color: black; padding-bottom: 0px; direction: ltr; text-align: left; padding-top: 0px; border-right-style: none; padding-left: 0px; margin: 0em; line-height: 12pt; padding-right: 0px; width: 100%; background-color: #f4f4f4">EVALUATE<br><br>SUMMARIZE ( <br><br>CALCULATETABLE(<br> <span style="color: #006080">'FactInternetSales'</span>, <br> <span style="color: #0000ff">IF</span>(@CustomerName = blank(), 1=1, IFERROR(<span style="color: #0000ff">Search</span>(@CustomerName, DimCustomer[CustomerName]), -1) > 0 )<br> )<br>,DimCustomer[CustomerName]<br>,DimCustomer[Gender]<br>,"<span style="color: #0000ff">Order</span> Quantity", [<span style="color: #0000ff">Order</span> <span style="color: #0000ff">Count</span>]<br>,"Sales Amount", [<span style="color: #0000ff">Sum</span> <span style="color: #0000ff">of</span> Sales Amount]<br><br>)</pre><br></div><br /><div> </div><br /><div>Here is the result:</div><br /><div> </div><br /><div><a href="http://lh5.ggpht.com/-s6h6HH_1BkE/UheG5HCxuyI/AAAAAAAAA5g/AT8fI3FrXuQ/s1600-h/image%25255B16%25255D.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-Arr6XdyiVPc/UheG8J0W-0I/AAAAAAAAA5o/G0xa8Wtxhzc/image_thumb%25255B8%25255D.png?imgmax=800" width="340" height="173"></a></div><br /><div> </div><br /><h4></h4><br /><h3>Reference</h3><br /><div><a title="http://technet.microsoft.com/en-us/library/ee634235.aspx" href="http://technet.microsoft.com/en-us/library/ee634235.aspx">http://technet.microsoft.com/en-us/library/ee634235.aspx</a><br></div><br /><div><br></div></div> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com3tag:blogger.com,1999:blog-8585049388147973885.post-61661368155105644372013-05-31T11:52:00.000-04:002013-06-11T11:54:33.968-04:00Setup SSRS email Delivery Option Using Gmail Account<p> </p> <p>In order to use the SSRS email delivery subscription or Data alerts feature, you need to configure the email option. If you don’t have access to the company exchange server, you could configure the system to use a Gmail account instead. Here are the steps:</p> <h3>Configure a virtual SMTP Server</h3> <ul> <li>Install SMTP Server window option on the local box or a server. <li>Open IIS 6.0 manager to configure the SMTP option: </li> <ul> <li>Click on the <strong>Access</strong> tab and select <strong>Anonymous access</strong> option in the <strong>Authentication</strong> option box</li></ul></ul> <p><a href="http://lh4.ggpht.com/-0nM60vCHeU4/UbdIJ1W9EFI/AAAAAAAAA1c/b0nghMn87GA/s1600-h/image14.png"><img title="image" style="border-left-width: 0px; border-right-width: 0px; background-image: none; border-bottom-width: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; display: inline; padding-right: 0px; border-top-width: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-R_HiNmmkTdI/UbdIKNCuP-I/AAAAAAAAA1g/9WRceH93gb4/image_thumb4.png?imgmax=800" width="200" height="244"></a></p> <ul> <ul> <li>Click on the <strong>Outbound Security</strong> option button from the <strong>Delivery</strong> tab, select the Basic authentication with your Gmail account and enable the <strong>TLS Encryption.</strong></li></ul></ul> <p><a href="http://lh3.ggpht.com/-ZhWGe4eNuKg/UbdIKY7GB0I/AAAAAAAAA1s/ZEg54UHKRN8/s1600-h/image15%25255B1%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-CivnkeKF8kQ/UbdIKt_xtjI/AAAAAAAAA1w/TpRXWcxWbS4/image15_thumb.png?imgmax=800" width="291" height="171"></a></p> <ul> <ul> <li>Click on the Outbound connections option button, set the TCP Port number to <em>587</em></li></ul></ul> <p><a href="http://lh3.ggpht.com/-u_qCYE5QT_A/UbdIK-PEg7I/AAAAAAAAA14/4bsYFuZ64yo/s1600-h/image18%25255B1%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-3NQIfM_RlVc/UbdILFaMq5I/AAAAAAAAA2E/pQXiZu8bUSs/image18_thumb.png?imgmax=800" width="293" height="174"></a></p> <ul> <ul> <li>Click on the <strong>Advance</strong> option button, set the <strong>Smart Host</strong> to use <em>smtp.gmail.com</em></li></ul></ul> <p><a href="http://lh4.ggpht.com/-1aCZ1G49T5I/UbdILq6e37I/AAAAAAAAA2I/4AcDvK9vTJA/s1600-h/image21%25255B1%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-cNRO-N40DSQ/UbdIL2n1dFI/AAAAAAAAA2Q/wX6DyPekhj0/image21_thumb.png?imgmax=800" width="294" height="170"></a></p> <p>Now you need to configure the SSRS to use the email delivery method. Depend on the SSRS mode, you will do the following:</p> <h3>SSRS Integrated Mode</h3> <p>Open SharePoint 2013 <strong>Central Administration</strong> and select the <strong>Manage Service applications </strong>option</p> <p><a href="http://lh3.ggpht.com/-zvhfq0B7tEs/UbdIMMGDVpI/AAAAAAAAA2U/XgEgT_jj7Xo/s1600-h/image24%25255B1%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-kTAVe32MGLY/UbdIMeud92I/AAAAAAAAA2k/T-8F0d9zqlM/image24_thumb.png?imgmax=800" width="299" height="175"></a></p> <ul> <li>Click on the <strong>SQL Server Reporting Services Service</strong> to open the configuration page <li>Click on the E-mail Setting link</li></ul> <p><a href="http://lh4.ggpht.com/-de3lqkkSoKw/UbdIMoo6wtI/AAAAAAAAA2s/dzUwxfxPEuE/s1600-h/image30%25255B1%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh4.ggpht.com/-v7_WElbVsmI/UbdIMw0hqsI/AAAAAAAAA2w/_gPMPXjmGNM/image30_thumb.png?imgmax=800" width="274" height="206"></a> </p> <ul> <li>Enable the <strong>Use SMTP server</strong> option and set the <strong>Outbound SMTP server</strong> name or IP address; Set the <strong>From address</strong> to your Gmail address same as you used in the setup IIS step.</li></ul> <p><a href="http://lh5.ggpht.com/-zYojciky7_o/UbdINChRlNI/AAAAAAAAA24/zf9S5KG86Vo/s1600-h/image33%25255B1%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh6.ggpht.com/-NQiaDy5CU1A/UbdINb5dKaI/AAAAAAAAA3A/UiFK01jvYlo/image33_thumb.png?imgmax=800" width="279" height="207"></a></p> <ul> <li>Click on OK button. You are good to go now.</li></ul> <h3></h3> <h3>SSRS Native Mode</h3> <p>Open Report Service Configuration manager.</p> <ul> <li>Click on the e-mail setting tab</li> <li>Type your Gmail address in the <strong>Sender Address</strong> on the SMTP Settings window</li> <li>Set the SMTP Server to the server name as previous step</li></ul> <p><a href="http://lh3.ggpht.com/-3Lqa36NYFhU/UbdINooZokI/AAAAAAAAA3M/XwZrHsc54i8/s1600-h/image36%25255B1%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-SoZdL5bNjns/UbdIN5NISLI/AAAAAAAAA3Q/KuFxJHiqMf8/image36_thumb.png?imgmax=800" width="341" height="136"></a></p> <ul> <li>Click on <strong>Apply</strong> button. You are good to go now.</li></ul> <h3>Send a SMS text message from email</h3> <p>If you want to send SMS text for the Data Alert. You could type in the 10 digit number with the mobile provider extension as the example below to send the SMNS text.</p> <p><a href="http://lh5.ggpht.com/-W422fKxVTiY/UbdIOPNRLDI/AAAAAAAAA3Y/yO7Ibtv7O20/s1600-h/image%25255B3%25255D.png"><img title="image" style="border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px 0px 0px 40px; border-left: 0px; display: inline; padding-right: 0px" border="0" alt="image" src="http://lh3.ggpht.com/-roKDultpU5Y/UbdIOQZOQBI/AAAAAAAAA3g/irpi_qPrM1Y/image_thumb%25255B1%25255D.png?imgmax=800" width="353" height="237"></a></p> <p> </p> <h4>Reference</h4> <p><a href="http://www.vsysad.com/2012/04/setup-and-configure-smtp-server-on-windows-server-2008-r2/" target="_blank">http://www.vsysad.com/2012/04/setup-and-configure-smtp-server-on-windows-server-2008-r2/</a></p> <p><a title="http://www.nytimes.com/2011/11/03/technology/personaltech/how-to-send-text-messages-by-e-mail.html?_r=0" href="http://www.nytimes.com/2011/11/03/technology/personaltech/how-to-send-text-messages-by-e-mail.html?_r=0">http://www.nytimes.com/2011/11/03/technology/personaltech/how-to-send-text-messages-by-e-mail.html?_r=0</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com3tag:blogger.com,1999:blog-8585049388147973885.post-22907973524084106492013-03-27T14:12:00.000-04:002013-04-19T14:14:53.067-04:00Trigger SQL Agent Job by Files<p> <p>Wondering how to trigger a SQL agent job when the file is ready to be processed? The simple and easy way to trigger a SQL Job after a file dropped into a window directory is attaching a SQL alert to the SQL job. <p>Here are the steps: <p>1. Open the job property window for the existed SQL agent Job. <p>2. Click on the <strong>Alert Node</strong> <p>3. Click on <strong>Add</strong> button to open new Alert window <p><a href="http://lh4.ggpht.com/-JtnOq0R44Qw/UXGJGgsYkBI/AAAAAAAAAzo/Emftx9mVxTE/s1600-h/image%25255B19%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 50px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-2mVh6j7plBA/UXGJHI279nI/AAAAAAAAAzw/Cr1AbjHJ29g/image_thumb%25255B8%25255D.png?imgmax=800" width="262" height="259"></a> <p>4. Select WMI Alert Event type and set the property for Namespace and query <p><a href="http://lh3.ggpht.com/-Eu-RMQJWaqg/UXGJHXDUaxI/AAAAAAAAAz4/RuGIAUWmZOE/s1600-h/image%25255B18%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 50px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-LlAY3KAeZac/UXGJH8X-VlI/AAAAAAAAA0A/s96TPsgvyM8/image_thumb%25255B7%25255D.png?imgmax=800" width="297" height="286"></a> <p>5. Click on OK to attach it. <p>Once file dropped in the folder, the SQL job will be triggered. The example I used will insert a row to [dbo].[FileProcessTime] table. <p><a href="http://lh3.ggpht.com/-lSNn6lsthvA/UXGJIABh8vI/AAAAAAAAA0I/DSU02C-SGfI/s1600-h/image%25255B16%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 50px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-Ie6RhVNCeKg/UXGJIcN7rOI/AAAAAAAAA0Q/VOFDnnnrilk/image_thumb%25255B5%25255D.png?imgmax=800" width="326" height="174"></a> <p>you may go to the Job property-><strong>History</strong> Page for the Alert or Query [msdb].[dbo].[sysalerts] table to check the the Number of occurrences and Last execution Time.</p> <p><a href="http://lh6.ggpht.com/-J-OvkktSA3o/UXGJIvUa5UI/AAAAAAAAA0Y/_fQDn2OVJcc/s1600-h/image%25255B15%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 50px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-5zmnUhJnMK8/UXGJJKFYVuI/AAAAAAAAA0g/z8WIQktrpgg/image_thumb%25255B4%25255D.png?imgmax=800" width="244" height="224"></a></p> <p>Sometime you may consider to delay the response for large or frequent files, you could go into the <strong>Option page</strong> to set it. </p> <p><a href="http://lh4.ggpht.com/-bGAhpb0speM/UXGJJfgVlEI/AAAAAAAAA0k/736IWxg6AkE/s1600-h/image%25255B25%25255D.png"><img style="background-image: none; border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 50px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-nNMAbHli1v0/UXGJJ-nDOxI/AAAAAAAAA0s/3j7smczoTIs/image_thumb%25255B10%25255D.png?imgmax=800" width="244" height="201"></a></p> <h3>Reference:</h3> <p><a title="http://msdn.microsoft.com/en-us/library/ms190084.aspx" href="http://msdn.microsoft.com/en-us/library/ms190084.aspx">http://msdn.microsoft.com/en-us/library/ms190084.aspx</a></p> <p><a title="http://msdn.microsoft.com/en-us/library/ms186510.aspx" href="http://msdn.microsoft.com/en-us/library/ms186510.aspx">http://msdn.microsoft.com/en-us/library/ms186510.aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com6tag:blogger.com,1999:blog-8585049388147973885.post-80930784031649459982012-11-02T11:13:00.001-04:002012-11-02T11:13:09.493-04:00SQL Saturday #174 Recap<p> </p> <p><font size="2">What a great success SQL Saturday was in Charlotte last weekend. I volunteered to be a proctor in the morning, so I could attend and also help out. My presentation on “Explore to Integration Services Catalog” went well with full house of attendees. Thanks to all the people who attend my session and everyone in the Charlotte BI group for organizing a great event. </font></p> <p><font size="2">I was asked to post my PowerPoint presentation. </font></p> <p><font size="2">Here is the link:</font></p> <p><a href="https://docs.google.com/open?id=0B__3sWYKOHrGQVlRMGdIVEt5Tkk" target="_blank"><font size="2">Explore the Integration Services Catalog</font></a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com0tag:blogger.com,1999:blog-8585049388147973885.post-20867290626965101452012-10-05T15:21:00.000-04:002012-10-31T11:27:36.712-04:00Use Sybase SQL Anywhere as data source for PowerPivot<p><font size="2">To use Sybase SQL Anywhere as the Power Pivot data source, you need to use ODBC. It is not intuitive to set it up. After encountered several errors, I am able to load the data into PowerPivot to analyze it now.</font></p> <p><font size="2">Here is the list of steps:</font></p> <ul> <li><font size="2">Install SQL Anywhere Client. You may download it from Sybase we: </font><a href="http://www.sybase.com/detail?id=1087327"><font size="2">http://www.sybase.com/detail?id=1087327</font></a><font size="2">. If you have 32 bits Office product, you need to install 32 bit client. </font> <li><font size="2">Open the Sybase Central App and create a new connection to the Sybase Host and the database you want to load the data from. </font> <li><font size="2">After you test the connection, click on the Save ODBC Data Source menu on the Tools button.</font></li></ul> <blockquote> <p><a href="http://lh4.ggpht.com/-MMzJUJM1J_A/UGYHukRzsII/AAAAAAAAAw8/2slzras1jAI/s1600-h/clip_image001%25255B1%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001" border="0" alt="clip_image001" src="http://lh3.ggpht.com/-I-gRS48t-hs/UGYHvVIPfeI/AAAAAAAAAxE/Exidr0edPjc/clip_image001_thumb.png?imgmax=800" width="445" height="355"></a></p></blockquote> <ul> <li><font size="2">When the <strong>Save as ODBC Data Source</strong> window open, type in a name for the ODBC and click on the <strong>Save</strong> button.</font></li></ul> <blockquote> <p><a href="http://lh5.ggpht.com/-WuEmE-Kze4g/UGYHv0Aba9I/AAAAAAAAAxM/-EabLjbPK3M/s1600-h/clip_image00153.png"><font size="2"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001[5]" border="0" alt="clip_image001[5]" src="http://lh5.ggpht.com/-sZmq00v3ta8/UGYHwgS4ETI/AAAAAAAAAxU/7WAOIQ41JbM/clip_image0015_thumb1.png?imgmax=800" width="343" height="203"></font></a> </p></blockquote> <ul> <li><font size="2">You could now open the ODBC data source admin to verify it been created. Please note that if you install 32 bit SQL Anywhere on a 64 bit windows machine, then you will need to use the 32 bit ODBC to view/configure it. You could find it in C:\Windows\System32\odbcad32.exe.</font></li></ul> <blockquote> <p><a href="http://lh5.ggpht.com/-rYqmvy1MBlI/UGYHxA8yo0I/AAAAAAAAAxc/C4KZkunt6HQ/s1600-h/clip_image00173.png"><font size="2"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001[7]" border="0" alt="clip_image001[7]" src="http://lh3.ggpht.com/-5ucca-1g_Uc/UGYHxl5OVgI/AAAAAAAAAxk/z8fK9ebReqw/clip_image0017_thumb1.png?imgmax=800" width="308" height="265"></font></a><font size="2"> </font></p></blockquote> <ul> <li><font size="2">In Excel, open the PowerPivot, click on the Get External Data from Other Sources on the Home page. </font> <li><font size="2">Select Others(OLEDB/ODBC) from the list to specify the Connection String </font></li></ul> <blockquote> <p><a href="http://lh4.ggpht.com/-0zgQm9PA0Kw/UJE82WlFmZI/AAAAAAAAAyU/oma_6g9_pvo/s1600-h/image%25255B5%25255D.png"><font size="2"><img style="background-image: none; border-right-width: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-JfB3pelRnt0/UJE823UIYrI/AAAAAAAAAyY/vAjlONI33Hc/image_thumb%25255B1%25255D.png?imgmax=800" width="244" height="209"></font></a></p></blockquote> <ul> <li><font size="2">You could type in the following connection string in the Table Import Wizard window: </font> <ul><font size="2">Provider=SAOLEDB.12;Data Source=DataSourceName; Persist Security Info=False </font></ul> <ul><font size="2"></font></ul> <ul><font size="2"></font></ul> <ul><font size="2"></font></ul> <ul><font size="2"></font></ul> <ul><font size="2"></font></ul><a href="http://lh3.ggpht.com/-aNxU2c3u1eQ/UGYHyJHsVMI/AAAAAAAAAxs/sHMlLwBCdSc/s1600-h/clip_image0014.jpg"><font size="2"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001" border="0" alt="clip_image001" src="http://lh5.ggpht.com/-D2ztjOEJpRc/UGYHzApONFI/AAAAAAAAAx0/YR3DRXymynk/clip_image001_thumb1.jpg?imgmax=800" width="337" height="212"></font></a><font size="2"> </font></li></ul> <p><font size="2"></font></p> <ul> <li><font size="2">Or Click on the <strong>Build</strong> button to open the Data Link Properties window to select the ODBC just created.</font></li></ul> <blockquote> <p><a href="http://lh4.ggpht.com/-94Vp-1YyQ_I/UJE83V7Fx_I/AAAAAAAAAyk/CT-IdTpcDTg/s1600-h/image%25255B8%25255D.png"><font size="2"><img style="background-image: none; border-right-width: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-Qx8DMEC6zPQ/UJE84MdZHbI/AAAAAAAAAys/1_VtjtQoXb8/image_thumb%25255B2%25255D.png?imgmax=800" width="192" height="244"></font></a><a href="http://lh3.ggpht.com/-6hDlkuNOcYo/UJE84qXnnXI/AAAAAAAAAy0/06X8GZiYisA/s1600-h/clip_image001%25255B1%25255D%25255B2%25255D.png"><font size="2"><img style="background-image: none; border-right-width: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001[1]" border="0" alt="clip_image001[1]" src="http://lh3.ggpht.com/-wAWADt5WFtM/UJE85LNBwvI/AAAAAAAAAy8/cuIFX9wVdRA/clip_image001%25255B1%25255D_thumb.png?imgmax=800" width="199" height="244"></font></a></p> <p><font size="2">Now, you should be able to load the data from SQL Anywhere data source into PowerPivot workbook.</font></p></blockquote> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com0tag:blogger.com,1999:blog-8585049388147973885.post-69171238996222040612012-10-03T15:46:00.001-04:002012-10-03T15:49:45.809-04:00SQL Saturday #174<p>This SQL Saturday training event is focused on Microsoft Business Intelligence topics. <p>I am honored to have been selected to present “<strong><a href="http://www.sqlsaturday.com/viewsession.aspx?sat=174&sessionid=11509" target="_blank">Explore the Integration Services Catalog</a></strong>” out SQL Server on October 27, 2012 in Charlotte North Carolina. <p>I am very excited as this will be my first time presenting in SQL Saturday. <p>If you in the area, there is no better opportunity to get free Microsoft BI training. I am looking forward to meeting you there. <p><a href="http://www.sqlsaturday.com/174/eventhome.aspx" target="_blank"><img style="background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; border-top: 0px; border-right: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://javierguillen.files.wordpress.com/2012/09/image_thumb12.png?w=310&h=158" width="310" height="158"></a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com1tag:blogger.com,1999:blog-8585049388147973885.post-46485284710012097162012-09-22T08:00:00.000-04:002013-01-29T19:14:22.371-05:00Create SSRS Report using DAX<p> </p> <p><font size="2">We could query BISM Tabular model using either MDX or DAX. People are very familiar with how to use MDX in SSRS. Using DAX in SSRS is totally different from using MDX. Here is an example to create SSRS report using DAX language:</font></p> <p><a href="http://lh3.ggpht.com/-9h5d-tlBsTI/UFzV5QvFtBI/AAAAAAAAAuo/vuBSLT34uDM/s1600-h/image3.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-yPYlwunIqGU/UFzV538tatI/AAAAAAAAAuw/0-bMLtLTq94/image_thumb1.png?imgmax=800" width="424" height="237"></a></p> <h2>List of Steps:</h2> <blockquote> <p><font size="2">1. Open SQL Server Data Tools to create a SSRS report.</font></p> <p><font size="2">2. Add an existed BISM Tabular Model as the Shared data source.</font></p> <p><font size="2">3. Write the DAX in the SSMS or DAX Studio first. The DAX Studio is an Excel Add-Ins that you could download from the CodePlex.</font></p></blockquote> <div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 233px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"> <div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">EVALUATE</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">SUMMARIZE</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> ( </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> CALCULATETABLE( <span style="color: #006080">'Internet Sales'</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> ,<span style="color: #006080">'Product Category'</span>[Product Category Name] = "Bikes" </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> ) ,</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> <span style="color: #006080">'Date'</span>[Calendar <span style="color: #0000ff">Year</span>],</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> <span style="color: #006080">'Product Category'</span>[Product Category Name], </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> "Total Sales", <span style="color: #006080">'Internet Sales'</span>[Internet Total Sales], </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> "Total Tax Amount", <span style="color: #006080">'Internet Sales'</span>[Internet Total Tax Amt], </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> "Total Margin", <span style="color: #006080">'Internet Sales'</span>[Internet Total Margin] </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--></div></div><br /><div id="codeSnippetWrapper"> </div><br /><blockquote><br /><p><font size="2">4. Create SSRS dataset</font></p></blockquote><br /><ul><br /><ul><br /><li><font size="2">Add a new dataset and connect to the BISM Tabular Model</font> <br /><li><font size="2">Open Query Designer</font> <br /><li><font size="2">Click on the <strong>Command Type DMX</strong> button on the toolbar to switch to the DMX query designer.</font></li></ul></ul><br /><p><font size="2"><a href="http://lh4.ggpht.com/-YFXJNap4e1s/UFzV6B2LYvI/AAAAAAAAAu4/cURVXrxhWps/s1600-h/image4.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-JpJ8E06GgnQ/UFzV6YAP1nI/AAAAAAAAAvA/05pNmCeDs7g/image_thumb11.png?imgmax=800" width="320" height="176"></a></font></p><br /><ul><br /><ul><br /><li><font size="2">Click on the Query/Design Mode button to switch to the Query mode.</font></li></ul></ul><br /><p><font size="2"><a href="http://lh4.ggpht.com/-BeG8jk8Am3Y/UFzV66t0mJI/AAAAAAAAAvI/k8OEC60E_5k/s1600-h/image8.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-aZl6bDrKQnc/UFzV7Q0gPoI/AAAAAAAAAvQ/8WUBViLVIWk/image_thumb3.png?imgmax=800" width="279" height="177"></a></font></p><br /><ul><br /><ul><br /><li><font size="2">Paste or type your DAX query into the Query window and then click on OK button.</font> <br /><li><font size="2">Click on the Fields tab to fix/rename the field name to make it more readable. The field name generated by the query designer will not lineup with the column name you have in the DAX query.</font></li></ul></ul><br /><p><font size="2"> <a href="http://lh6.ggpht.com/-YiaVHgf8AO4/UFzV7p5H49I/AAAAAAAAAvY/NmEkF9x1yJg/s1600-h/image12.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-FV1kONIVnLk/UFzV79LF0yI/AAAAAAAAAvg/vsruDY3q3yc/image_thumb5.png?imgmax=800" width="380" height="234"></a></font></p><br /><ul><br /><ul><br /><li><font size="2">Now, you could add those fields to the report.</font></li></ul></ul><br /><blockquote><br /><p><font size="2">5. Add parameter</font></p><br /><ul><br /><li><font size="2">Go to dataset query, open the query designer.</font> <br /><li><font size="2">Change the DAX query by replacing the filer part of Query with parameter name and click on the Parameter Button to open the <strong>Query Parameters</strong> window</font></li></ul><br /><p><a href="http://lh5.ggpht.com/-pi4MLJ7SZGA/UFzV8pzWd9I/AAAAAAAAAvo/fL8NVG0dI_0/s1600-h/image17.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-etlP5fpxddM/UFzV9G4xtpI/AAAAAAAAAvw/JP2cFr0ueDU/image_thumb8.png?imgmax=800" width="367" height="291"></a></p><br /><p><a href="http://lh5.ggpht.com/-SnCfdlXNrgc/UFzV9bS_sDI/AAAAAAAAAv4/TgHNhl1JZik/s1600-h/image21.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-jMbuBJaItUc/UFzV9_Y07dI/AAAAAAAAAwA/LVTmbN7sV_U/image_thumb10.png?imgmax=800" width="322" height="258"></a></p><br /><ul><br /><li><font size="2">Add Parameter and set the default value, then click OK to exit out the designer</font> <br /><li><font size="2">On the report data window, you will find a parameter been created with default value that you typed in.</font></li></ul><br /><p><a href="http://lh5.ggpht.com/-Nwsv20_eqyU/UFzV-HmUe7I/AAAAAAAAAwI/aNIqvXRA-mo/s1600-h/image25.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-eVqCbeg5LIQ/UFzV-qsfhMI/AAAAAAAAAwQ/P585VJmhLhE/image_thumb12.png?imgmax=800" width="305" height="259"></a></p><br /><p><font size="2">6. Create parameter dataset</font></p><br /><ul><br /><li><font size="2">There is no parameter query generated by the tool. You need to create it if you allow user to pick from the drop down list. </font><br /><li><font size="2">Write a DAX query with VALUES() function using the same steps before to create a dataset that will contain the list of parameter values.</font></li></ul><br /><div id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet">EVALUATE <br><span style="color: #0000ff">values</span>(<span style="color: #006080">'Product Category'</span>[Product Category Name])<br><span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">By</span> <span style="color: #006080">'Product Category'</span>[Product Category Name]<br><br></pre><br></div><br /><ul><br /><li><font size="2">Associate this dataset to the report parameter</font></li></ul><br /><p><a href="http://lh6.ggpht.com/-XRp8GiuvbxY/UFzV--hGcDI/AAAAAAAAAwY/ggLCV_TQyj0/s1600-h/image33.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-LLWqyDHHIL0/UFzV_WPeG7I/AAAAAAAAAwg/xuYTP6IR84s/image_thumb16.png?imgmax=800" width="318" height="270"></a></p><br /><ul><br /><li><font size="2">Now your SSRS report should work with single value selection.</font></li></ul><br /><h2>Report Parameter Allows Multiple Values</h2><br /><p><font size="2">If you allow multiple values for the report parameter, you could not use filter with equal sign (“ = ”) in the DAX query. You could use PATHCONTAINS() function in the filter part of query to filter the result set. </font></p><br /><div id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet">PATHCONTAINS(<<span style="color: #0000ff">path</span>>, <item>)<br></pre><br></div><br /><div><font size="2">Since the multi select parameter string pass in will contain extra special characters that could not be used for the Path() function, you need to use Substitute() function to replace them.</font><br></div></blockquote><br /><blockquote><br /><div><font size="2">Here is the complete DAX query for the report.</font></div></blockquote><br /><div><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">EVALUATE</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">SUMMARIZE(</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> CALCULATETABLE( <span style="color: #006080">'Internet Sales'</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> ,PATHCONTAINS(</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> substitute( </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> substitute( </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> substitute( </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> @CategoryName</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> , "{ ", "") </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> , " }", "") </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> , ",", "|") </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> ,<span style="color: #006080">'Product Category'</span>[Product Category Name] ) </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">), </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> <span style="color: #006080">'Date'</span>[Calendar <span style="color: #0000ff">Year</span>],</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> <span style="color: #006080">'Product Category'</span>[Product Category Name], </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> "Total Sales", <span style="color: #006080">'Internet Sales'</span>[Internet Total Sales], </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> "Total Tax Amount", <span style="color: #006080">'Internet Sales'</span>[Internet Total Tax Amt], </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> "Total Margin", <span style="color: #006080">'Internet Sales'</span>[Internet Total Margin] </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--></div></div></div><br /><div> </div><br /><div><font size="2">After you update your query, the SSRS report will work for multiple selections from the user now.</font></div><br /><div><font size="2"></font> </div><br /><div><font size="2"></font> </div><br /><h4></h4><br /><h4>Reference:</h4><br /><p><a title="http://daxstudio.codeplex.com/" href="http://daxstudio.codeplex.com/">http://daxstudio.codeplex.com/</a></p><br /><p><a title="http://msdn.microsoft.com/en-us/library/gg492182.aspx" href="http://msdn.microsoft.com/en-us/library/gg492182.aspx">http://msdn.microsoft.com/en-us/library/gg492182.aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com7tag:blogger.com,1999:blog-8585049388147973885.post-89597676275192733292012-09-03T20:34:00.000-04:002012-09-05T08:29:07.735-04:00DQS Exception Issues<p><font size="2">Recently I created several SSIS packages with DQS component to do data cleansing jobs while loading data into data warehouse. After deploying the project to the remote server, the packages failed to execute at remote server with following exception thrown:<br></font></p> <div id="codeSnippetWrapper"><pre id="codeSnippet" class="csharpcode"><div id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet">[DQS Cleansing [2]] Error: <br>Microsoft.Ssdqs.Infra.Exceptions.EntryPointException: A fatal error occurred <br>when trying to execute the service.at <br>Microsoft.Ssdqs.Proxy.Database.DBAccessClient.Exec()<br> at <br>Microsoft.Ssdqs.Proxy.EntryPoint.KnowledgebaseManagementEntryPointClient.KnowledgebaseStartBatchActivity(Int64 <br>knowledgebaseId, String activityIdentifier)<br> at <br>Microsoft.Ssdqs.Component.DataCorrection.Logic.DataCorrectionComponent.PreExecute()<br> at <br>Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 <br>wrapper)<br></pre><br></div></pre><br></div><br><br /><p><font size="2">After research and investigation for several days, I figured out that the SQL server version on my PC is different from the remote sever which has SQL 2012 CU2. I then upgraded my client machine to SQL 2012 CU2. <br></font><br /><p><font size="2">After that, some DQS packages will run inside the VSDT, some will still fail with the same exception as the diagram showed:</font> <br><br /><p><a href="http://lh3.ggpht.com/-AswjbqDbjj4/UEaVnsh6IHI/AAAAAAAAArQ/JmXjsNx7rc0/s1600-h/clip_image001%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001" border="0" alt="clip_image001" src="http://lh5.ggpht.com/-NFl5qjBEJUM/UEaVoBcWCkI/AAAAAAAAArY/Z7VF120N1yY/clip_image001_thumb%25255B1%25255D.png?imgmax=800" width="254" height="299"></a> <br><br /><p><font size="2">This is caused by the length of Data Quality Project Name that DQS created, which is limited to 128 characters (a combination of the package name + DQS Cleansing component name + datetime stamp + a few GUIDs). The long project name will fail to be inserted into [DQS_MAIN].[dbo].[A_KNOWLEDGEBASE_ACTIVITY].[KNOWLEDGEBASE_NAME]. </font><br></p><br /><p><font size="2">In addition, I noticed that before CU2, the project name only append one GUID at end which allows longer SSIS package name without failing. So, if you have experience of the DQS package failing to execute after upgrading to CU2, you may have the same problem.<br></font></p><br><br /><p><a href="http://lh4.ggpht.com/-xrMS7HHSeQw/UEaVojqnAnI/AAAAAAAAArg/i1PLVELCIuY/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-Aih2mrK3F6I/UEaVpdxmwHI/AAAAAAAAAro/VRK6iVa-8qk/image_thumb%25255B2%25255D.png?imgmax=800" width="540" height="99"></a></p><br><br /><p><a href="http://lh6.ggpht.com/-a7PvEz0inZE/UEaVqGWnpmI/AAAAAAAAArw/tkGFOrxVNSw/s1600-h/clip_image001%25255B9%25255D%25255B6%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001[9]" border="0" alt="clip_image001[9]" src="http://lh5.ggpht.com/-4cixCCDbgKs/UEaVq5rv4JI/AAAAAAAAAr4/d7DfS9tjKtI/clip_image001%25255B9%25255D_thumb%25255B3%25255D.png?imgmax=800" width="553" height="110"></a><br></p><br /><h4><font size="3">Solution:</font><br></h4><br /><ul><br /><li><font size="2">Right click the DQS Cleansing Component to open the component property page. <br></font><br /><li><font size="2">Rename the <strong>PackageName</strong> + <strong>Name</strong> of the component to be less than 30 characters.</font></li></ul><br><br /><p><font size="2">P.S. Change the <strong>Name</strong> of the package property or SSIS package name will not change the <strong>PackageName</strong> property inside the DQS Cleansing component.</font></p><br /><p><br><a href="http://lh6.ggpht.com/-QOdRkgiF6Pw/UEaVrqYd58I/AAAAAAAAAsA/2CzeRUB0ip4/s1600-h/clip_image001%25255B5%25255D%25255B3%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001[5]" border="0" alt="clip_image001[5]" src="http://lh5.ggpht.com/-5Lt7fs41i-Q/UEaVsTklpnI/AAAAAAAAAsI/R9peujZrnoQ/clip_image001%25255B5%25255D_thumb%25255B1%25255D.png?imgmax=800" width="436" height="218"></a></p><br /><p><font size="2">Now, the SSIS Package is able to run without exception on either client or remote server.</font> <br /><p><a href="http://lh5.ggpht.com/-3ltRUyyNzlQ/UEaVtInqHvI/AAAAAAAAAsQ/wdRgz8BSyFk/s1600-h/clip_image001%25255B7%25255D%25255B3%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="clip_image001[7]" border="0" alt="clip_image001[7]" src="http://lh3.ggpht.com/-HdHL4Eebngc/UEaVt6nVL0I/AAAAAAAAAsY/q79jUbgzlXo/clip_image001%25255B7%25255D_thumb%25255B1%25255D.png?imgmax=800" width="449" height="195"></a> </p><br /><p><font size="3"><font size="2">If you encountered the following error when you open the DQS client application, it is related to the server been upgraded without updating the DQS databases. You could run DQS installer with upgrade option in Command Prompt to resolve the issue.</font> </font></p><br /><p><a href="http://lh5.ggpht.com/-J7F4TLxGzXY/UEdC1oY7mEI/AAAAAAAAAsw/u0uKzDEMjnE/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-f43AegdilaQ/UEdC2MKKe0I/AAAAAAAAAs4/Nq1LwHzwESU/image_thumb%25255B2%25255D.png?imgmax=800" width="379" height="268"></a> </p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 98.98%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 68px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">DQSInstaller.exe -upgrade</pre><!--CRLF--></div></div><br /><p><font face="Bell MT"><font color="#000000" size="2"><em>Special thanks to Jason H. from Microsoft for looking into my question posted on DQS Forums.</em></font> <br></font></p><br /><h4>Reference: </h4><br /><p><a title="http://social.technet.microsoft.com/wiki/contents/articles/8442.upgrade-dqs-installing-cumulative-updates-or-hotfix-patches-on-data-quality-services-en-us.aspx" href="http://social.technet.microsoft.com/wiki/contents/articles/8442.upgrade-dqs-installing-cumulative-updates-or-hotfix-patches-on-data-quality-services-en-us.aspx">http://social.technet.microsoft.com/wiki/contents/articles/8442.upgrade-dqs-installing-cumulative-updates-or-hotfix-patches-on-data-quality-services-en-us.aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com1tag:blogger.com,1999:blog-8585049388147973885.post-22236978696577955142012-07-14T09:00:00.000-04:002012-09-22T10:22:13.518-04:00Alternate Row Color for SSRS Report With Column Group<p><font size="3">There are several ways to alternate background color for rows. The most common way is using RowNumber() function to retrieve the order of the row then set the background color using expression as:</font></p> <p><code><font size="3" face="Georgia">=IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", Nothing)</font></code></p> <p><a href="http://lh5.ggpht.com/-QHp5FLrqL_E/UACE9F_seEI/AAAAAAAAAnk/iblErCAF-R8/s1600-h/image4.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-57oM6a5tzfc/UACE9j8ZTzI/AAAAAAAAAns/V2GJE4rpHn8/image_thumb2.png?imgmax=800" width="334" height="159"></a></p> <p><font size="3">If there is a row group, using RowNumber() can lead to some issues. The another alternative is to use RunningValue() function as:</font></p> <p><font size="3">=IIf(RunningValue(Fields!Product.Value, CountDistinct, "Customer") Mod 2 > 0,"Silver", Nothing)</font></p> <p><a href="http://lh4.ggpht.com/-MKyyRWFz5nI/UACE-MczX5I/AAAAAAAAAn0/M0xK55Uj6bw/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-3Jrbdamydvg/UACE-vEJe_I/AAAAAAAAAn8/HxQDQ2dRrsA/image_thumb%25255B1%25255D.png?imgmax=800" width="359" height="233"></a></p> <h3><font size="3">Dynamic Column Group Scenario</font></h3> <p><a href="http://lh6.ggpht.com/-lF9bS6VLpaw/UACE_Oox6qI/AAAAAAAAAoE/eRZT2NXDAdo/s1600-h/image%25255B18%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-J3D2MT5gUCA/UACE_rPI5OI/AAAAAAAAAoM/lQXTDUA6oaw/image_thumb%25255B8%25255D.png?imgmax=800" width="393" height="144"></a></p> <p><font size="3">When there is a column group with dynamic data (i.e. Sales Reason) , you will not be able to use above solutions to alternate the row color. Since the number of column (the Sales Reason in my example) may be varied depended on the row group data (the Date and Product in my example). Here is what you may see if you use above approach only.</font></p> <p><a href="http://lh5.ggpht.com/-6x9XQpwmGec/UACE_xcxJ4I/AAAAAAAAAoU/iWYZoICd63E/s1600-h/image%25255B14%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-yauR42L5M9M/UACFASWuIdI/AAAAAAAAAoc/YOUP4sQNE90/image_thumb%25255B7%25255D.png?imgmax=800" width="389" height="133"></a></p> <h3><code></code></h3><code> <h3><font face="Georgia">Solution 1</font></h3> <p><font size="3" face="Georgia">Use Customer Code and variable to determine the row color</font></p> <ul> <li><font size="3" face="Georgia">Create a report customer code to toggle the row status for each function call</font></li></ul> <div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"> <div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">Public</span> RowStatus <span style="color: #0000ff">as</span> <span style="color: #0000ff">Boolean</span> = <span style="color: #0000ff">False</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">Public</span> <span style="color: #0000ff">Function</span> GetRowStatus <span style="color: #0000ff">as</span> <span style="color: #0000ff">Boolean</span> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> RowStatus = <span style="color: #0000ff">Not</span> RowStatus </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> <span style="color: #0000ff">Return</span> RowStatus </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">End</span> <span style="color: #0000ff">Function</span></pre><!--CRLF--></div></div><br /><ul><br /><li><font size="3" face="Georgia">Add a variable <em>RowColor</em> to the detail row group that has value set to the expression as below to call the customer function:</font></li></ul><br /><p><font size="3" face="Georgia"> = Code.GetRowStatus()</font></p><br /><p><font size="3" face="Arial"><a href="http://lh5.ggpht.com/-3BqGV4ePrsI/UAQj6KEkpnI/AAAAAAAAAqs/LtThtMDvO_E/s1600-h/image%25255B10%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-XKzhHeChrC8/UAQj7s2bB7I/AAAAAAAAAq0/bfCSohgEoIY/image_thumb%25255B5%25255D.png?imgmax=800" width="398" height="190"></a></font></p><br /><ul><br /><li><font size="3" face="Georgia">Select the whole detail row and set the background color based on the variable value.</font><a href="http://lh5.ggpht.com/-z7ap-b3s2yQ/UACFBocGCWI/AAAAAAAAAo0/_QKe4V6ocLU/s1600-h/image%25255B30%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-NUY31aQlDPM/UACFCD68xmI/AAAAAAAAAo8/jwOzOJbhr7I/image_thumb%25255B14%25255D.png?imgmax=800" width="429" height="96"></a> <br /><li><font size="3" face="Georgia">Now the alternated row color shows up correctly.</font><a href="http://lh4.ggpht.com/-MV_YOgpGlqA/UACFCVuizuI/AAAAAAAAApE/RXOjUQ1PmAU/s1600-h/image%25255B41%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-6Z0SThpMMys/UACFCqRi__I/AAAAAAAAApM/8anut1JSw5o/image_thumb%25255B19%25255D.png?imgmax=800" width="304" height="116"></a></li></ul><br /><p><a href="http://lh6.ggpht.com/-InmhLuIKxp8/UACFDPntfKI/AAAAAAAAApU/zbxVAO6eyaY/s1600-h/image%25255B42%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-gp5b8QcxTlk/UACFDpKAdRI/AAAAAAAAApc/p5qiLm04Tp8/image_thumb%25255B20%25255D.png?imgmax=800" width="306" height="122"></a></p><br /><h3><font face="Georgia">Solution 2</font></h3><br /><p><font size="3" face="Georgia">Store RunningValue() in a textbox and use it to set the row color</font></p><br /><ul><br /><li><font size="3" face="Georgia">Add a new column outside the Column group at end of the report</font></li></ul><br /><p><a href="http://lh5.ggpht.com/-3o25KSYqPyI/UACFD1NRtrI/AAAAAAAAApk/RDyhUdDdDeg/s1600-h/image%25255B48%25255D.png"><font size="3" face="Georgia"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-ZOuZjUt6vBI/UACFEasywSI/AAAAAAAAAps/rbx-yrWvJT8/image_thumb%25255B22%25255D.png?imgmax=800" width="244" height="66"></font></a></p><br /><ul><br /><li><font size="3" face="Georgia">Name the detail row textbox as RowNumber and set its value to the expression as </font></li></ul><br /><p><font size="3" face="Georgia"> =RunningValue(Fields!Product.Value, CountDistinct, "OrderDate" )</font></p><br /><ul><br /><li><font size="3" face="Georgia">Select the whole detail row and set the back ground color to use the expression as:</font></li></ul><br /><p><font size="3" face="Georgia"> =IIF(ReportItems!RowNumber.Value mod 2 > 0, "LightGrey", Nothing)</font></p><br /><p><a href="http://lh4.ggpht.com/-fMg-g7iIIUk/UACFE-yI6nI/AAAAAAAAAp0/LM1NL0xnWSE/s1600-h/image%25255B52%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-f2Qlz1W8lPM/UACFFdlHkbI/AAAAAAAAAp8/DulAmqsHxwQ/image_thumb%25255B24%25255D.png?imgmax=800" width="427" height="80"></a></p><br /><ul><br /><li><font size="3"><br /><p><font size="3" face="Georgia">Now the row color displays correctly and we can hide the last column that contains the RowNumber text box from the end user.</font></p></font></li></ul><br /><p><a href="http://lh5.ggpht.com/-YeQfYegCX9k/UACFFpuywdI/AAAAAAAAAqE/dhDhGvlRlLM/s1600-h/image%25255B56%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-A9nMYC6MDbQ/UACFF_YH69I/AAAAAAAAAqM/RrU7DF6h0J0/image_thumb%25255B26%25255D.png?imgmax=800" width="430" height="169"></a></p><br /><p><a href="http://lh6.ggpht.com/-sJCCT2x9phY/UAQkxkd_NpI/AAAAAAAAAq8/_NX0WsclCZU/s1600-h/image%25255B14%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-Mx903Ch6hjk/UAQkyX2pwBI/AAAAAAAAArE/q29z49YMkgo/image_thumb%25255B7%25255D.png?imgmax=800" width="328" height="124"></a></p></code><br /><h3><code></code></h3><br /><h4>Reference:</h4><br /><p><a title="http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/6dbde4cf-2b3f-4aa6-b47c-5c37c47bfc82#72a83b7f-241c-4c80-b79a-7f6e4b4bc74a" href="http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/6dbde4cf-2b3f-4aa6-b47c-5c37c47bfc82#72a83b7f-241c-4c80-b79a-7f6e4b4bc74a"><font size="3">http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/6dbde4cf-2b3f-4aa6-b47c-5c37c47bfc82#72a83b7f-241c-4c80-b79a-7f6e4b4bc74a</font></a></p><br /><p><a title="http://msdn.microsoft.com/en-us/library/dd255285" href="http://msdn.microsoft.com/en-us/library/dd255285"><font size="3">http://msdn.microsoft.com/en-us/library/dd255285</font></a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com11tag:blogger.com,1999:blog-8585049388147973885.post-59555669369766738402012-06-05T13:50:00.000-04:002012-09-12T20:14:50.727-04:00The Simple DAX Functions as SELECT Statement<br><font size="2">Do you even wonder how to use DAX function to query BISM to return a simple dataset or a dataset that is grouped by some attributes? <br><br><strong>Evaluate</strong> is the core DAX function that returns a table of data. It is similar to the <strong>SELECT</strong> statement in T-SQL.</font> <br> <h2>Here is the Syntax:</h2> <div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.79%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 43px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet">EVALUATE <<span style="color: blue">table</span>> </pre><br></div><br><br><br /><h2><font size="3">Example:</font></h2><font size="2">We want to select all the data from the Internal Sales table.</font><br><br><br><br><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div><br></div><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">EVALUATE(<span style="color: #006080">'Internet Sales'</span>)</pre><!--CRLF--></div></div><br><br><br /><p><font size="2">By using the Evaluate function, the query returns all the rows and columns from the Internet Sales as below:</font><br><br><br><a href="http://lh6.ggpht.com/-e31gmFreh-o/T8Qm8pyrBPI/AAAAAAAAAmc/de_XArRUua4/s1600-h/image%25255B15%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-757Qz1xTjso/T8Qm9F8rvbI/AAAAAAAAAmk/5OacEGSjKNQ/image_thumb%25255B9%25255D.png?imgmax=800" width="476" height="248"></a><br><br><font size="2">To sort the result,we could add <strong>ORDER BY</strong> at end. For example, we want to sort the result by Customer Id and product ID, we could do the following:<br></font><br><br><a href="http://lh3.ggpht.com/-btcc_tq-9Lo/T8Qm9bmRzOI/AAAAAAAAAms/_xhcrgtcxZc/s1600-h/image%25255B19%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-RQIZySxnhog/T8Qm-GduOJI/AAAAAAAAAm0/CdBwTKzRmHs/image_thumb%25255B11%25255D.png?imgmax=800" width="443" height="277"></a><br><br><font size="2"><strong>CalculateTable</strong> is a function that return a table that modified by the giving filters. It is similar to the SELECT * statement with WHERE Clause in T-SQL</font><br><font size="3"></font></p><br><br /><p><font size="3">Here is the Syntax:</font><br></p><br><br /><div id="codeSnippetWrapper" class="csharpcode-wrapper"><pre id="codeSnippet" class="csharpcode"><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 48px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet">CALCULATETABLE(<expression>,<filter1>,<filter2>,…)<br></pre><br></div><br></pre><br></div><br><br><br /><p><font size="2">This function takes expression for a table as the first parameter and any number of Boolean expressions as filter.</font><br></p><br><br /><h2><font size="2">Example:</font></h2><br><br><br /><p><font size="2">We want to return the Internal Sales records that were ordered in 2007 and the [Product Category] equals to “Bikes”.</font></p><br><br><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div><br></div><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">EVALUATE</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">CALCULATETABLE(</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> <span style="color: #006080">'Internet Sales'</span>,</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> <span style="color: #006080">'Date'</span>[Calendar <span style="color: #0000ff">Year</span>] = "2007", </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> <span style="color: #006080">'Product Category'</span>[Product Category Name] ="Bikes" </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">)</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> <span style="color: #006080">'Internet Sales'</span>[<span style="color: #0000ff">Order</span> <span style="color: #0000ff">Date</span>]</pre><!--CRLF--></div></div><br><br><br /><p><font size="2">By using the CalculateTable function, the query returns the records that match the filters provided. </font></p><br><br><br /><p><a href="http://lh6.ggpht.com/-DL75BToo5Qk/T8-opxdPTII/AAAAAAAAAnQ/lvfL2jgfDag/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-KmAZWSkgnxA/T8-orSg8ERI/AAAAAAAAAnY/uMKDMopGnN0/image_thumb%25255B2%25255D.png?imgmax=800" width="550" height="197"></a><br><font size="2"><strong>Summarize</strong> is a DAX function that returns a table for the requested totals over a set of groups. It is similar to the <strong>SELECT</strong> statement with <strong>Group By</strong> in T-SQL.<br></font></p><br><br><br /><h2><font size="2">Here is the Syntax:</font><br><br><br><br><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div><br></div><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">SUMMARIZE(<<span style="color: #0000ff">table</span>>, </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><groupBy_columnName>[, <groupBy_columnName>]…[, <name>, </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><expression>]…)</pre><!--CRLF--></div></div></h2><br><font size="2">This function takes table of data as the first parameter, any number of columns as group by parameter, the name given to the Sum of the column, and the expression<br><br></font><br><br /><h2><font size="2">Example:</font></h2><br><font size="2">We want to Sum up the total of [Internet Total Sales], total of [Internet Total Tax Amt], and total of [Internet Total Margin] FROM the [Internet Sales] table grouped by the [Calendar Year] and the [Product Category].<br><br><br></font><br><br /><div id="codeSnippetWrapper"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet">EVALUATE<br><br>SUMMARIZE('Internet Sales', <br> 'Date'[Calendar Year],'Product Category'[Product Category Name], <br> "Total Sales", 'Internet Sales'[Internet Total Sales], <br> "Total Tax Amount", 'Internet Sales'[Internet Total Tax Amt], <br> "Total Margin", 'Internet Sales'[Internet Total Margin] <br>)ORDER BY 'Date'[Calendar Year],'Product Category'[Product Category Name]<br></pre><br></div><br><font size="2">By using the Summarize function, the query returns the sum of the measures that are grouped by the columns provided. <br></font><br><br><a href="http://lh3.ggpht.com/--WSVC75qxcs/T8Qm-w1DPGI/AAAAAAAAAm8/wx9heW42dTI/s1600-h/image%25255B24%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/--YGuu7QyYgc/T8Qm_8itLxI/AAAAAAAAAnE/IzJo3Dpr0N0/image_thumb%25255B14%25255D.png?imgmax=800" width="528" height="225"></a><br><br><br><br /><h4>Reference:</h4><br><br><br /><p><a title="http://msdn.microsoft.com/en-us/library/gg492156.aspx" href="http://msdn.microsoft.com/en-us/library/gg492156.aspx"><font size="2">http://msdn.microsoft.com/en-us/library/gg492156.aspx</font></a><br></p><br><br /><p><a title="http://msdn.microsoft.com/en-us/library/ee634760(v=sql.105).aspx" href="http://msdn.microsoft.com/en-us/library/ee634760(v=sql.105).aspx"><font size="2">http://msdn.microsoft.com/en-us/library/ee634760(v=sql.105).aspx</font></a><br><br><a title="http://technet.microsoft.com/en-us/library/gg492171.aspx" href="http://technet.microsoft.com/en-us/library/gg492171.aspx"><font size="2">http://technet.microsoft.com/en-us/library/gg492171.aspx</font></a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com2tag:blogger.com,1999:blog-8585049388147973885.post-22156142637554595312012-05-29T09:00:00.000-04:002012-09-05T08:42:25.602-04:00SSIS Errors<br><font size="2">Recently I encountered some ETL errors after deploying the SSIS packages from a single SQL server environment to a two SQL servers environment.</font> <br><br> <h3>Error One:</h3><font size="2">Error 0x8000FFFF while preparing to load the package. Catastrophic failure. <br></font> <br> <h3>Problem and Resolution:</h3><font size="2">This error message is very vague, it dese not provide enough info to figure out what's going on. When I google it, the only clue is invalid disk address. Since I just pushed some new packages to a new server, I suspect the client tool calling the ETL packages may not be able to locate it for some reason. <br>After closer examining the Parent package that hosts this package, I find this package is outside the sequence container even thought it looks like in it. The step to replace the physical path for the package before this sequence container has been skipped and causes this error.</font><br><br><a href="http://lh4.ggpht.com/-lcT15F_Wz08/T7_gT6Z9AKI/AAAAAAAAAko/Owz-1k-WOsQ/image%25255B2%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; margin: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-o_mBzZXXmaA/T7_gU5xFxtI/AAAAAAAAAkw/jxIqP8eQgek/image_thumb.png?imgmax=800" width="244" height="163"></a><a href="http://lh6.ggpht.com/-z1rROkPRbuQ/T7_gVRl1CnI/AAAAAAAAAk4/DELdwZr1JqQ/image%25255B6%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-JU2_EC9D4Ow/T7_gWOAVHSI/AAAAAAAAAlA/FkoBL-9JB-s/image_thumb%25255B2%25255D.png?imgmax=800" width="244" height="171"></a><br> <h3> </h3> <h3>Error Two:</h3><font size="2">The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D025. "The partner transaction manager has disabled its support for remote/network transactions.". Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8004D025.<br>A similar error could be: <br>System.Transactions.TransactionException: The partner transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D025) ---> System.Runtime.InteropServices.COMException (0x8004D025) <br></font> <br> <h3>Problem and Resolution:</h3><font size="2">If there is a SSIS package that has the TransactionOption=”<strong>Required</strong>”, you will need to have DTC turns on.</font> <br><br><a href="http://lh6.ggpht.com/-sb04_1Dr--g/T7_gWiHk3kI/AAAAAAAAAlI/KWg2PNxlQFw/image%25255B11%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-XGTXeg94v6I/T7_gXCtT8oI/AAAAAAAAAlQ/VBEzFFJtEmQ/image_thumb%25255B5%25255D.png?imgmax=800" width="352" height="109"></a> <br><a href="http://lh3.ggpht.com/-wJ1BihxFnXk/T7_gXjaRzmI/AAAAAAAAAlY/8s1qaK_DyTA/image%25255B15%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-vhh02nF3nRg/T7_gYeV7MfI/AAAAAAAAAlg/bxSHiMsG5SE/image_thumb%25255B7%25255D.png?imgmax=800" width="344" height="133"></a> <br><br><font size="2">When the package is going to be executed on a multi SQL servers environment, you will need to enable <strong>Network DTC Access</strong> on both Servers or change the SSIS TransitionOption to not be “Required” to fix the error. <br>Here is the step to enable the Network DTC Access: <br></font> <ul> <li><font size="2">Open Component Services in Administrative Tools</font></li></ul><a href="http://lh5.ggpht.com/-1Xrw-W3Svjw/T7_gZupvjJI/AAAAAAAAAlo/SPxVCSQHnlE/image%25255B22%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-bhfDQL055n0/T7_gb-9AcdI/AAAAAAAAAlw/iB_ZO_N6loE/image_thumb%25255B10%25255D.png?imgmax=800" width="296" height="152"></a><br> <ul> <li><font size="2">Expend the Computers->Expend My Computer-> to find <strong>Local DTC</strong> </font> <li><font size="2">Right Click on the Local DTC to open the property window.</font></li></ul><a href="http://lh6.ggpht.com/-ulURRTGimMM/T7_gcVGhKoI/AAAAAAAAAl4/dzIlIjWtXRM/image%25255B25%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-8MVhGLhSO1o/T7_gdOcz8rI/AAAAAAAAAmA/ZmbBL6plmj4/image_thumb%25255B11%25255D.png?imgmax=800" width="244" height="180"></a><br> <ul> <li><font size="2">Go to <strong>Security</strong> Tab and enable the <strong>Network DTC Access.</strong> For more information on other options, See </font><a title="http://technet.microsoft.com/en-us/library/cc753510(WS.10).aspx" href="http://technet.microsoft.com/en-us/library/cc753510(WS.10).aspx"><font size="2">Enable Network DTC Access</font></a><font size="2">.</font></li></ul><a href="http://lh3.ggpht.com/-P5QgfIzaAv0/T7_gdmrpbxI/AAAAAAAAAmI/vpMx-qkKNNE/image%25255B29%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-wWZXT1fivZo/T7_geaBKEGI/AAAAAAAAAmQ/D_sDQxeowYg/image_thumb%25255B13%25255D.png?imgmax=800" width="261" height="298"></a><br> <br> <h4>Reference</h4><a title="http://technet.microsoft.com/en-us/library/cc753510(WS.10).aspx" href="http://technet.microsoft.com/en-us/library/cc753510(WS.10).aspx"><font size="2">Enable Network DTC Access</font></a> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com1tag:blogger.com,1999:blog-8585049388147973885.post-79359992559872052362012-05-23T21:52:00.001-04:002012-05-24T11:05:07.922-04:00Deployment Options for BISM Project<p> </p> <p><font size="2">There are two <strong>Query Mode</strong> options you could use when you deploy the BISM project to the AS Tabular Model server.</font></p> <ol> <ol></ol></ol> <ul> <li><font size="2">In-Memory </font></li> <li><font size="2">DirectQuery</font></li></ul><a href="http://lh4.ggpht.com/-LDz-qKlhYDE/T72Udt0oWRI/AAAAAAAAAkc/cjobKkGXrMk/s1600-h/image3.png"> <p><font size="2"><a href="http://lh4.ggpht.com/-LDz-qKlhYDE/T72Udt0oWRI/AAAAAAAAAkg/EjIqrjJy2I4/s1600-h/image%25255B1%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-dqhi_VmbgBE/T72UeLP6iRI/AAAAAAAAAkY/LFqun01WpWY/image_thumb.png?imgmax=800" width="483" height="272"></a></font></p></a> <p><font size="2">By default, data in tabular model is processed, compressed using the xVelocity in-memory analytics engine (VertiPaq). This in-memory columnar storage engine has been optimized for high performance analysis and exploration of data. It provides fast query times for aggregation queries. </font></p> <p><font size="2">However, there are some drawbacks: </font> <ul> <li><font size="2">Data is not updated when the source data changes. Model needs to be processed to refresh the data. </font> <li><font size="2">When you turn off the computer hosting the model, the cache is saved to disk and must be reopened when you load the model. </font> <li><font size="2">The save and load operations can be time-consuming. </font> <li><font size="2">Need lots memory to hold the large fact data</font></li></ul> <p><font size="2">On the other hand, DirectQuery mode uses data that is stored in a SQL Server database. It lets users retrieve data directly from a SQL Server data source in real time. This mode also lets you create models and build reports for large data sets that cannot reside in memory. </font> <p><font size="2">Here are additional benefits: </font> <ul> <li><font size="2">The data is guaranteed to be up-to-date. </font> <li><font size="2">Use the advantage of provider-side query acceleration, i.e. SQL 2012 column indexes. </font> <li><font size="2">Could use row-level security provided by the backend database. </font> <li><font size="2">Analysis Services can perform optimization to ensure the query plan against the backend database will be as efficient as possible.</font></li></ul> <p><font size="2">There are some design considerations if you are planning to use DirectQuery mode:</font></p> <ul> <ul><font size="2"></font></ul> <li><font size="2">During design phase, you may need to use Preview or Filter function to load subset data into your project. </font> <li><font size="2">MDX queries are not supported for a model in DirectQuery mode. You cannot use PPS or other clients that only issue MDX queries to consume it. </font> <li><font size="2">Currently this model only supports one data connection. You cannot query two or more SQL Servers from a DirectQuery enabled model. </font> <li><font size="2">Calculated columns and some DAX functions are not supported. You may need to use SQL View or other technique for that purpose. </font></li></ul> <p><font size="2">DirectQuery supports a hybrid deployment mode that can use either the cache or the relational source. For more information, see </font><a href="http://msdn.microsoft.com/en-us/library/hh230898.aspx"><font size="2">DirectQuery Mode (SSAS Tabular)</font></a><font size="2">. </font></p> <p> <h3>Reference </h3> <p><a title="http://msdn.microsoft.com/en-us/library/hh213006.aspx" href="http://msdn.microsoft.com/en-us/library/hh213006.aspx"><font size="2">Formula Compatibility in DirectQuery Mode</font></a> </p> <p><font size="2"><a href="http://msdn.microsoft.com/en-us/library/hh230898.aspx">DirectQuery Mode (SSAS Tabular)</a></font></p> <p><font size="2">White Paper: Using DirectQuery in the Tabular BI Semantic Model</font></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com1tag:blogger.com,1999:blog-8585049388147973885.post-47700896628772149352012-03-18T07:21:00.001-04:002012-05-24T10:39:57.713-04:00Sort a MDX Dataset by Multiple Criteria<p><font size="2">Recently I work on a SSRS report that needs a parameter to return a set of user to be selected as the filter for the report. The list of user has to be sorted by Department Code and User’s name. </font></p> <p><font size="2">In MDX, sorting a dataset based on one criterion is directly supported<br>through the Order( ) function. However, it doesn’t support multiple<br>criteria for sorting explicitly. In order to sort the dataset with multiple criteria, some tricks will be needed to complete the task. </font></p> <h3>Sample Data</h3> <p><a href="http://lh4.ggpht.com/-70Qdq6RNRx0/T3GvCVA9NLI/AAAAAAAAAjU/ldab9yhxN4Q/image7.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-5MvZeDc_q4Q/T3GvDAicO6I/AAAAAAAAAjc/FsFG7utMM2k/image_thumb3.png?imgmax=800" width="308" height="308"></a></p> <p><font size="2">Here is how we could return a user list dataset that are sorted by Department Code and User Name.</font></p> <h3>Trick One:</h3> <p><font size="2">Concatenate the two attributes into one and then use Order() function to sort it accordingly.</font> </p> <div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.77%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 208px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"> <div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">WITH</span> MEMBER [Measures].[ParameterCaption] </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">AS</span> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.MEMBER_CAPTION </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">MEMBER [Measures].[ParameterValue] </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">AS</span> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.UNIQUENAME </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">MEMBER [Measures].[Department] </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">AS</span> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.Properties( "Department Code")</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">SELECT</span> { [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[Department] } <span style="color: #0000ff">ON</span> COLUMNS , </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">{</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> <span style="color: #0000ff">ORDER</span>(</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].Children ,</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.Properties( "Department Code") + [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.MEMBER_CAPTION, </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> BASC</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">} <span style="color: #0000ff">ON</span> <span style="color: #0000ff">ROWS</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">FROM</span> [Test]</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--></div></div><br /><p><font size="2">As you could see, the result set returned has been sorted by Department first and then Username in alphabetical ascending order.</font></p><br /><p><a href="http://lh5.ggpht.com/-lSZ541ieYag/T3GvD7jAttI/AAAAAAAAAjk/Gs3BB80o_Wc/image%25255B3%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-CHTRm7t4gRA/T3GvEjnihyI/AAAAAAAAAjs/ZCUIb7gAd8A/image_thumb%25255B1%25255D.png?imgmax=800" width="430" height="237"></a><br><font size="2">You may use this trick when the sorting order of both members are in the same direction either ascending or descending.</font></p><br /><h3>Trick TWO</h3><br /><p><font size="2">When the sorting criteria are in different alphabetical order i.e. one in ascending order and the other in descending order, we will need to use two ORDER() functions.</font></p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">WITH</span> MEMBER [Measures].[ParameterCaption] </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">AS</span> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.MEMBER_CAPTION </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">MEMBER [Measures].[ParameterValue] </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">AS</span> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.UNIQUENAME </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">MEMBER [Measures].[Department] </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">AS</span> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.Properties( "Department Code")</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">SELECT</span> { [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[Department] } <span style="color: #0000ff">ON</span> COLUMNS , </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">{</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> <span style="color: #0000ff">ORDER</span>( </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> <span style="color: #0000ff">ORDER</span>(</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].Children ,</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.MEMBER_CAPTION, </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> BASC</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> ),</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> [<span style="color: #0000ff">User</span>].[<span style="color: #0000ff">User</span>].CURRENTMEMBER.Properties( "Department Code"),</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> BDESC</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">} <span style="color: #0000ff">ON</span> <span style="color: #0000ff">ROWS</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">FROM</span> [Test]</pre><!--CRLF--></div></div><br /><p><font size="2">As you could see, the result set returned now will be sorted by the Department Code in descending order first, then the Username in ascending order.</font></p><br /><p><a href="http://lh5.ggpht.com/-o85KQoRqdF8/T3GvFFA__II/AAAAAAAAAj0/r7Kr_13lTlM/image%25255B7%25255D.png?imgmax=800"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-izsgRm3Zax4/T3GvFk1LUxI/AAAAAAAAAj8/qyfBpLP4seQ/image_thumb%25255B3%25255D.png?imgmax=800" width="426" height="292"></a></p><br /><h3>Reference</h3><br /><p><a title="http://msdn.microsoft.com/en-us/library/ms145587.aspx" href="http://msdn.microsoft.com/en-us/library/ms145587.aspx">http://msdn.microsoft.com/en-us/library/ms145587.aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com2tag:blogger.com,1999:blog-8585049388147973885.post-34667632993306692812012-03-12T20:54:00.001-04:002012-03-13T19:29:48.561-04:00Display the No Row Information in the SSRS Report Footer<p>Recently there is a reader asked about how to display row count information in page footer if no data returned. Since <strong>RowNumber</strong>() function can only be used in the report body to retrieve the number of rows in the specified scope, we could not use it in either the Header or Footer area.</p> <p>We could use other counting function such as <strong>CountRows</strong>() or <strong>Count</strong>() to retrieve the number of rows retuned for the dataset. </p> <p>Here is what we can do:</p> <ul> <li>Add a textbox in the report footer</li></ul> <p><a href="http://lh4.ggpht.com/-o0MMToU4vac/T16asxeKTYI/AAAAAAAAAh8/WrS-CW7BnFM/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 20px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-_0xbJtEcykM/T16atb_G94I/AAAAAAAAAiE/biqJfccjUPk/image_thumb%25255B1%25255D.png?imgmax=800" width="319" height="71"></a></p> <ul> <li>Use CountRows() function passing in the dataset name in the expression for the textbox as: </li></ul> <p><a href="http://lh4.ggpht.com/-TRFPcpphak0/T19XhmvqAqI/AAAAAAAAAjM/IAfouxcjGc8/s1600-h/image%25255B3%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 20px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-GFXZN6kxUog/T19XijvuIuI/AAAAAAAAAjQ/J7LxEzMkveY/image_thumb%25255B1%25255D.png?imgmax=800" width="311" height="284"></a></p> <h3>Result</h3> <p>AS you could see, the report footer shows the number of row returned by the dataset now.</p> <p><a href="http://lh3.ggpht.com/-PZ85HKrQBBs/T16augGG3VI/AAAAAAAAAic/MmN2--TDcE8/s1600-h/image%25255B21%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 20px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-4sYdnGUeJ5c/T16avMrjPsI/AAAAAAAAAik/KNa1_S_ouAU/image_thumb%25255B9%25255D.png?imgmax=800" width="341" height="150"></a></p> <p><a href="http://lh6.ggpht.com/-SvJ5ofMUBbg/T16avZaKS2I/AAAAAAAAAis/cIcsdDeO2OM/s1600-h/image%25255B25%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 20px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-6xprfs5COvo/T16avnolfOI/AAAAAAAAAi0/0n5OFFq1hZM/image_thumb%25255B11%25255D.png?imgmax=800" width="351" height="105"></a></p> <h3>Reference:</h3> <p><a title="http://msdn.microsoft.com/en-us/library/dd255237(v=sql.110).aspx" href="http://msdn.microsoft.com/en-us/library/dd255237(v=sql.110).aspx">http://msdn.microsoft.com/en-us/library/dd255237(v=sql.110).aspx</a></p> <p><a title="http://msdn.microsoft.com/en-us/library/ms157163(v=sql.110).aspx" href="http://msdn.microsoft.com/en-us/library/ms157163(v=sql.110).aspx">http://msdn.microsoft.com/en-us/library/ms157163(v=sql.110).aspx</a></p> <p><a title="http://msdn.microsoft.com/en-us/library/ms156330(v=sql.100).aspx" href="http://msdn.microsoft.com/en-us/library/ms156330(v=sql.110).aspx">http://msdn.microsoft.com/en-us/library/ms156330(v=sql.110).aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com0tag:blogger.com,1999:blog-8585049388147973885.post-45165153044216045472012-03-06T21:00:00.001-05:002012-03-10T21:48:23.033-05:00Use DAX Earlier Function in Calculated Column to Mimic Group By Clause<p>It is very easy to group or slice the measures in the Pivot table by adding a attribute to the row or slicer. But, how do we create the same effect in the Calculated Column?</p> <p><a href="http://lh5.ggpht.com/-0X1U7GfgrQ0/T1ln5xoYY9I/AAAAAAAAAgk/9qDVcyksbqc/s1600-h/image%25255B4%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-fZUiOP1Siso/T1ln6qR40AI/AAAAAAAAAgs/gq5q1khLmxY/image_thumb%25255B2%25255D.png?imgmax=800" width="331" height="263"></a></p> <p>Here is the Sample Data</p> <p><a href="http://lh5.ggpht.com/-TyjuC97Gs7g/T1bBLUnV7nI/AAAAAAAAAfk/XLcTbT82fkQ/s1600-h/image2.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-E4yEy77BPiM/T1bBMCFG8CI/AAAAAAAAAfs/g9K5mCGT-Z4/image_thumb1.png?imgmax=800" width="403" height="199"></a></p> <p>If we treat this table as a SQL table, we could use the following T-SQL query to find the total queued time for each queue per date.</p> <div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 98.82%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 96px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"> <div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; border-left-style: none; padding-left: 0px; width: 101.44%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; height: 62px; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"><span style="color: #0000ff">SELECT</span> [<span style="color: #0000ff">Date</span>], [QueueName], <span style="color: #0000ff">SUM</span>([QueuedTime]) <span style="color: #0000ff">AS</span> [TotalQueuedTime]</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"><span style="color: #0000ff">FROM</span> CallDetail</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"><span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> [<span style="color: #0000ff">Date</span>], [QueueName]</pre><!--CRLF--></div></div><br /><p>In PowerPivot, we could use the EARLIER function to achieve the same effect. EARLIER is a very useful DAX function when you want to use a certain value as input to produce a calculation. </p><br /><h4>Syntax:</h4><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">EARLIER(<<span style="color: #0000ff">column</span>>, <number>) </pre><!--CRLF--></div></div><br /><h4>Solution:</h4><br /><p>Step 1: Calculate the total of the QueuedTime by using <strong>SUM(), </strong>and then use <strong>FILTER</strong>() to obtain the subset of the table for the Date and the QueueName needed.</p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">=CALCULATE(</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> <span style="color: #0000ff">SUM</span>(CallDetail[QueuedTime]), </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> FILTER(CallDetail, CallDetail[<span style="color: #0000ff">Date</span>] = ?) </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> && CallDetail[QueueName]= ?</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--></div></div><br /><p class="csharpcode-wrapper">Step 2: In the question mark area, we need to plug in a value/expression to restrict the rows been returned. </p><br /><p class="csharpcode-wrapper" align="left">If we set they to the static value as “2012-02-18” and “Queue A”, the Sum of QueuedTime will be 900 for all the rows which won’t be correct for the Queue not equal to “Queue A” or the Date not equal to “2012-02-18”.</p><br /><div class="csharpcode-wrapper"><a href="http://lh3.ggpht.com/-653Mri_pmks/T1ln7Gd9U6I/AAAAAAAAAhk/CITSRm86cy8/s1600-h/image%25255B22%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-bbcsQucR6L0/T1ln7rOctHI/AAAAAAAAAho/XFEd5T0mzrY/image_thumb%25255B12%25255D.png?imgmax=800" width="491" height="147"></a></div><br /><div class="csharpcode-wrapper">Step 3: To pass in correct expression, we could use the <strong>EARLIER</strong>() as the input for the certain value (i.e. Date or Queue name) that the calculation will be based on it. </div><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 102.3%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 134px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">=CALCULATE(</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> <span style="color: #0000ff">SUM</span>(CallDetail[QueuedTime]), </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> FILTER(CallDetail, CallDetail[<span style="color: #0000ff">Date</span>] = EARLIER(CallDetail[<span style="color: #0000ff">Date</span>])</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> && CallDetail[QueueName]= EARLIER(CallDetail[QueueName])</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--></div></div><br /><div class="csharpcode-wrapper">You can see the result now is correct based on the Date and QueueName</div><br /><div class="csharpcode-wrapper"><a href="http://lh3.ggpht.com/-was49b3xFz4/T1ln7x2W-sI/AAAAAAAAAhE/0ACJnrsKTGc/s1600-h/image%25255B17%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-XrxhASTASXU/T1ln8U_hRLI/AAAAAAAAAhM/sNrHX_HP_Cs/image_thumb%25255B9%25255D.png?imgmax=800" width="483" height="176"></a></div><br /><div class="csharpcode-wrapper">The EARLIER function is based on the current row context. If there is no row context, it will return an error.</div><br /><h4>Implementation Example:</h4><br /><p>Here is a scenario that I have encountered: Create a measure that contains the cumulated Queued Time only for the last queue. </p><br /><p><a href="http://lh5.ggpht.com/-fGDOXQbfK6M/T1bBOd-ZpUI/AAAAAAAAAgE/5Xl2U4Vj2DA/s1600-h/image6.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 10px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-BdzAaYm7VlI/T1bBOhNkxfI/AAAAAAAAAgM/Cj2jhdgeOHU/image_thumb3.png?imgmax=800" width="374" height="64"></a></p><br /><p>To solve it, I add the following condition to the pervious formula based on the LastQueue flag</p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 101.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 170px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px">=<span style="color: #0000ff">IF</span>(CallDetail[LastQueue] = 1, </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> CALCULATE(</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> <span style="color: #0000ff">SUM</span>(CallDetail[QueuedTime]), </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> FILTER(CallDetail, CallDetail[<span style="color: #0000ff">Date</span>] = EARLIER(CallDetail[<span style="color: #0000ff">Date</span>]) </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> && CallDetail[CallID]=EARLIER(CallDetail[CallID])</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: #f4f4f4; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> ,BLANK()</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; background-color: white; margin: 0em; border-left-style: none; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; border-right-style: none; font-size: 8pt; overflow: visible; padding-top: 0px"> )</pre><!--CRLF--></div></div><br /><p>This way, only the last queue will have the total queued time in the column. <a href="http://lh5.ggpht.com/-wYcY6Rj6fv4/T1ln9Kxb0hI/AAAAAAAAAhU/kh0bJWEc5M4/s1600-h/image%25255B21%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-enZ3rev6AOg/T1ln9m4ElgI/AAAAAAAAAhc/Izb90-EgaGQ/image_thumb%25255B11%25255D.png?imgmax=800" width="487" height="175"></a></p><br /><p>When summing up in the Pivot table, the total of QueueTimeHandled per day will now match the total of QueuedTime.</p><br /><p><a href="http://lh5.ggpht.com/-w5dx_Z78wsQ/T1qkjevJNOI/AAAAAAAAAhs/KpehU9y-3QA/s1600-h/image%25255B5%25255D.png"><img style="background-image: none; border-right-width: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-0KrGuyFvjXM/T1qkjwwo3fI/AAAAAAAAAh0/oR64Png-z-w/image_thumb%25255B2%25255D.png?imgmax=800" width="396" height="249"></a></p><br /><p>My colleague <a href="http://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/">Javier Guillen</a> has another nice blog entry to discuss how to use the EARLIER() in DAX measure. You may want to check it out.</p><br /><h4>Reference:</h4><br /><p><a title="http://technet.microsoft.com/en-us/library/ee634551.aspx" href="http://technet.microsoft.com/en-us/library/ee634551.aspx">http://technet.microsoft.com/en-us/library/ee634551.aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com5tag:blogger.com,1999:blog-8585049388147973885.post-35189005902720694382012-02-26T11:05:00.000-05:002012-05-24T11:32:55.442-04:00Using Page and CAML Query to Extract SharePoint List in SSIS<p> </p> <p>There are several ways to read SharePoint List as data source in SSIS. The easiest way is using the SharePoint List Adapter. You could download it from the <a title="http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652" href="http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652">Codeplex</a> site. However, you may want to have more control during extracting the list to avoid failure, to manipulate data before sent to the output rows, or you are experiencing timeout issue with lots of item in the list. </p> <p>Here is the solution I used:</p> <h3>Add For Loop Container in Task Component with a Data Flow</h3> <ul> <li>Since the SharePoint list could be large, use the for loop container to loop through the paging. </li></ul> <p><a href="http://lh4.ggpht.com/-J1WHsxGBwWo/T0bkUG8FXQI/AAAAAAAAAdY/lg3s6I4-usE/s1600-h/image3.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-wGLIxJvbtcA/T0bkUQ_mWrI/AAAAAAAAAdg/gSrYbuORdWM/image_thumb1.png?imgmax=800" width="302" height="178"></a> </p> <ul> <li>Set <strong>InitExpression</strong> to the Page number <strong>@PageCount</strong> = 1 <li>Set the <strong>EvalExpression</strong> to a Variable: <strong>@LoopNext</strong> which will be determine in the data flow. <li>Set the <strong>AssignExpression</strong> to be <strong>@PageCount</strong> = <strong>@PageCount + 1</strong></li></ul> <p><a href="http://lh5.ggpht.com/-3Phhuf64Yus/T0bkUyo_ESI/AAAAAAAAAdo/BZRx4lKTHx0/s1600-h/image43.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-UrI8YFYYG2k/T0bkVT0bvzI/AAAAAAAAAdw/EfFOCGgRnxY/image4_thumb1.png?imgmax=800" width="357" height="348"></a> </p> <h3>Create a Script Component with Source Type to Read SharePoint List</h3> <ul> <li>In the data flow, add a Script Component with Source type.</li></ul> <p><a href="http://lh5.ggpht.com/-dMUAZVIHN80/T0bkVhigeyI/AAAAAAAAAd4/3WETEdIGwLk/s1600-h/image41.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-8Fg9NseV68Y/T0bkWJ1ReAI/AAAAAAAAAeA/BjjhD2Eh7IM/image_thumb11.png?imgmax=800" width="332" height="230"></a> </p> <ul> <li>Pass in @<strong>LoopNext</strong> and @<strong>SharePointPageInfo</strong> variables as Read Write Variables. <li>Pass in @SharePointSite, @SharepointListname, @SharePoinbtView, @SharePointRowLimit variables as Read Variables. <li>Create two Outputs. One for the default SharePoint list data set, the other will hold the error data. <li>Click <strong>Edit Script…</strong> button to add .net code <li>Right click the project to add Server Reference that point to the SharePoint Site</li></ul> <p><a href="http://lh3.ggpht.com/-nuhok9Mu0lo/T0bkXfrn7gI/AAAAAAAAAeI/OAZ8FY_YS7A/s1600-h/image10.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 30px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-RVE3Q6lmCkk/T0bkX1BgfOI/AAAAAAAAAeQ/DbHdIHq4NEQ/image_thumb3.png?imgmax=800" width="244" height="228"></a> <a href="http://lh4.ggpht.com/-mmBeYEyypAE/T0bkYJMJjUI/AAAAAAAAAeY/ZIjxSYstqAM/s1600-h/image17.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-mNlpl3ZEiig/T0bkYp1CacI/AAAAAAAAAeg/W3cQBvhalCU/image_thumb6.png?imgmax=800" width="269" height="225"></a> </p> <ul> <li>In CreateNewOutputRows(), instantiate the SharePoint Web Service List object <li>Create several XMLNode objects as the parameters to call <strong>System.Xml.XmlNode GetListItems()</strong></li></ul> <div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.62%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 210px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"> <div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #008000">//Retrieve the list of available fields from the GetList call to Sharepoint.</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">Lists SPS = <span style="color: #0000ff">new</span> Lists();</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">SPS.UseDefaultCredentials = <span style="color: #0000ff">true</span>;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">SPS.Url = strSharepointURL;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #008000">//Create XML Node</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">XmlDocument xmlDoc = <span style="color: #0000ff">new</span> System.Xml.XmlDocument();</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, <span style="color: #006080">"ViewFields"</span>, <span style="color: #006080">""</span>);</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, <span style="color: #006080">"QueryOptions"</span>, <span style="color: #006080">""</span>);</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, <span style="color: #006080">"Query"</span>,<span style="color: #006080">""</span>);</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">XmlNode ndListItems = <span style="color: #0000ff">null</span>;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">ndListItems = SPS.GetListItems(strListName, strViewName, ndQuery, ndViewFields, strRowLimit, ndQueryOptions, <span style="color: #0000ff">null</span>);</pre><!--CRLF--></div></div><br /><ul><br><br /><li>Once the result gets back, iterate through the XML nodes to populate the default output data row. <br /><li>If there is an exception thrown <br /><ul><br /><li>Set the @LoopNext to false which will stop the For Loop container <br /><li>Add a row that contains the exception message to error output.</li></ul></li></ul><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">catch</span> (System.Web.Services.Protocols.SoapException ex)</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> {</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> <span style="color: #0000ff">string</span> strError = <span style="color: #006080">"Message:"</span> + ex.Message + <span style="color: #006080">"\nDetail:"</span> + ex.Detail.InnerText + <span style="color: #006080">"\nStackTrace:"</span> + ex.StackTrace;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> ErrorBuffer.AddRow();</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> ErrorBuffer.ErrorData = strError.ToString().Substring(0, strError.ToString().Length > 4000 ? 4000 : strError.ToString().Length);</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> bLoopNext = <span style="color: #0000ff">false</span>;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> }</pre><!--CRLF--></div></div><br /><ul><br><br /><li>In the data flow, sent the SharePoint list items to the destination table and the error data to error table. This way the SSIS package will not fail to execute with any SharePoint Site issue. Other process could utilize the data in the error table to recover it softly. <br /><li>In the Add Metadata component, add the @PageCount as an output column. It could be used for recovery or debug purpose.<br></li></ul><br /><p><a href="http://lh5.ggpht.com/-iCcOyaEkes0/T0pg_1Mj14I/AAAAAAAAAfI/M79H4UncdZ0/s1600-h/image5%25255B1%25255D.png"><img style="background-image: none; border-right-width: 0px; margin: 0px 0px 0px 40px; padding-left: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-lsANR3m6ZfA/T0bkZuUa8mI/AAAAAAAAAfQ/iULcWI_MZkk/image5_thumb.png?imgmax=800" width="304" height="269"></a> <br></p><br /><h3>Narrow Down the Needed Fields</h3><br><br /><p>Don’t select all the fields from the list. Sometime the list may contain binary data, meta data or image that may take long time to load. To narrow down the fields, just specify the fields in the ViewFields XML Node as:<br></p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #008000">//Get List of Field needed</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">ndViewFields.InnerXml = "<FieldRef Name=<span style="color: #006080">'ID'</span> />";</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">ndViewFields.InnerXml += "<FieldRef Name=<span style="color: #006080">'Created'</span> />";</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">ndViewFields.InnerXml += "<FieldRef Name=<span style="color: #006080">'Author'</span> />";</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">ndViewFields.InnerXml += "<FieldRef Name=<span style="color: #006080">'Editor'</span> />";</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">ndViewFields.InnerXml += "<FieldRef Name=<span style="color: #006080">'Modified'</span> />";</pre><!--CRLF--></div></div><br /><h3>Add CAML Query to Extract Only the Delta</h3><br><br /><p>To pull only the delta records in, just add filters in the query. The SharePoint list has a system field called “Modified” which will be time stamped when the record get changed. You could utilize the CAML query to get the items that have Modified Date greater than a Date value or within an arrange of date. If you don’t know the syntax of the CAML Query, you could use some SharePoint Utility such as <a href="http://www.u2u.be/res/software.aspx">U2U CAML Builder</a>.</p><br /><p>Here is a sample CAML Query that will filter the result by range of date:</p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 99.83%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; height: 140px; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><<span style="color: #0000ff">Where</span>><<span style="color: #0000ff">And</span>></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><Geq><FieldRef Name="Modified" IncludeTimeValue="<span style="color: #0000ff">TRUE</span>" /><<span style="color: #0000ff">Value</span> Type="DateTime">2012-01-01 00:00:00</<span style="color: #0000ff">Value</span>></Geq></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><Leq><FieldRef Name="Modified" IncludeTimeValue="<span style="color: #0000ff">TRUE</span>" /><<span style="color: #0000ff">Value</span> Type="DateTime">2012-01-31 23:59:00</<span style="color: #0000ff">Value</span>></Leq></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"></<span style="color: #0000ff">And</span>></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"></<span style="color: #0000ff">Where</span>></pre><!--CRLF--></div></div><br><br /><h3>Page though Large Number of List Items in the Script</h3><br><br /><p>Depend on the size of the item, you may set the row limit to 1000 while calling the <strong>GetListItems</strong>(). The function supports service-side paging. When the XML result returned, it includes a <strong>ListItemCollectionPositionNext</strong> attribute that contains the information to support the paging. You need to save it for the next call. Make sure you do not modify the string. </p><br /><p>In the example below, I set the <strong>ListItemCollectionPositionNext</strong> attribute to a local variable first and the <strong>bLoopNext</strong> to true. I then save the string to a SSIS Read/Write variable to be use for next loop.<br></p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #008000">//Get page related meta data</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px">XDocument meta = XDocument.Parse(ndListItems.InnerXml);</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">if</span> (meta.Root.Attribute("ListItemCollectionPositionNext") != <span style="color: #0000ff">null</span>)</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> {</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> strPage = meta.Root.Attribute("ListItemCollectionPositionNext").<span style="color: #0000ff">Value</span>;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> bLoopNext = <span style="color: #0000ff">true</span>;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> }</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">else</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> {</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> strPage = string.Empty;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> bLoopNext = <span style="color: #0000ff">false</span>;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> }</pre><!--CRLF--></div></div><br /><p>When next loop starts, I set the saved attribute string to the QueryOptions parameter to be used to fetch the next set data back.<br></p><br /><div style="border-bottom: silver 1px solid; text-align: left; border-left: silver 1px solid; padding-bottom: 4px; line-height: 12pt; background-color: #f4f4f4; margin: 20px 0px 10px; padding-left: 4px; width: 97.5%; padding-right: 4px; font-family: 'Courier New', courier, monospace; direction: ltr; max-height: 200px; font-size: 8pt; overflow: auto; border-top: silver 1px solid; cursor: text; border-right: silver 1px solid; padding-top: 4px" id="codeSnippetWrapper"><br /><div style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px" id="codeSnippet"><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #008000">//Set page Query option</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">if</span> (strPage.Length > 0)</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> {</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> ndQueryOptions.InnerXml = @"<Paging ListItemCollectionPositionNext=<span style="color: #006080">''</span> />";</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> ndQueryOptions.ChildNodes[0].Attributes["ListItemCollectionPositionNext"].InnerText = strPage;</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> }</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"><span style="color: #0000ff">else</span></pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> {</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> ndQueryOptions.InnerXml = "";</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: #f4f4f4; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> }</pre><!--CRLF--><pre style="border-bottom-style: none; text-align: left; padding-bottom: 0px; line-height: 12pt; border-right-style: none; background-color: white; margin: 0em; padding-left: 0px; width: 100%; padding-right: 0px; font-family: 'Courier New', courier, monospace; direction: ltr; border-top-style: none; color: black; font-size: 8pt; border-left-style: none; overflow: visible; padding-top: 0px"> </pre><!--CRLF--></div></div><br /><h2> <font size="3">Result</font></h2><br><br /><p>After executing the SSIS package, you can see the SharePoint List been extracted page by page as below: </p><br /><p><a href="http://lh6.ggpht.com/--Bj7NZlS6TQ/T0bkac1ywVI/AAAAAAAAAe4/7Bax3xSNky0/s1600-h/image18.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-f8k11_xqjbc/T0bkcMVzPiI/AAAAAAAAAfA/jG-8jaEtOdo/image_thumb9.png?imgmax=800" width="496" height="185"></a> <br><em></em></p><br /><p><em>The <strong>PageCount</strong> column stores the SharePoint List page number.</em><br></p><br /><h3>Reference:</h3><br><br /><p><a title="http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx" href="http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx">http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx</a><br></p><br /><p><a title="http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx" href="http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx">http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx</a><br></p><br /><p><a title="http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652" href="http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652">http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com3tag:blogger.com,1999:blog-8585049388147973885.post-21399440843486037892012-02-16T22:06:00.000-05:002012-02-17T08:36:11.792-05:00Tip to Change SSIS Variable Scope<p>Recently I work on a SSIS project that has a <strong>Sequence Container</strong> with lot of variables that I need for a data flow. Later on I want to change the sequence container to be a <strong>For Loop Container.</strong> It is easy to copy and paste the the data flow in the container to the other. But, how to move the SSIS variables in the visual studio? </p> <p>If you are familiar with the SSIS XML code, you may be able to copy or move the variables from one XML Node section to the other without corrupt it. For others, recreating the variable with correct value or expression could be cumbersome. </p> <p>Here is a easy way to handle it:</p> <ul> <li>Install BIDS Helper from CodePlex site. <li>Select the variable you want to copy or move</li></ul> <p><a href="http://lh6.ggpht.com/-Gp1q3Haw6pU/Tzxy20Y-fYI/AAAAAAAAAdA/p2iQiQjbwlg/s1600-h/image%25255B19%25255D.png"><img style="border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 40px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-aRLul44U9BE/Tzxy3cYYtrI/AAAAAAAAAdE/9WhxM0hEqJw/image_thumb%25255B9%25255D.png?imgmax=800" width="376" height="221"></a></p> <ul> <li>Click on the <strong>Move/Copy Variables</strong> from SSIS Variables tool bar to open a new window</li></ul> <p><a href="http://lh3.ggpht.com/-lFeFhh19laY/Tzxy4_ML_6I/AAAAAAAAAdI/RmMkFUvvYOI/s1600-h/image%25255B20%25255D.png"><img style="border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 40px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-XnwxXIi6gP0/Tzxy5Yx-M7I/AAAAAAAAAdM/HrVH44TKhQk/image_thumb%25255B10%25255D.png?imgmax=800" width="289" height="314"></a> </p> <ul> <li>Click on the DTS executable that you want the variable scope to change to , then click OK</li></ul> <p><a href="http://lh3.ggpht.com/-2uDbbplMN2U/Tzxy5uokiqI/AAAAAAAAAdQ/3x6eGNtmwPk/s1600-h/image%25255B21%25255D.png"><img style="border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 40px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-SpXfEqT7oKQ/Tzxy6EXr7wI/AAAAAAAAAdU/8BWOWSwMlyk/image_thumb%25255B11%25255D.png?imgmax=800" width="292" height="150"></a> </p> <ul> <li>The variable shows up in the SSIS Variables Window with new Scope</li></ul> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com2tag:blogger.com,1999:blog-8585049388147973885.post-56038005278301400672012-02-15T21:18:00.001-05:002012-02-15T21:18:16.981-05:00SQL Dump After Either Upgrade or Downgrade the PowerPivot<p>Recently I upgraded the PowerPivot Excel add on to the version 2 to work with new DAX. I experienced excel crush when I opened the workbook. The error message showed as:</p> <blockquote> <p><em>SQLDUMPER.EXE<br></em><em>Unable to open file <a href="file://\\?\Program">\\?\Program</a> Files\Microsoft Analysis Services\AS OLEDB\10\FlightRecorderCurrent.trac error 2</em></p></blockquote> <p>I tried to uninstall and re-install the program, but it didn’t work. I have to manually delete the directory C:\Program Files\Microsoft Analysis Services\AS OLEDB\ and repair the PowerPivot, then it works.</p> <p>Same situation happened again when I downgrade my PowerPivot back to version 1 to be compatible with my client’s environment. Once I manually remove the C:\Program Files\Microsoft Analysis Services\AS OLEDB\ directory and then repair the program, it works again.</p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com2tag:blogger.com,1999:blog-8585049388147973885.post-65743311110445332152012-01-26T20:57:00.005-05:002012-01-27T08:23:51.242-05:00Publish a BISM Tabular Model Database Connection on SharePoint 2010<h3></h3>Once you built a BISM Tabular Model project and deployed to SSAS, you may want to publish it on the SharePoint 2010. A BISM connection allows business end users to access the underline model on SharePoint with addtional security control. It could also utilize the SharePoint quick launch commands to open the Excel Workbook or Power View Report. For more information about how to create a BISM Tubular Model project, see my previous blog entry <a href="http://fendy-huang.blogspot.com/2011/09/create-ssas-bism-tabular-model-project.html">Create a SSAS BISM Tabular Model Project</a>.<br />
<h3> </h3><h3>Add the BI Semantic Model Connection Content Type to the SharePoint Library</h3><ul><li>Go to the SharePoint Library site that will host the connection file, click the <strong>Library</strong> in the<strong> Library Tools.</strong></li>
<li>Click the Library Settings</li>
<li>In the <strong>General Settings</strong> section, select the <strong>Advanced settings</strong></li>
</ul><a href="http://lh6.ggpht.com/-94aGom22XHA/TyIEWU1ed9I/AAAAAAAAAYM/gK3QfXMEhGQ/s1600-h/image%25255B64%25255D.png"><img alt="image" border="0" height="87" src="http://lh5.ggpht.com/-bhA4csgyjr8/TyIEWilc-aI/AAAAAAAAAYQ/mED4BHs9I78/image_thumb%25255B34%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 0px 40px;" title="image" width="477" /></a><br />
<ul><li>In the Content Types, Select the <strong>Allow management of content types</strong> to be<strong> Yes,</strong> then click <strong>OK</strong></li>
<li>In the <strong>Content Types</strong> section, Click on the <strong>Add from existing site content types</strong></li>
<li>Add the <strong>BI semantic Model Connection </strong></li>
</ul><a href="http://lh6.ggpht.com/-0YWNFNCOjh4/TyIEWwiUtfI/AAAAAAAAAYY/XaXUxlAL1Mk/s1600-h/image%25255B65%25255D.png"><img alt="image" border="0" height="151" src="http://lh5.ggpht.com/-xAH0p-3qG0c/TyIEXJn202I/AAAAAAAAAYg/MgaDurNPQHs/image_thumb%25255B35%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 0px 40px;" title="image" width="479" /></a> <br />
<ul><li>After click on the OK button, you can see the BI Semantic Model Connection added in the Content type table</li>
</ul><a href="http://lh3.ggpht.com/-9QAMuoNSGf4/TyIEXgEupkI/AAAAAAAAAYo/n_8eT2rMl08/s1600-h/image%25255B66%25255D.png"><img alt="image" border="0" height="91" src="http://lh6.ggpht.com/-1f9CvFdCJ9U/TyIEYLND1hI/AAAAAAAAAYw/kk_Eiru3GtA/image_thumb%25255B36%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 0px 40px;" title="image" width="488" /></a><br />
<h3> </h3><h3>Set Up Permissions</h3><ul><li>Grant the Tubular Model Analysis Service administrative permissions to the SharePoint Service Account.</li>
<li>Grant the user who is going to use the connection with the <strong>Read</strong> permission on the Tabular Model database<br />
<ul><li>Add a Role with Read permission</li>
<li>Adding the user to the Role</li>
</ul></li>
</ul><h3> </h3><h3>Create the Connection File on SharePoint</h3><ul><li>Go to the library page, click on the <strong>Documents</strong> in the <strong>Library Tools.</strong></li>
<li>Click on the down arrow on the <strong>New Document</strong> and select the <strong>BI semantic Model Connection.</strong></li>
</ul><a href="http://lh3.ggpht.com/-zgHpKRnF9qo/TyIEYXVH3VI/AAAAAAAAAY4/5Ndho3yuM48/s1600-h/image%25255B11%25255D.png"><img alt="image" border="0" height="218" src="http://lh5.ggpht.com/-R6J9L4uzrpY/TyIEYyy7mWI/AAAAAAAAAZA/7osSZKTP75w/image_thumb%25255B5%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 0px 40px;" title="image" width="244" /></a> <br />
<ul><li>On the New BI Semantic Model Connection page, specify the server name and database name and then Click OK.</li>
</ul><a href="http://lh6.ggpht.com/-M-9KVWhtj7E/TyIEZFrXiVI/AAAAAAAAAZM/7f82d-EEATc/s1600-h/image%25255B21%25255D.png"><img alt="image" border="0" height="170" src="http://lh5.ggpht.com/-4IefnhfQ71M/TyIEZjPEtsI/AAAAAAAAAZU/MGKoEUZ1Hs8/image_thumb%25255B11%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 0px 40px;" title="image" width="368" /></a> <br />
<ul><li>On the library page, you should be able to see the new connection file.</li>
</ul><h3><a href="http://lh6.ggpht.com/--utjY-Hei8c/TyIEZ5lAtvI/AAAAAAAAAZc/0a9WtudbAYs/s1600-h/image%25255B55%25255D.png"><img alt="image" border="0" height="57" src="http://lh6.ggpht.com/-MfqjxC1E2_E/TyIEaKGXoYI/AAAAAAAAAZk/rSEyD-A8pSg/image_thumb%25255B29%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 0px 40px;" title="image" width="509" /></a> </h3><br />
<h3>Use the BISM Connection in SharePoint</h3>Now you may use Excel or Power View to consume the BISM connection file as the data source. <br />
<ul><li>If you create the file in PowerPivot Gallery, you could click on either the <strong>Open New Excel Workbook</strong> or <strong>Create Power View Report</strong> link on the right upper corner to open the application. </li>
</ul><a href="http://lh3.ggpht.com/-_NMRJxeUJtY/TyIEamFWaWI/AAAAAAAAAZs/W_PmQjBQgX0/s1600-h/image%25255B30%25255D.png"><img alt="image" border="0" height="141" src="http://lh5.ggpht.com/-Usx8A2DYRN4/TyIEbgpMhPI/AAAAAAAAAZ0/LfssF_BtTck/image_thumb%25255B16%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 0px 40px;" title="image" width="489" /></a><br />
<ul><li>If you create the file in the Shared Documents page, you could click on the Down Arrow on the File Name to launch either the Excel or the Power View Report.</li>
</ul><br />
<a href="http://lh6.ggpht.com/-TgB1DEtw1uA/TyIEb3LgmtI/AAAAAAAAAZ8/1A5cUdW3ZKE/s1600-h/image%25255B63%25255D.png"><img alt="image" border="0" height="162" src="http://lh6.ggpht.com/-SlRORSMwGEc/TyIEcHgHYWI/AAAAAAAAAaE/PgCbNHpUzLs/image_thumb%25255B33%25255D.png?imgmax=800" style="border-bottom-width: 0px; border-left-width: 0px; border-right-width: 0px; border-top-width: 0px; display: inline; margin: 0px 0px 0px 40px;" title="image" width="321" /></a> <br />
<br />
<h5>Additional Reference</h5><a href="http://technet.microsoft.com/en-us/library/hh230813(SQL.110).aspx" title="http://technet.microsoft.com/en-us/library/hh230813(SQL.110).aspx">http://technet.microsoft.com/en-us/library/hh230813(SQL.110).aspx</a><br />
<a href="http://technet.microsoft.com/en-us/library/gg492136(SQL.110).aspx" title="http://technet.microsoft.com/en-us/library/gg492136(SQL.110).aspx">http://technet.microsoft.com/en-us/library/gg492136(SQL.110).aspx</a>Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com2tag:blogger.com,1999:blog-8585049388147973885.post-49553937021361012242012-01-12T16:07:00.001-05:002012-01-12T16:19:26.597-05:00SQL Server 2012 – Integration Services Catalog<p>SSIS in SQL 2012 has a new Integration Services Catalog (SSISDB) that is used for monitoring and managing SSIS projects. The Catalog will store all the integration services objects and will automatically log all the package execution activities when the SSIS project is using the new Project Deployment Model.</p> <p>In order to use the new Project Deployment Model, you need to create the Integration Services Catalog for the first time. Otherwise you will get the following error message as:</p> <blockquote> <p><em>An Integration Services catalog (SSISDB) was not found on this server instance ("LocalHost"). <br>To deploy a project to this server, you must create the SSISDB catalog. Open the Create Catalog dialog box from the Integration Services node. </em></p></blockquote> <p><a href="http://lh5.ggpht.com/-d9k9fm_x-FQ/Tw9Lg6qumCI/AAAAAAAAAXs/nVeM05kj3Nw/s1600-h/image8%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-eCGLnlayv1o/Tw9LhN27HeI/AAAAAAAAAXw/HdkmKS9qx4k/image8_thumb%25255B1%25255D.png?imgmax=800" width="455" height="117"></a> </p> <h3>Steps to Create the Catalog:</h3> <ul> <li>Open SQL Server Management Studio <li>Right click on the Integration Services node <li>Click on the Create Catalog…..</li></ul> <p><a href="http://lh4.ggpht.com/-F5bvtXsevYY/Tw9LhWtbCLI/AAAAAAAAAV4/YzPx-Axm248/s1600-h/image%25255B3%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-VDQzGH73biU/Tw9LhsYdowI/AAAAAAAAAWA/Mki01cIqNdg/image_thumb%25255B1%25255D.png?imgmax=800" width="277" height="208"></a> </p> <ul> <li>The catalog name is SSISDB. Enter the password for encryption then hit the OK button.</li></ul> <p><a href="http://lh4.ggpht.com/-t7xv1vaA7bY/Tw9Lh9qBT-I/AAAAAAAAAWI/GHy9XXzVOJA/s1600-h/image15%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-Qii0ptADRts/Tw9LiUPeitI/AAAAAAAAAWQ/2rMnHaZMsM4/image15_thumb.png?imgmax=800" width="359" height="239"></a></p> <ul> <li>The new SSISDB will be created and show in two places: <ul> <li>Database node <li>Integration Services node</li></ul></li></ul> <p><a href="http://lh3.ggpht.com/-7gr-74Nf0mY/Tw9LiqSNuPI/AAAAAAAAAWY/iFmH2rxPw1Q/s1600-h/image%25255B9%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-NhSWgG8SYOQ/Tw9LjKLFh3I/AAAAAAAAAWg/cv6M2x-UD8s/image_thumb%25255B3%25255D.png?imgmax=800" width="244" height="226"></a> </p> <ul> <li>You may view or adjust the catalog properties using the property window or Catalog.Configure_Catalog stored procedure.</li></ul> <p><a href="http://lh3.ggpht.com/-C9ladvxUTUs/Tw9LjfPmk7I/AAAAAAAAAWo/ZXr4YEsa7Mw/s1600-h/image%25255B13%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-vG6B3KaSX74/Tw9Ljn-uSgI/AAAAAAAAAWw/VYBE92uHw9c/image_thumb%25255B5%25255D.png?imgmax=800" width="341" height="290"></a> </p> <h3>Monitor the Execution Activities</h3> <p>After deploy a SSIS project, the data will be collected by the SSISDB. The build in Integration Service Dashboard could be used to monitor all the activities that executed by the SSIS packages for that project.</p> <ul> <li>Right Click on the SSISDB node to open the Reports->Standard Reports->Integration Services Dashboard.</li></ul> <p><a href="http://lh3.ggpht.com/--FRjJuK0VK4/Tw9LjxwkdtI/AAAAAAAAAX0/ueGlULBO7eY/s1600-h/image18%25255B3%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-0-UqYj4Tdxg/Tw9LkPr-yXI/AAAAAAAAAX4/si7W-Knqnx4/image18_thumb%25255B2%25255D.png?imgmax=800" width="450" height="229"></a> </p> <ul> <li>Dashboard Summary Report opened.</li></ul> <p></p> <p><a href="http://lh6.ggpht.com/-KQBvzbPjMqM/Tw9LkSH9SFI/AAAAAAAAAX8/rXvpdtjB3fA/s1600-h/image21%25255B4%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-dwtN1HNERz8/Tw9LkkR1NwI/AAAAAAAAAYA/YBxAQXPtEVo/image21_thumb%25255B3%25255D.png?imgmax=800" width="509" height="318"></a> </p> <ul> <li>There are hyperlinks on the overview report to navigate to more detail information.</li></ul> <p><a href="http://lh6.ggpht.com/-m8sbKjWoV74/Tw9Lk_d3zBI/AAAAAAAAAYE/0qWZJZOapYQ/s1600-h/image24%25255B4%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-GM3dLT-dEmk/Tw9LlM_3pYI/AAAAAAAAAYI/EJN2EpkUuIs/image24_thumb%25255B3%25255D.png?imgmax=800" width="513" height="279"></a></p> <p>There is no need to create any custom logging in the SSIS packages at all.</p> <p></p> <p></p> <p></p> <p></p> <h4>Additional Reference:</h4> <p><a title="http://msdn.microsoft.com/en-us/library/hh479588(v=SQL.110).aspx" href="http://msdn.microsoft.com/en-us/library/hh479588(v=SQL.110).aspx">http://msdn.microsoft.com/en-us/library/hh479588(v=SQL.110).aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com9tag:blogger.com,1999:blog-8585049388147973885.post-40542723122581273212011-09-28T21:11:00.001-04:002012-04-25T12:23:55.780-04:00Create a SSAS BISM Tabular Model Project<p>To create a SSAS tabular model project, you will need to install the BIDS (now called Microsoft SQL Server Data Tools in SQL 2012) and have a SQL Server Analysis Services running in tabular mode (xVelocity in-memory analytics engine (VertiPaq)). It is recommended that the AS and the BIDS are installed on the same machine.</p> <h3>Install an Analysis Services instance running in tabular mode</h3> <ul> <li>Add new feature to existed instance or add new instance</li></ul> <p><a href="http://lh3.ggpht.com/-fF3cU82Ge0w/TynhQyKG7aI/AAAAAAAAAbU/0ILYCe_YRj4/s1600-h/image%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-0xBqpjcqGz4/ToPFncjXFYI/AAAAAAAAAbc/i3AMpcmOc1E/image_thumb.png?imgmax=800" width="309" height="232"></a> </p> <ul> <li>Select the Server Mode: Tabular Mode and add yourself as the administrator</li></ul> <p><a href="http://lh5.ggpht.com/-QMdd5-IwfoY/ToPFn7rSx2I/AAAAAAAAAbg/yrlFTV-wXOM/s1600-h/image3%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-gGB5xuAt4E4/ToPFodC4x9I/AAAAAAAAAbk/99QJ9NsUDmA/image3_thumb.png?imgmax=800" width="321" height="229"></a> </p> <p>You could verify the AS Server mode using the SQL Server Management Studio and note the icon next to the server name in the Object Explore. You could also check the DeploymentMode property (0 =Traditional, 1 = PowerPivot for SharePoint, 2 = Tabular) in the msmdsrv.ini file.</p> <p><a href="http://lh3.ggpht.com/-nMcR28OH4G4/ToPFo5PIdqI/AAAAAAAAAbo/-uHlW4AyJfM/s1600-h/image14%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-LXr6c7n32AY/ToPFpDgsZ8I/AAAAAAAAAbs/cVYFS29mXTE/image14_thumb%25255B1%25255D.png?imgmax=800" width="326" height="220"></a> </p> <h3>Create New project</h3> <ul> <li>Open Visual Studio 2010 and select New project <li>There are three types of project template as shown below: <ul> <li><strong>Analysis Service Tabular Project</strong>: Use this template to create an Analysis Service project with tabular models <li><strong>Import from PowerPivot:</strong> Use this template to create a tabular project by extracting the metadata and data from an existing PowerPivot workbook <li><strong>Import from Server (Tabular</strong>): Use this template to create a tabular project by extracting the metadata from an existing tabular AS server</li></ul></li></ul> <p><a href="http://lh5.ggpht.com/-92b4JU99vRU/ToPFpjXTuAI/AAAAAAAAAbw/ln8ZSgc203c/image1%25255B1%25255D.png?imgmax=800"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-lQMVLgBdd68/ToPFp8xhdrI/AAAAAAAAAb0/mPCx6NS788k/image1_thumb.png?imgmax=800" width="358" height="278"></a> </p> <ul> <li>Once you create a project, you could import your data source by clicking on the <strong>Model</strong> on the toolbar and select the <strong>Import From Data Source</strong> to start the Table Import Wizard.</li></ul> <p><a href="http://lh3.ggpht.com/-90QuSH_alIA/ToPFqXOC2VI/AAAAAAAAAb4/KABCQa0smxU/s1600-h/image8%25255B1%25255D%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-Yh8qgsjZ-IE/ToPFq4PE_BI/AAAAAAAAAb8/AldPSAHPmEQ/image8%25255B1%25255D_thumb.png?imgmax=800" width="447" height="181"></a> </p> <ul> <li>You could either create a connection or use an existing connection to import the tables or data you need. There are lots of choices such as Relational databases, Multidimensional Source, Data Feeds, or Text Files.</li></ul> <p><a href="http://lh6.ggpht.com/-qIRp0uVPCH8/Tynhl2eJwjI/AAAAAAAAAcA/46GVwV3Sz-Y/s1600-h/image12%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-O13cycv7LKM/ToPFr5LQZEI/AAAAAAAAAcI/Hwrfy-Dtv9Q/image12_thumb.png?imgmax=800" width="320" height="251"></a> </p> <ul> <li>You could change your model view from Data View to Diagram View by either clicking on the Model View on the Model or the icons on the right low corner. </li></ul> <p><a href="http://lh4.ggpht.com/-RE-1xOYz9bU/ToPFsZnHanI/AAAAAAAAAU4/BZNTLkNgylA/s1600-h/image%25255B18%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-kr1bY3sXR10/ToPFs9M8WFI/AAAAAAAAAU8/iUJ66_v2eGw/image_thumb%25255B7%25255D.png?imgmax=800" width="244" height="211"></a> <a href="http://lh6.ggpht.com/-jl4NNiKUokw/ToPFtDVyAVI/AAAAAAAAAVA/05v_DEQbae4/s1600-h/image%25255B22%25255D.png"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh4.ggpht.com/-trgbSVWuPbU/ToPFtxEtNuI/AAAAAAAAAVE/-0I0QA1CT3w/image_thumb%25255B9%25255D.png?imgmax=800" width="219" height="211"></a> </p> <ul> <li>You could manage the relationships or create hierarchies using the Diagram View. You could create and manage measures in Data View. <li>Once you finishing modeling your project, you may deploy it to your AS Tabular model server to be used.</li></ul> <p><a href="http://lh5.ggpht.com/-0KeqztAXqGs/ToPFuCSRUjI/AAAAAAAAAVI/SyAF9egjVYg/s1600-h/image%25255B30%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-n8A1JLDrnQo/ToPFuev61MI/AAAAAAAAAVM/JSOFrjGlw_Q/image_thumb%25255B13%25255D.png?imgmax=800" width="558" height="191"></a> </p> <h3>Issue or Error</h3> <p>You may encounter the following error: Unable to connect to default workspace database server, which indicates that you have not yet set up a default workspace server.</p> <p><a href="http://lh4.ggpht.com/--ITiCJ95rbM/ToPFuigm6sI/AAAAAAAAAVQ/BJDMEohPfJ0/s1600-h/image21%25255B2%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-c64vH1YAMyc/ToPFx7VO97I/AAAAAAAAAVU/cgC2wqUQEKA/image21_thumb%25255B1%25255D.png?imgmax=800" width="370" height="161"></a> </p> <p>To fix it, you could click on the Options from the Tools menu and then select the Analysis Services option to set the default workspace server and deployment server.</p> <p><a href="http://lh3.ggpht.com/-qZonzuU4qPU/ToPFyfC-I9I/AAAAAAAAAVY/zbhEMlNIok8/s1600-h/image28%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-WFcr5aNFIlA/ToPFyqMnqlI/AAAAAAAAAVc/73p9rrzd5VM/image28_thumb.png?imgmax=800" width="305" height="178"></a></p> <p> <a href="http://lh3.ggpht.com/-z06nPXlQuAw/ToPFzKqf4tI/AAAAAAAAAVg/Ky8WVPbhfSc/s1600-h/image31%25255B1%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-HBZ6-NUAmh8/ToPFzWnl3RI/AAAAAAAAAVk/2znJC_F8iFo/image31_thumb.png?imgmax=800" width="303" height="175"></a> </p> <p>If you are interested about how to use the model once you created, you may want to check out my other post : <a href="http://fendy-huang.blogspot.com/2012/01/publish-bism-tabular-model-database.html">Publish BISM Tabular Model Database Connection on SharePoint 2010</a>.</p> <p> </p> <h3>Reference:</h3> <p><a title="http://msdn.microsoft.com/en-us/library/hh231722(v=SQL.110).aspx" href="http://msdn.microsoft.com/en-us/library/hh231722(v=SQL.110).aspx">http://msdn.microsoft.com/en-us/library/hh231722(v=SQL.110).aspx</a></p> <p><a title="http://blogs.msdn.com/b/analysisservices/archive/2011/07/13/welcome-to-tabular-projects.aspx" href="http://blogs.msdn.com/b/analysisservices/archive/2011/07/13/welcome-to-tabular-projects.aspx">http://blogs.msdn.com/b/analysisservices/archive/2011/07/13/welcome-to-tabular-projects.aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com4tag:blogger.com,1999:blog-8585049388147973885.post-83662093729849052112011-09-08T21:14:00.001-04:002011-09-10T08:13:59.039-04:00How to add SSIS Custom component to the SSIS Toolbox in Denali CTP3<p>Recently I try to convert the SQL 2008 SSIS packages to “Denali CTP3” version. Those packages are using the custom components. After recompiling the customer components to reference the SQL 11.0 objects, I need to add them into the SSIS Toolbox.</p> <p>In BIDS 2008, I could open the <strong>Choose Toolbox Items</strong> option from the <strong>Tool </strong>menu to add those customer components.</p> <p><a href="http://lh4.ggpht.com/-02FOf49rryU/TmloZMj2IlI/AAAAAAAAATY/r-6K6x2i4yo/s1600-h/image%25255B7%25255D.png"><img style="border-right-width: 0px; margin: 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-rBnUQ_rgwKw/TmloZiMZBwI/AAAAAAAAATc/sN16Gel2-2w/image_thumb%25255B3%25255D.png?imgmax=800" width="161" height="296"></a> <a href="http://lh3.ggpht.com/-TH2WEMyUA80/TmtUgnAdhsI/AAAAAAAAAT4/R8CndLxS4Qg/s1600-h/image%25255B4%25255D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh6.ggpht.com/-17eZ59wzgDM/TmtUg_vb7zI/AAAAAAAAAT8/AhIsDt8NPkw/image_thumb%25255B1%25255D.png?imgmax=800" width="272" height="196"></a> </p> <p> </p> <p>In SQL Denali CTP3, the new SSIS Toolbox does not have a "<strong>Choose Toolbox Items</strong>" option. It will automatically detect new custom components, and add them to the SSIS toolbox. If you cannot find them, you could do the following:</p> <ul> <li>Make sure you add your task/component to the appropriate SSIS folder i.e. “C:\Program Files\Microsoft SQL Server\110\DTS\PipelineComponents\” or “C:\Program Files\Microsoft SQL Server\110\DTS\Tasks\” <li>Open the SSIS Toolbox by clicking the SSIS Toolbox button that is located on the top-right corner of the package design surface.</li></ul> <p></p> <p></p> <p><a href="http://lh5.ggpht.com/-lf-Rp_1wMR8/TmlobKAbfMI/AAAAAAAAATo/mDb6nORykBg/s1600-h/image%25255B18%25255D.png"><img style="border-right-width: 0px; margin: 0px 0px 0px 40px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="image" border="0" alt="image" src="http://lh5.ggpht.com/-oW1s-z3bR6M/TmlobcMG12I/AAAAAAAAATs/ZgpjmEUSRGI/image_thumb%25255B8%25255D.png?imgmax=800" width="308" height="181"></a> </p> <ul> <li>Right click on the Toolbox and select the<strong> Refresh Toolbox</strong> option.</li></ul> <p></p> <p><a href="http://lh5.ggpht.com/-MoLWIJDGjio/TmlobvSVclI/AAAAAAAAAUA/CRCrFAhr91U/s1600-h/image15%25255B1%25255D.png"><img style="border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 40px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="http://lh3.ggpht.com/-9FHU0hNwuzk/TmlocAj1mPI/AAAAAAAAAUE/WjS1FeT_5uQ/image15_thumb.png?imgmax=800" width="258" height="215"></a> </p> <p>Reference:</p> <p><a title="http://social.technet.microsoft.com/wiki/contents/articles/ssis-toolbox-in-sql-server-denali-ctp1.aspx" href="http://social.technet.microsoft.com/wiki/contents/articles/ssis-toolbox-in-sql-server-denali-ctp1.aspx">http://social.technet.microsoft.com/wiki/contents/articles/ssis-toolbox-in-sql-server-denali-ctp1.aspx</a></p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com3tag:blogger.com,1999:blog-8585049388147973885.post-12268421238477598982011-07-10T11:38:00.001-04:002011-09-15T17:51:57.671-04:00Come Join with Mariner<p>My employer, Mariner, is currently looking for talent individuals with strong SSIS, SSRS, and SSAS experience. Mariner is based in Charlotte, North Carolina. <p><a href="http://lh5.ggpht.com/-93OosqucfrU/ThnHeUj-wmI/AAAAAAAAATQ/gm42W3rDBoE/s1600-h/MarinerLogo_png%25255B2%25255D.jpg"><img style="border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="MarinerLogo_png" border="0" alt="MarinerLogo_png" src="http://lh5.ggpht.com/-lo20VqltGMs/ThnHeyQ4wII/AAAAAAAAATU/iYJoa5GbVjs/MarinerLogo_png_thumb.jpg?imgmax=800" width="244" height="124"></a> <p>We are a company with SQL MVPs and MCPs who are talented, dedicated, and fun to work with. If you have experience developing Microsoft BI solutions and like to work in a truly collaborative team environment, then please consider Mariner. <p>The best way to inquire is through the email address on the <a href="http://www.mariner-usa.com/jobs/" target="_blank">Jobs</a> page of the <a href="http://www.mariner-usa.com" target="_blank">Mariner</a> corporate website. </p> Fendy Huanghttp://www.blogger.com/profile/18206729087423539831noreply@blogger.com2