tag:blogger.com,1999:blog-89303492356614277592024-03-18T10:48:57.271+01:00Keep It Simple and FastKeep It Simple and Fast refers to my focus during development of applications. In my opinion, applications should be very simple to use and do not have dozens of options. Every application should perform very fast, even in high transactions volume or high multi user environments.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.comBlogger209125tag:blogger.com,1999:blog-8930349235661427759.post-6413957017942149912013-10-21T11:43:00.000+02:002013-10-21T11:43:37.060+02:00Overview of the latest deadlocks on your SQL Server as of SQL 2008<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3TcEwGj6ZtypR-ptB2al3Bjmjka2Fn7QkSp3r9A346bsqYNz-J-yekgakKEvS0u5hnEVx9d3D3HXp0jngh0IptBAlp6I3JCWLm8NJdqtZj49j2y2kkYrCebJQCzL4vK3sHe0Ppf3WEqU/s1600/deadlock.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3TcEwGj6ZtypR-ptB2al3Bjmjka2Fn7QkSp3r9A346bsqYNz-J-yekgakKEvS0u5hnEVx9d3D3HXp0jngh0IptBAlp6I3JCWLm8NJdqtZj49j2y2kkYrCebJQCzL4vK3sHe0Ppf3WEqU/s320/deadlock.jpg" width="320" /></a></div>
<br />
<br />
In case you want to analyze the deadlocks which occur on your server, you can use the information from the SYSTEM_HELATH session. <br />
Use next query to retrieve the latest deadlock information. <br />
<div class="listing">
<span style="color: blue;">SELECT </span><span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: black;">event_data.value</span><span style="color: grey;">(</span><span style="color: red;">'(event/data/value)[1]'</span><span style="color: grey;">,<br /> </span><span style="color: red;">'varchar(max)'</span><span style="color: grey;">) </span><span style="color: blue;">AS XML</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">DeadlockGraph</span><span style="color: blue;">FROM </span><span style="color: grey;">( </span><span style="color: blue;">SELECT </span><span style="color: black;">XEvent.query</span><span style="color: grey;">(</span><span style="color: red;">'.'</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">event_data<br /> </span><span style="color: blue;">FROM </span><span style="color: grey;">( </span><span style="color: green;">-- Cast the target_data to XML <br /> </span><span style="color: blue;">SELECT </span><span style="color: magenta;">CAST</span><span style="color: grey;">(</span><span style="color: black;">target_data </span><span style="color: blue;">AS XML</span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">TargetData<br /> </span><span style="color: blue;">FROM </span><span style="color: green;">sys.dm_xe_session_targets</span><span style="color: black;"> st<br /> </span><span style="color: blue;">JOIN </span><span style="color: green;">sys.dm_xe_sessions<span style="color: black;"> s</span><br /> </span><span style="color: blue;">ON </span><span style="color: black;">s.</span><span style="color: blue;">address </span><span style="color: grey;">= </span><span style="color: black;">st.event_session_address<br /> </span><span style="color: blue;">WHERE </span><span style="color: black;">name </span><span style="color: grey;">= </span><span style="color: red;">'system_health'<br /> </span><span style="color: grey;">AND </span><span style="color: black;">target_name </span><span style="color: grey;">= </span><span style="color: red;">'ring_buffer'<br /> </span><span style="color: grey;">) </span><span style="color: blue;">AS </span><span style="color: black;">Data </span><span style="color: green;">-- Split out the Event Nodes <br /> </span><span style="color: black;">CROSS </span><span style="color: black;">APPLY TargetData.nodes</span><span style="color: grey;">(</span><span style="color: red;">'RingBufferTarget/<br /> event[@name="xml_deadlock_report"]'</span><span style="color: grey;">)<br /> </span><span style="color: blue;">AS </span><span style="color: black;">XEventData </span><span style="color: grey;">( </span><span style="color: black;">XEvent </span><span style="color: grey;">)<br /> ) </span><span style="color: blue;">AS </span><span style="color: black;">tab </span><span style="color: grey;">( </span><span style="color: black;">event_data </span><span style="color: grey;">)</span></div>
<div class="listing">
<span style="color: grey;"></span> </div>
<div class="listing">
<span style="color: grey;"><span style="color: black;">Be aware that, due to changes in the deadlock graph to support multi-victim deadlocks, and to minimize the size of the event data, the resulting XML cannot be saved as an XDL file for graphical representation.</span></span></div>
<div class="listing">
<span style="color: grey;"><span style="color: black;">More information about analyzing deadlocks can be found <a href="https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/" target="_blank">here</a> </span></span></div>
André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com35tag:blogger.com,1999:blog-8930349235661427759.post-84024702307614313612013-07-19T13:58:00.000+02:002013-07-19T13:58:16.577+02:00Reference parameter values in a text box without parameter fields.<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeGaa0MMxAC5o7KvT81Gu8aTEF6M42XGD8U7ZVCwvhv5Rl1Zt3yuV02ZMJw7l68_zpMnWkB3QCxYTpnXkbGFC0jQkbOlzpyc3kE6TWH1hCXO3gyosKuYA7HA9aNaVieAaHqH5LZCc0S-Q/s1600/parameters.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeGaa0MMxAC5o7KvT81Gu8aTEF6M42XGD8U7ZVCwvhv5Rl1Zt3yuV02ZMJw7l68_zpMnWkB3QCxYTpnXkbGFC0jQkbOlzpyc3kE6TWH1hCXO3gyosKuYA7HA9aNaVieAaHqH5LZCc0S-Q/s1600/parameters.jpg" /></a></div>
<br />
You can add the parameter value of a parameter to a text field in you report but after adding you get next content in the text box:<br />
<br />
[ERVCountries].[Country].&[Netherlands]<br />
<br />
You only want to have: Netherlands<br />
<br />
Solution: Change the reference expression to use .Label instead of .Value.<br />
In this example use: <br />
<br />
=Parameters!ERVCountriesCountry.Label(0)<br />
<br />
To display multiple parameter values you an use next syntax:<br />
<br />
=<span style="color: #a31515;"><span style="color: #a31515;">"My own text: "</span></span> + Join(Parameters!Country.Label,<span style="color: #a31515;"><span style="color: #a31515;">", "</span></span>)<br />
For more details about displaying multi value parameters click <a href="http://www.keepitsimpleandfast.com/2012/04/how-to-display-multi-value-parameter-in.html" target="_blank">here</a> for a more detailed blog postAndré van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com7tag:blogger.com,1999:blog-8930349235661427759.post-62144458937068542672013-07-16T16:24:00.001+02:002013-07-16T16:24:34.895+02:00The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_IQ5S8UO9VivkfDaudRbbdLxY508bZ36o6ZgU4Ak4r28QPVAB1UtwVpExmqbDHpQRvdsMFxMM5n0Y6I_JxtLf4mYdw6jniiEbNhKWi219417oMYZcYpRFQdQJTmmwcTMpurxjVRX2UiI/s1600/transactional+replication.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_IQ5S8UO9VivkfDaudRbbdLxY508bZ36o6ZgU4Ak4r28QPVAB1UtwVpExmqbDHpQRvdsMFxMM5n0Y6I_JxtLf4mYdw6jniiEbNhKWi219417oMYZcYpRFQdQJTmmwcTMpurxjVRX2UiI/s1600/transactional+replication.jpg" /></a></div>
<br />
The error message as mentioned in the title: 'The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication' , can occur when you are setting up a subscription from a SQL 2008 R2 publisher to a SQL 2012 subscriber using SQL Server Management Studio (SSMS) version of SQL 2008 R2.<br />
This error is caused by SSMS. <br />
<strong></strong><br />
<strong>Solution:</strong><br />
Use the SSMS version of SQL 2012, now you are able to setup the Subscription from a SQL2008 R2 publisher to a SQL 2012 subscription.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com3tag:blogger.com,1999:blog-8930349235661427759.post-91527293360625858852013-07-16T11:21:00.001+02:002013-07-16T11:21:48.200+02:00TSQL statement of running queriesIn a SQL production environment it can happen that you see heavy transactions. With next query you can retrieve the TSQL statement of a process you see in master..sysprocesses.<br />
<br />
<span style="font-size: x-small;"><span style="font-size: small;">SELECT Text,hostname, program_name, nt_username,* <br />FROM Master..sysprocesses <br />CROSS APPLY sys.dm_exec_sql_text (sql_handle)<br />WHERE Spid > 50</span> </span><br />
<span style="font-size: x-small;"></span><br />
You can extend the WHERE clause with SPID = XX or something else you wnat to filter on.<br />
<span style="font-size: x-small;"></span><span style="font-size: x-small;"></span><br />
<span style="font-size: x-small;"></span>André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com11tag:blogger.com,1999:blog-8930349235661427759.post-53426553366463530702013-06-02T19:00:00.000+02:002013-06-02T19:00:01.622+02:00What is the server mode of an Analysis Services instance?In SQL Analysis Service 2012 (SSAS) a new server mode is introduced, the tabular mode. This mode is available when you are installing SQL 2012 Enterprise edition or Business Intelligence edition. More details about edition features click <a href="http://msdn.microsoft.com/en-us/library/cc645993.aspx#BISemModel_tabular" target="_blank">here</a>. To deploy a tabular model, for instance a Power Pivot tabular model which you have converted to an SSAS tabular model, you need to have a SSAS server which is running in tabular model. In this blog I will describe how you can detect the mode in which your SSAS server is running. <br />
The model in which the SSAS server is running is determined during the installation of the SSAS server. The model can't be changed afterwards.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbFqntdPGv9EN3ZL0F8Q95sKFAVtNiVB6tYNNMo4aRguGEwN6pcqVvpWiuvcZ9_e677vgowed4FPqjs2s1J9SWzrCwwi5VPIaBRfGtafST5PhgYGtitekhc_dxH_bov5ydeMOuVkTx7J0/s1600/SSAS_Install.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="126" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbFqntdPGv9EN3ZL0F8Q95sKFAVtNiVB6tYNNMo4aRguGEwN6pcqVvpWiuvcZ9_e677vgowed4FPqjs2s1J9SWzrCwwi5VPIaBRfGtafST5PhgYGtitekhc_dxH_bov5ydeMOuVkTx7J0/s400/SSAS_Install.PNG" width="400" /></a></div>
<br />
To retrieve the Server mode on a running server, Start SQL Management Studio 2012. You can't use a lower version of SSMS like 2008 R2. These lower versions are not aware of this new SSAS modes.<br />
Connect to Analysis Server. Retrieve the Analysis Server properties. <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqXMLIO-vMAoG8beeEbqsSimDdDQfEMfNuOi_la2uUlEOgzWDR-1n3Na_VvPK7RSlgz3m4qyMmLdMXFkuD2yY4uuT14nyToWnQPP65h290dcq7sxGklM0BPTYinv24aj7s3sZTMBYRF8c/s1600/SSAS_Install2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="110" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqXMLIO-vMAoG8beeEbqsSimDdDQfEMfNuOi_la2uUlEOgzWDR-1n3Na_VvPK7RSlgz3m4qyMmLdMXFkuD2yY4uuT14nyToWnQPP65h290dcq7sxGklM0BPTYinv24aj7s3sZTMBYRF8c/s400/SSAS_Install2.PNG" width="400" /></a></div>
André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-10494264375662032922013-05-29T13:48:00.000+02:002013-05-29T13:48:19.579+02:00Three ways to check your Power Pivot results.<div>
Power Pivot is a good personal Business Intelligence tool. It should help you to give you better insight in what is happening in your company. The goal is to give insight, so you can take action to do it better in the future. Analyzing the data can results in 3 situations</div>
<ol>
<li>Results you expect.</li>
<li>Positive results you do not expect</li>
<li>Negative results you do not expect.</li>
</ol>
<div>
In case of situation 3: ‘Negative results you do not expect.’ people will doubt on the correctness of the report. This blog post will help you to analyze the correctness of the data in more detail. In my <a href="http://www.keepitsimpleandfast.com/2013/05/global-overview-power-pivot-basics.html" target="_blank">previous blog I explained the basics of Power Pivot</a>. Pleas read this document before using this blogpost.</div>
<div>
<br />
<span style="font-size: large;">1) Add slicers to your report to get a dataset you can understand.</span></div>
<div>
PivotTables and PivotCharts will show consolidated data. This can be a total of thousand or more records. Try to lower the number of records so you can understand the end result in the PivotTable or PivotChart. This can be done by defining some additional slicers which you can use to filter the data. For instance in a revenue report add the country, state and representative to the PivotTable. Now you can look to the revenue report for only one employee, in one country and one state. </div>
<div>
How to add slicer to the PivotChart? </div>
<div>
Select the Pivot Chart, Open tab PivotChart Tools Analyze. <br />
Press the Insert Slicer button in the ribbon. <br />
Select the country field from the debtor dataset. </div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlaSJJlnmheuAY8atykxb77A3fL10setrpqN1aTHv_k73gYM4KO4Li8WPO3mg2jQSFHtw4dcze0PgeJzrx7Hi3IeIgM5w2yLzJ7Vs9bVEewNmYLDyTGTFq-iakb1pAqYQ4YCi3Pv66Q8g/s1600/PowerPivotBlog11.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjlaSJJlnmheuAY8atykxb77A3fL10setrpqN1aTHv_k73gYM4KO4Li8WPO3mg2jQSFHtw4dcze0PgeJzrx7Hi3IeIgM5w2yLzJ7Vs9bVEewNmYLDyTGTFq-iakb1pAqYQ4YCi3Pv66Q8g/s400/PowerPivotBlog11.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
Repeat these steps for other slicers like statecode or representative.<br />
<div>
<br />
<span style="font-size: large;">2) Add filters in Power Pivot window</span></div>
<div>
The Power Pivot Window will show the import dataset records. In the Power Pivot you can filer the dataset to analyze a smaller set of data. For instance for year 2012 and period 6 </div>
<div>
Open the Power Pivot window. Open PowerPivot tab and select PowerPivot Windows button in the ribbon.</div>
<div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiItXahfsemKwXfVmX8rWj2E42Dsw-KnUrAoVkwnwI5ncOUp4SF6Obichn73MOH7zqb5eT_UBR6EgsyRePlVNyeSg3ixlJVdF9r5MCHYiii9WuyPyuUv9f_C6Z4Y-PR4K-XAFWd-i3ixvk/s1600/PowerPivotBlog1.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="93" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiItXahfsemKwXfVmX8rWj2E42Dsw-KnUrAoVkwnwI5ncOUp4SF6Obichn73MOH7zqb5eT_UBR6EgsyRePlVNyeSg3ixlJVdF9r5MCHYiii9WuyPyuUv9f_C6Z4Y-PR4K-XAFWd-i3ixvk/s400/PowerPivotBlog1.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
Select the dataset you want to filter. For instance InvoiceHistory. Select the drop down button in the column Invoice Year. Select only 2012. Press OK. </div>
<div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhn1b9yCe0AC7cD2iIVTkENufYA-89Sm2E53vLibDT1Y2XDCyuFrivsxV7RFgZe_Ayb1TDJrjiCBPPl37XkrreockpAMzJP2fZWSQAm_ys9UsGkLHDE7dXDWk_siyqRAUXKGR2fiWy0srE/s1600/PowerPivotBlog12.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="311" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhn1b9yCe0AC7cD2iIVTkENufYA-89Sm2E53vLibDT1Y2XDCyuFrivsxV7RFgZe_Ayb1TDJrjiCBPPl37XkrreockpAMzJP2fZWSQAm_ys9UsGkLHDE7dXDWk_siyqRAUXKGR2fiWy0srE/s400/PowerPivotBlog12.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
Repeat these steps for other columns in the dataset. Look if you got the records you expect.</div>
<br />
<span style="font-size: large;">3) Use query analyzer to add filters to the view.</span>The last step you can do is to analyze the dataset itself in the SQL Server Management Studio. (SSMS) . For this you need to have knowledge of building TSQL queries.<br />
First of all we need to know the query of the dataset in PowerPivot to import the data. <br />
Select the dataset tab. Select Design tab, Press Table Properties button. In the Edit table properties window switch to: Query editor.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQ1wOr8K5OrQc3LGJ39TBXnGhyphenhyphenlIrucfXu1gtWo_RuUdAQT_xLD1B56ZLJSt2k99LH-pM-m-58wpuPhQ6v8xN_SBkfN4RllPMYK-askuZzN1KRHPGdIyDGtN7uAIbEidMUnXwMgrD4l8U/s1600/PowerPivotBlog13.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="191" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQ1wOr8K5OrQc3LGJ39TBXnGhyphenhyphenlIrucfXu1gtWo_RuUdAQT_xLD1B56ZLJSt2k99LH-pM-m-58wpuPhQ6v8xN_SBkfN4RllPMYK-askuZzN1KRHPGdIyDGtN7uAIbEidMUnXwMgrD4l8U/s400/PowerPivotBlog13.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Click to enlarge</td></tr>
</tbody></table>
Select the query and copy it to your clipboard. (CTRL C)<br />
Open SSMS, Open new query windows and connect to the database. Paste the query from your clipboard to the query window. <br />
Now you can add WHERE clauses, JOIN’S with other tables etc to the query. André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com1tag:blogger.com,1999:blog-8930349235661427759.post-86943043895429050832013-05-26T21:14:00.000+02:002013-05-26T21:14:00.312+02:00Global overview Power Pivot: The basics.<strong>Introduction</strong><br />
In general Power Pivot is a datawarehouse engine within Excel. If you use it with financial data of your company, you will get more insight in what is happening in our company. You can get insight from different perspectives. With this insight you can look how you can do it better. A Power Pivot sheet will contain the following components:<br />
<ul>
<li>Datasets</li>
<li>Measures</li>
<li>Relationships between datasets</li>
<li>Time dimension dataset</li>
<li>PivotTables and PivotCharts</li>
</ul>
<div>
<strong>Datasets</strong></div>
<div>
Every Power Pivot sheet can contain one or more datasets. For instance you have a dataset for your Debtors and a dataset for your Invoices. Dataset Debtors will contain all related Debtor information like, Debtor code, Country, State, Sector etc. Useful information is data which can be used filters or group by calculations etc. For instance, country of a debtor can be used to show revenue per country. Phone number is not useful because it is unique for one customer. Revenue per phone number is the same as the revenue per debtor. Datasets are defined in the Power Pivot Window. The Power Pivot Window button is part of the Power Pivot tab of the ribbon.</div>
<div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigoVmGHe0QC8AD5IqtIXin_NT2sCcZJDTt_OqgSonSZL0yFRQilipbKeVHG_40sllEzDqMv1h56kOfvBgWYrkFbVBIEKPbv5eV_rW0i9WYUmszSOonRKq0SGCK7HYXbq7vFRpEdUk8hIg/s1600/PowerPivotBlog1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="92" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigoVmGHe0QC8AD5IqtIXin_NT2sCcZJDTt_OqgSonSZL0yFRQilipbKeVHG_40sllEzDqMv1h56kOfvBgWYrkFbVBIEKPbv5eV_rW0i9WYUmszSOonRKq0SGCK7HYXbq7vFRpEdUk8hIg/s400/PowerPivotBlog1.png" width="400" /></a></div>
<div>
</div>
<div>
Example of the dataset debtors:</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinyyboym48S8zGUrPzb1sAnoofIIlkohD10cNsaedhYheWv0klIKA9Z7TD1TebMPyRPcP00QT_eQvP6MxQpXhumSf0jih2H8lQK_bAZr_VmoHpEZGQNNBw1gRZVFzFfmVt5Ui6dN6KYLI/s1600/PowerPivotBlog2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="212" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEinyyboym48S8zGUrPzb1sAnoofIIlkohD10cNsaedhYheWv0klIKA9Z7TD1TebMPyRPcP00QT_eQvP6MxQpXhumSf0jih2H8lQK_bAZr_VmoHpEZGQNNBw1gRZVFzFfmVt5Ui6dN6KYLI/s640/PowerPivotBlog2.png" width="640" /></a></div>
<br />
Example of the dataset Invoices: <br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqWMo7t1dyRXC1tMRTpOZoWBYU-K4K1xOiBjBrCoI63F2T0USCwkZlBzZPvqoLsFZOJymCye1_UAE6T3FPT6b9E2tIgII2rD4cuuoPh1kKPGjE-dUqB_n7XD4jJCnXaHSCp1ocVv-Zv5A/s1600/PowerPivotBlog3.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="123" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqWMo7t1dyRXC1tMRTpOZoWBYU-K4K1xOiBjBrCoI63F2T0USCwkZlBzZPvqoLsFZOJymCye1_UAE6T3FPT6b9E2tIgII2rD4cuuoPh1kKPGjE-dUqB_n7XD4jJCnXaHSCp1ocVv-Zv5A/s400/PowerPivotBlog3.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Dataset Invoices: Click to enlarge</td></tr>
</tbody></table>
<br />
<strong>Measures</strong><br />In every dataset you can define measures. A measure is a formula that is created specifically for numeric data that you want to summarize or analyze in a PivotTable or PivotChart. Measures can be based on standard aggregation functions, such as COUNT, AVERAGE or SUM, or you can define your own formula by using DAX. <br />Example of measures on the Invoices dataset, like Invoices, Invoices per Customer, Invoiced customers, Average Invoice amount etc. <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxlxjbV7DS5IIuORjxWHl_e7kfIEjYuDYaAiIJWnHhnJK_W8FxWmXBj1UufrGFrcFCBw4YpAUK2i4r01eniYMjB_gJhnGBqlyCohDFq2HE-7IDgZkWF3uli4sVLJo02C88pDKc4u2gsIE/s1600/PowerPivotBlog4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="396" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgxlxjbV7DS5IIuORjxWHl_e7kfIEjYuDYaAiIJWnHhnJK_W8FxWmXBj1UufrGFrcFCBw4YpAUK2i4r01eniYMjB_gJhnGBqlyCohDFq2HE-7IDgZkWF3uli4sVLJo02C88pDKc4u2gsIE/s640/PowerPivotBlog4.png" width="640" /></a></div>
<br />Defined measures will be available in the Power Pivot field list and can be recognized by the calculator symbol.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBLhVHUv_bP1VvbHzJS3og_hIhgJvunaOtawh9Y975lTdK5u3MbZm0ennaPmBLXzjEqzWBY0rFddXzDw8pcH4jZmH2feMvTfXvOb5mEuoS9BqH-UwxBYVkKFjHbsMm0Qcs_Gi1xqjrf80/s1600/PowerPivotBlog5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBLhVHUv_bP1VvbHzJS3og_hIhgJvunaOtawh9Y975lTdK5u3MbZm0ennaPmBLXzjEqzWBY0rFddXzDw8pcH4jZmH2feMvTfXvOb5mEuoS9BqH-UwxBYVkKFjHbsMm0Qcs_Gi1xqjrf80/s1600/PowerPivotBlog5.png" /></a></div>
<br />
<strong>Relationships between datasets </strong>Datasets can be linked to each other by creating a relationship between the tables. The relationship establishes how the data in the two tables should be correlated. For example, a Debtor table and an Invoice table can be related in order to show the customer name that is associated with each order. After you have defined a relationship between tables in the Power Pivot window, you will be able to filter data by using related columns, lookup values in related tables, and integrate columns from multiple tables in a PivotTable. To understand with related columns can be used, you need to look to the defined relations. Open the Power Pivot window and press on the Diagram View button in the ribbon.<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXAu9mf3_Usd1nFLQe-UU-YaGWz0gsAF5upIARZP3PR9ImjwDfBEuiZ4sVLfkxQ35j9jGpld0f9UE3tUI1_oyQfUOKauCSFcDUAkQz7aPFEfqI0EOPe-9F082yxDabsuCfLgt3z-Fel0c/s1600/PowerPivotBlog6.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="330" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXAu9mf3_Usd1nFLQe-UU-YaGWz0gsAF5upIARZP3PR9ImjwDfBEuiZ4sVLfkxQ35j9jGpld0f9UE3tUI1_oyQfUOKauCSFcDUAkQz7aPFEfqI0EOPe-9F082yxDabsuCfLgt3z-Fel0c/s400/PowerPivotBlog6.png" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Diagram View: Click to enlarge</td></tr>
</tbody></table>
<strong>Time dimension dataset</strong><br />To make use of the powerful Time intelligent functions it is recommended to create a separate related time table. To create such a table read: <a href="http://www.keepitsimpleandfast.com/2012/09/script-to-generate-time-dimension-table.html">http://www.keepitsimpleandfast.com/2012/09/script-to-generate-time-dimension-table.html</a> The TimeDimension table contains characteristics of all days. For instance the day 24 may 2013. Some examples of the characteristics of this day: Year: 2013, Month: 5, It’s a Friday, Quarter 2, ISO week number: 21 All these different characters can be used in your pivots. Link your time dimension table to the date columns on your other datasets. For instance link Invoice date of your Invoice table to the date column. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig1sxL0d8mntKUvSo6T-WgQtWgMj63ZWP_Bux04NcnwdR2yvuyQ5OiEynhAihZcuhVRFlTFVp3shPweQh7IWMwLNVVLSWdwR9_GepkooHop-d6BZU4UxiUcaZOBD9f1HbQDWSfYA-P5bE/s1600/PowerPivotBlog7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig1sxL0d8mntKUvSo6T-WgQtWgMj63ZWP_Bux04NcnwdR2yvuyQ5OiEynhAihZcuhVRFlTFVp3shPweQh7IWMwLNVVLSWdwR9_GepkooHop-d6BZU4UxiUcaZOBD9f1HbQDWSfYA-P5bE/s320/PowerPivotBlog7.png" width="199" /></a></div>
This enables you to build revenue reports per week, month etc. <br />
<strong>PivotTables and PivotCharts</strong>After having defined datasets, relationships, a date time table and measures, we can start building the reports. Select the Power Pivot Tab in the ribbon, Select PivotTable and select what you want to insert.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8Qkk4jjgcTpZvgOdnVFPCOh_dX1tLByKgS_UqGQ4c3tnvRaOxhYOIBAPtnv5euH_4MM9aC_oVm4MNEvQSdiQmwpMDEtyPIGDbfRgwKBI2iU_g6nWNwk_LogVczGTO8n3ePWDmnrd2ZVE/s1600/PowerPivotBlog8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="195" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8Qkk4jjgcTpZvgOdnVFPCOh_dX1tLByKgS_UqGQ4c3tnvRaOxhYOIBAPtnv5euH_4MM9aC_oVm4MNEvQSdiQmwpMDEtyPIGDbfRgwKBI2iU_g6nWNwk_LogVczGTO8n3ePWDmnrd2ZVE/s640/PowerPivotBlog8.png" width="640" /></a></div>
<br />
You will get the Power Pivot field list which you need to use to define your chart. <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqYtSSnHhBq-To6ucgRhcalVOgHxJ_oUtv_lBX9VsuYd5UzR4vJQYLdpg43rVjIP6UwXpEJvAhCqTRTlv1n0JTmVI7hEAg6wVtJ7rExJ9H69Kmp3f0KrurINZtjIwsinV4rccU_DcbyIM/s1600/PowerPivotBlog9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqYtSSnHhBq-To6ucgRhcalVOgHxJ_oUtv_lBX9VsuYd5UzR4vJQYLdpg43rVjIP6UwXpEJvAhCqTRTlv1n0JTmVI7hEAg6wVtJ7rExJ9H69Kmp3f0KrurINZtjIwsinV4rccU_DcbyIM/s640/PowerPivotBlog9.png" width="512" /></a></div>
<br />
This will result in next chart: <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEip9xciSaHSxCSf0iHDVRvrlwCBRNO_MhFXVrnDbL9-kuHJc0HNq53DLD_2swn1B06isgTYC98OIgPqMGqvzW54J8Y2r19W7RwbrnxK7tKnK6A6nVFRIzdH-b8wB6YXMBS0cTKx_pVjtUY/s1600/PowerPivotBlog10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="281" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEip9xciSaHSxCSf0iHDVRvrlwCBRNO_MhFXVrnDbL9-kuHJc0HNq53DLD_2swn1B06isgTYC98OIgPqMGqvzW54J8Y2r19W7RwbrnxK7tKnK6A6nVFRIzdH-b8wB6YXMBS0cTKx_pVjtUY/s640/PowerPivotBlog10.png" width="640" /></a></div>
<br />
Add a second chart or pivot to the same sheet. If you change slicer values of the previous chart, the added pivot or chart does not change. You need to link the added chart or pivot to the slicer. To do these select the added chart or pivot. Select the Pivot Table Tools, Options tab. Select Insert Slicer, Slicer Connections. You will get a list of all slicers. Select the slicer of the current sheet. The slicer is now active on both charts\pivots. You can check this by changing the slicer values. André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-23829064527207301592013-05-24T12:52:00.000+02:002013-05-28T09:06:16.499+02:00How to retrieve IO statistics of SQL databases on file level?<div align="left" class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghAARDnn0wEBLRfrzqGv5xOF01hW4nNxjbe_7inVfYSp8MqB3NAJi8LSAbMZ30YDnrVIEUv-kgnx45zKrhec96ES9wKDwwcmX8HY-PFbmYVBDanpqTQaAHzWMxwMVaBOcybPxEbAGE_xA/s1600/DiskIO3.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEghAARDnn0wEBLRfrzqGv5xOF01hW4nNxjbe_7inVfYSp8MqB3NAJi8LSAbMZ30YDnrVIEUv-kgnx45zKrhec96ES9wKDwwcmX8HY-PFbmYVBDanpqTQaAHzWMxwMVaBOcybPxEbAGE_xA/s320/DiskIO3.jpg" width="240" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
</div>
<div class="separator" style="clear: both; text-align: left;">
Performance of a SQL database depends on different factors. One of these factors is disk activity, also known as Disk IO. With Windows Performance monitor (Perfmon) you can measure the performance of your disk. However if you have 4 database files on 1 drive, you do not know which of your databases is causing the most Disk IO. Within SQL Server you can use a dynamic view which will give you information on database file level. Execute next statement on the SQL Server: </div>
<br />
SELECT d.name ,s.filename, NumberReads, NumberWrites, BytesRead,BytesWritten, <br /> IoStallReadMS, IoStallWriteMS, IoStallMS,BytesOnDisk<br />FROM Fn_Virtualfilestats(NULL,NULL) f<br />INNER JOIN sys.sysaltfiles s ON f.dbid = s.dbid and f.FileId = s.fileid<br />INNER JOIN sys.databases d ON f.DbId = d.database_id<br />ORDER BY IoStallReadMS DESC<br />
<br />
This query will show next columns:<br />
<br />
<strong>Name</strong>: Database name<br />
<strong>Filename</strong>: Filename of the database file. Look to the extension to see if it is the MDF or LDF file<br />
<strong>Timestamp</strong>: Database timestamp at which time the data was taken<br />
<strong>Number of reads</strong>: Number of reads issued on the file<br />
<strong>BytesRead</strong>: Number of bytes read issued on the file<br />
<strong>IoStallReadMS</strong>: Total amount of time, in milliseconds, that users waited for the read IOs to complete the file<br />
<strong>Number of writes</strong>: Number of writes issued on the file<br />
<strong>BytesWritten</strong>: Number of bytes written issued on the file<br />
<strong>IoStallWriteMS</strong>: Total amount of time, in milliseconds, that users waited for the read IOs to complete the file<br />
<strong>BytesOnDisk</strong>: Physical file size(count of bytes) on disk.<br />
<br />
<br />
With this query, you can look which databases are generating the most IO and time database files are waiting on the disk to get the required data. This can help you to decide to move some database files to seperate disks.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com3tag:blogger.com,1999:blog-8930349235661427759.post-53664921122120493062013-05-10T10:17:00.000+02:002013-05-10T10:17:14.938+02:00The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_NjlaYiY3Rv5d53iECOrVPUk761Ctlb-lFihmR1w9RGhyunzXS_kvNZw-WPYO9YYxQwMukYyaqGI5yV__z9G5ig9tvDEd8AaAwJNrDnpH1EdPwCnId2mRYP68p0gkyLSlMzXE_LQcQrA/s1600/access.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_NjlaYiY3Rv5d53iECOrVPUk761Ctlb-lFihmR1w9RGhyunzXS_kvNZw-WPYO9YYxQwMukYyaqGI5yV__z9G5ig9tvDEd8AaAwJNrDnpH1EdPwCnId2mRYP68p0gkyLSlMzXE_LQcQrA/s1600/access.jpg" /></a></div>
You are a part of the local administrators group on the server. You want to add a user to the Server administrator group of your Analysis Server. After adding the user you will get next error message: The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties. <br />
<br />
Solution: Start SQL Server Management Studio with the option 'Run as Administrator'. Now it is possible to add users to the Server administrator group of your Analysis Server.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com2tag:blogger.com,1999:blog-8930349235661427759.post-70862044001535683742013-04-10T17:29:00.001+02:002013-04-11T11:34:53.608+02:00The sqlncli10 provider is not registered on the local machine. Failed to connect to the server.<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiR3IVBoHk1pQoc0K2ui3_D_t-ISEtMf7eo6x3p3O-G__hd5OeCjGVP6vFstIS4lG6ayj-I3dBnqBjt9yxONyKpQDY0zo1nHfGfn8eFvqGWLvykkJLB0hvC9Ueo73xvDkPkm_hDAM_bP7M/s1600/failedSQLNCLI10.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="97" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiR3IVBoHk1pQoc0K2ui3_D_t-ISEtMf7eo6x3p3O-G__hd5OeCjGVP6vFstIS4lG6ayj-I3dBnqBjt9yxONyKpQDY0zo1nHfGfn8eFvqGWLvykkJLB0hvC9Ueo73xvDkPkm_hDAM_bP7M/s400/failedSQLNCLI10.png" width="400" /></a></div>
<br />
When you <a href="http://www.keepitsimpleandfast.com/2011/04/how-to-change-database-for-your-power.html" target="_blank">configure the data source</a> of your Office 2010 Excel Power Pivot sheet, you can get the message: <br />
<strong>Failed to connect to the server. Reason: The sqlncli10 provider is not registered on the local machine. </strong><br />
<br />
This can happen if you want to connect to a SQL 2008 (R2) server. On the client you need to have installed the Microsoft SQL Server Native Client (SQL Server Native Client). This is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver.<br />
<br />
<strong>Solution:</strong> Install the sqlncli10 provider on your machine.<br />
<br />
The sqlncli10 provider for SQL 2008 R2 can be downloaded from here.<br />
<br />
<a href="http://go.microsoft.com/fwlink/?LinkID=188400&clcid=0x409" target="_blank">X86 package</a>.<br />
<a href="http://go.microsoft.com/fwlink/?LinkID=188401&clcid=0x409" target="_blank">X64 package</a><br />
<a href="http://go.microsoft.com/fwlink/?LinkID=188402&clcid=0x409" target="_blank">IA64 package</a>. <br />
<br />
<br />
In case you have a SQL 2012 server and have received a Power Pivot sheet which want to use the SQLNCLI10 provider, you can change the data provider to use to data provider for SQL 2012: SQLNCLI11<br />
<br />
<ol>
<li>Press the Existing connection button in the Power Pivot Window</li>
<li>Select the PowerPivot Data Connection</li>
<li>Press Edit</li>
<li>Specify the SQL server name</li>
<li>Specify the Database name</li>
<li>Press the advanced</li>
<li>Select Provider: SQL Server Native Client 11.0 </li>
</ol>
In case you can't selected the SQL Server Native Client 11.0. The provider is not installed on your client. The SQLNCLI11 provider for SQL 2012 can be downloaded from here:<br />
<a href="http://go.microsoft.com/fwlink/?LinkID=239647&clcid=0x409" target="_blank">X86 package</a><br />
<a href="http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409" target="_blank">X64 package</a><br />
<br />
After installation it should be possible to configure your datasource, to be able to update your Excel Power Pivot sheet with all data.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com7tag:blogger.com,1999:blog-8930349235661427759.post-68614273326947908772013-04-05T15:34:00.001+02:002013-04-05T15:34:28.409+02:00Tsql script to see creation anf modification time of all indexes in database.Next script will show the modification date of all indexes in the selected database.<br />
<br />
Select s.name, t.name, t.create_date, t.modify_date,i.name, c.name<br />From sys.tables t<br />inner join sys.schemas s on t.schema_id = s.schema_id<br />inner join sys.indexes i on i.object_id = t.object_id<br />inner join sys.index_columns ic on ic.object_id = t.object_id<br />inner join sys.columns c on c.object_id = t.object_id and<br /> ic.column_id = c.column_id<br />Where i.index_id > 0 <br />and i.type in (1, 2) -- clustered & nonclustered only<br />and i.is_primary_key = 0 -- do not include PK indexes<br />and i.is_unique_constraint = 0 -- do not include UQ<br />and i.is_disabled = 0<br />and i.is_hypothetical = 0<br />and ic.key_ordinal > 0<br />Order by 4 descAndré van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com2tag:blogger.com,1999:blog-8930349235661427759.post-54375363072388590562013-02-28T15:42:00.000+01:002013-03-03T12:26:19.607+01:00Performance tips for your Power Pivot sheet<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUODk25Fe7YzevMVwYrF05zh5g_PPeXlKeQfVNuCnBrqNK9YkjnCq8G58N5P_QBQAJ11PUr0N4RLRDIwC8RXMlJXxlo7sjuT6ZUFqBzwV6hh7hu3yaUqTHTZw4OTXiCUCrr9glHOMlHbc/s1600/powerpivot.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUODk25Fe7YzevMVwYrF05zh5g_PPeXlKeQfVNuCnBrqNK9YkjnCq8G58N5P_QBQAJ11PUr0N4RLRDIwC8RXMlJXxlo7sjuT6ZUFqBzwV6hh7hu3yaUqTHTZw4OTXiCUCrr9glHOMlHbc/s200/powerpivot.png" width="186" /></a></div>
<br />
Power Pivot is a really good personal Business Intelligence tool with a great performance. However, for every tool there are tips to optimize the performance. In Power Pivot you need to define the BISM. (Business Intelligence Semantic model), please take next tips into consideration during the design of your BISM model:<br />
<br />
<ul>
<li>Use views to import data in Power Pivot. The view will contain the business logic of how the data is stored in your database. If changes are made to your business logic, you only need to change the views. The Power Pivot sheet will still work.</li>
<li>Use logical columns names in the views. For instance [Account code] in stead of debnr. Everybody should understand what kind of content is stored in each column.</li>
<li>Import only columns you really need. Avoid SELECT * FROM MyView1 As described in my previous blog post: <a href="http://www.keepitsimpleandfast.com/2013/02/memory-management-in-power-pivot-column.html" target="_blank">Memory management in Power Pivot</a>, all data is kept in memory. Every column which is not used will use memory which can not be used for other purposes.</li>
<li>Import columns which are useful for analytics purposes. For instance for customer data: Account code, Country, State. Columns like street name are not so useful. As described <a href="http://www.keepitsimpleandfast.com/2013/02/memory-management-in-power-pivot-column.html" target="_blank">here</a>, it will create a lot of distinct values in your dictionary for this column. This will have a negative impact on performance.</li>
<li>Import DateTime columns in 2 separate columns. One Date column and one Time column. If time portion is not useful for your analytics do not import it at all.</li>
<li>Import master data in separate tabs. For instance all item attributes in one tab and use the item key in all transactional tabs. Link the item key from the transactional tab to the item key of the Item master tab.</li>
<li>Reduce the number of rows to import. If you analyse on month level, group all data in the view to the level you want. For instance group by Date, Item, Amount. This will save a lot of rows to import. Of course, this is not possible sometimes because you do not want to loose the granularity of analysis.</li>
<li>Reduce the number of rows to import by selecting only the subset you are going the analyze. For instance your database contains financial transaction as of financial year 2008. If you need to analyze of the current and previous year, import only the last 2 years.</li>
<li>Optimize column data types. A column with few distinct values will be lighter than a column with a high number of distinct values. This is important also for measures, which are considered also possible quantitative attributes. If the measure you are storing is a float and is the result of a calculation, consider reducing the number of digits to be imported. This will reduce the size of the dictionary, and possibly also the number of distinct values.</li>
<li>Avoid high-cardinality columns. Columns with unique ID's like invoice numbers are very expensive. Sometimes you can skip this columns and use the COUNTROWS function instead of the DISTINCTCOUNT.</li>
<li>Use measures instead of calculated columns if possible. Calculated columns are stored as an imported column. This does not apply to calculated measures. A calculated measure is calculated at query time. </li>
<li>In case you need to store a measure in a calculated column, consider to reduce the number of digits of the calculation.</li>
<li>Normalizing data doesn’t have a big effect on the size of the resulting database. However, it might have a strong impact on both processing time and memory required to process data. The key is to find a right balance. A Star schema is in most situation the right balance.</li>
</ul>
Enjoy it, to make your Power Pivot sheets even more powerful.<br />
<br />André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-31738012300715257812013-02-27T13:29:00.000+01:002013-02-27T13:29:33.063+01:00Memory management in Power Pivot: Column oriented databases.<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-7X1-yaCt5WocdrPj5FpQBTXrQKDzCedafLsOsDorj5kFXdgXIB8v4zB6j69I3u6-oaLVQF-AemCSPhn2W5UDj22RXm68EdPg3Rr-fVOvQ4S84D8ftqJygy3KwPkeUjmDu9ca13Aik7M/s1600/PowerPivot_RevenueDashboard.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="243" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-7X1-yaCt5WocdrPj5FpQBTXrQKDzCedafLsOsDorj5kFXdgXIB8v4zB6j69I3u6-oaLVQF-AemCSPhn2W5UDj22RXm68EdPg3Rr-fVOvQ4S84D8ftqJygy3KwPkeUjmDu9ca13Aik7M/s320/PowerPivot_RevenueDashboard.PNG" width="320" /></a></div>
<br />
Power Pivot is a perfect personal Business Intelligence tool. It is simple to use and the performance of the Power Pivot engine is really great. To better understand this engine, so you can even better make use of it, I will explain how this engine is working.<br />
<br />
<strong>Row oriented versus column oriented databases.</strong><br />
<br />
All traditional relational databases, including SQL Server, are row oriented databases. They store data in tables row by row. The row of a table is the main unit of storage. Indexes are used to point to all columns of a certain row. It depends on the definition of the index which records belongs to this index.<br />
<br />
A column-oriented database, like Power Pivot, uses a different approach. Every column is considered as a separate entity. Data is stored for every column in a separate way. I will explain this with an example.<br />
<o:p>
<o:p> </o:p>
</o:p><br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: currentColor; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="background-color: transparent; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.4pt;" valign="top" width="79"><strong>ID<o:p></o:p></strong><br />
<strong>
</strong></td><strong>
</strong><td style="background-color: transparent; border-color: windowtext windowtext windowtext rgb(0, 0, 0); border-style: solid solid solid none; border-width: 1pt 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108"><strong>
</strong><strong>Car<o:p></o:p></strong><br />
<strong>
</strong></td><strong>
</strong><td style="background-color: transparent; border-color: windowtext windowtext windowtext rgb(0, 0, 0); border-style: solid solid solid none; border-width: 1pt 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.25in;" valign="top" width="120"><strong>
</strong><strong>Engine<o:p></o:p></strong><br />
<strong>
</strong></td><strong>
</strong><td style="background-color: transparent; border-color: windowtext windowtext windowtext rgb(0, 0, 0); border-style: solid solid solid none; border-width: 1pt 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108"><strong>
</strong><strong>Color<o:p></o:p></strong><br />
<strong>
</strong></td><strong>
</strong></tr>
<strong>
</strong>
<tr style="mso-yfti-irow: 1;"><strong>
</strong><td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.4pt;" valign="top" width="79"><strong>
</strong>1<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Audi A4<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.25in;" valign="top" width="120">Petrol<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Silver<o:p></o:p></td>
</tr>
<tr style="mso-yfti-irow: 2;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.4pt;" valign="top" width="79">2<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Audi A4<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.25in;" valign="top" width="120">Gazole<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Red<o:p></o:p></td>
</tr>
<tr style="mso-yfti-irow: 3;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.4pt;" valign="top" width="79">3<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Audi A4<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.25in;" valign="top" width="120">Gazole<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Blue<o:p></o:p></td>
</tr>
<tr style="mso-yfti-irow: 4;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.4pt;" valign="top" width="79">4<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">BMW<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.25in;" valign="top" width="120">Petrol<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Silver<o:p></o:p></td>
</tr>
<tr style="mso-yfti-irow: 5;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.4pt;" valign="top" width="79">5<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">BMW<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.25in;" valign="top" width="120">Gazole<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Silver<o:p></o:p></td>
</tr>
<tr style="mso-yfti-irow: 6;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.4pt;" valign="top" width="79">6<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">BMW<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.25in;" valign="top" width="120">Gazole<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Red<o:p></o:p></td>
</tr>
<tr style="mso-yfti-irow: 7; mso-yfti-lastrow: yes;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 59.4pt;" valign="top" width="79">7<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Mercedes<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 1.25in;" valign="top" width="120">Gazole<o:p></o:p></td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 81pt;" valign="top" width="108">Blue<o:p></o:p></td>
</tr>
</tbody></table>
<br />
<span style="mso-spacerun: yes;"> Every column will have it's own sorted dictionary with all distinct values and a bitmap index references the actual values of each item in the column by using a zero-based index to the dictionary. Next table will show the dictionary values and index values.</span><br />
<span style="mso-spacerun: yes;"></span><span style="mso-spacerun: yes;"> </span><br />
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: currentColor; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0in 5.4pt 0in 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="background-color: transparent; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Column<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: windowtext windowtext windowtext rgb(0, 0, 0); border-style: solid solid solid none; border-width: 1pt 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Dictionary<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: windowtext windowtext windowtext rgb(0, 0, 0); border-style: solid solid solid none; border-width: 1pt 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.4pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Values<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 1;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">ID<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">32,23,10,43,57,65,71<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.4pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">2,1,0,3,4,5,6<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 2;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Car<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Audi,BMW,Mercedes<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.4pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">0,0,0,1,1,1,2<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 3;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Engine<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Petrol, Gazole<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.4pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">0,1,1,0,1,1,1<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 4; mso-yfti-lastrow: yes;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Color<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.35pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">Silver, Red, Blue<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0in 5.4pt; width: 160.4pt;" valign="top" width="214"><div class="MsoNormal" style="line-height: normal; margin: 0in 0in 0pt;">
<span style="font-family: Calibri;">0,1,2,0,0,1,2<o:p></o:p></span></div>
</td>
</tr>
</tbody></table>
<br />
As you can see, the dictionary can be the most expansive part of the index. Especially if a high number of distinct values exists in a column. The lower the number of distinct values in a column the smaller the size of dictionary for this column. This will make the value bitmap index more efficient.<br />
<br />
The xVelocity engine, which is implemented on Power Pivot, is an in-memory database. This means that it has been designed and optimized assuming that the whole database is loaded in memory. Data is compressed in memory and dynamically uncompressed during each query. Because all data is kept in memory it is essential to be critical which data to import in your Power Pivot sheet. For instance customer data can be useful like, country, state. However street name is not efficient. Every customer will have a unique address which will result in a big dictionary without a low number of distinct values. It will have a high number of distinct values. <br />
<br />
Enjoy the power of Power Pivot.<br />
<br />
André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-45429544451643130732013-02-25T18:09:00.000+01:002013-02-25T18:09:52.322+01:00How to create XML with a TSQL query?<div align="LEFT">
In this blog post I will describe how you can generate an XML file using TSQL statements. For instance for data migrations, you need to export data from your SQL database which can be imported via XML in another system. </div>
<br />
The solution is really simple. Add ''FOR XML" to your SELECT query.<br />
<br />
<strong>Example 1:</strong> <br />
SELECT res_id, sur_name, first_name<br />
FROM Humres <br />
WHERE Res_id > 0 <br />
FOR XML PATH ('Resource')<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBZyvk2Ba5jQUUIyY-efBOO8tdwAWH1o6woO48xXMfnXpqWkgUYoqw0Q-QfScQ_FhQuxLW7D2uBCGJt6HoOOP2UciRRA913GJumoss4zxgWExQXEZ-QGvBTFghDz1SmAYzdWmq7qrR7g4/s1600/TSQL-XML1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="101" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBZyvk2Ba5jQUUIyY-efBOO8tdwAWH1o6woO48xXMfnXpqWkgUYoqw0Q-QfScQ_FhQuxLW7D2uBCGJt6HoOOP2UciRRA913GJumoss4zxgWExQXEZ-QGvBTFghDz1SmAYzdWmq7qrR7g4/s400/TSQL-XML1.PNG" width="400" /></a></div>
<br />
<br />
<br /><br /><br /><br />
<br />
<div>
</div>
<div>
The „FOR XML‟ always needs to be completed with the „AUTO‟ or „PATH‟ command:</div>
<ul>
<li>When using „AUTO‟ command every column in the SELECT query will be handled as an attribute in a single element per records.</li>
<li>When including the „PATH(< path name >)‟ command the XML path can be set. Every records starts with its own parent element having the label as defined in the „PATH‟ command. Every column in the SELECT query will be handled as child element.</li>
</ul>
<div>
In this example the „PATH‟ command is used since this allows better control.</div>
<div>
</div>
<div>
<div>
<strong>Example 2:</strong></div>
</div>
<div>
The next step would be to include custom column names to be used in the XML elements (rather than using „res_id‟, „sur_name‟, etc.) and include the resource number as an attribute in the „Resource‟ element.</div>
<div>
</div>
<div>
SELECT res_id AS <a href="mailto:'@number'">'@number'</a>, <br /> RTRIM(sur_name) AS 'LastName', <br /> RTRIM(first_name) AS 'FirstName'<br />FROM Humres <br />WHERE Res_id > 0 <br />FOR XML PATH ('Resource')</div>
<div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhs3mQKy9YMb73xzPgbjIg3dLEyoRMv4CqB9uc6Tlkygz7DrCJ3E3b3gl_aZngbcQF85PzYdDZ3xiAbcEajpmoF4FYLTo-JQ1r_Um9M78w6NYyhK910YIekMMyHI-keJKq5YhkMzl9B0zE/s1600/TSQL-XML2.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="148" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhs3mQKy9YMb73xzPgbjIg3dLEyoRMv4CqB9uc6Tlkygz7DrCJ3E3b3gl_aZngbcQF85PzYdDZ3xiAbcEajpmoF4FYLTo-JQ1r_Um9M78w6NYyhK910YIekMMyHI-keJKq5YhkMzl9B0zE/s320/TSQL-XML2.PNG" width="320" /></a></div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
Explanation:</div>
<div>
<ul>
<li>Use a „@‟ in the column name results in an attribute</li>
<li>Including the RTRIM command trims the value (removing the extra spaces at the end of the value).</li>
<li>In many cases XML is case sensitive. Therefore make sure to use the correct attribute and element names.</li>
</ul>
<div>
<strong>Example 3:</strong></div>
<div>
A final step in creating a basic XML file would be to include the root element. A root element can be included by simply adding the command „ROOT(< root name >)‟ to the XML command in the SQL query.</div>
</div>
<div>
</div>
<div>
SELECT res_id AS <a href="mailto:'@number'">'@number'</a>, <br /> RTRIM(sur_name) AS 'LastName', <br /> RTRIM(first_name) AS 'FirstName'<br />FROM Humres <br />WHERE Res_id > 0 <br />FOR XML PATH ('Resource'), ROOT('Resources')</div>
<div>
</div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikbTE5uoGZLqPrrmvaPlgsp_ungAb_MoNn59SXZxkXU9R7yL_qMbJB0eRz1te41E5dOvNewyzHfOfBG3pdmLsrj_yNME4S_1X_LqkJYZtsTi5TvsRApTK1dIBmDkk4zY3phnjjvOdIu40/s1600/TSQL-XML3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="163" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikbTE5uoGZLqPrrmvaPlgsp_ungAb_MoNn59SXZxkXU9R7yL_qMbJB0eRz1te41E5dOvNewyzHfOfBG3pdmLsrj_yNME4S_1X_LqkJYZtsTi5TvsRApTK1dIBmDkk4zY3phnjjvOdIu40/s320/TSQL-XML3.PNG" width="320" /></a></div>
<div>
</div>
<div>
Please note:<br />Sometimes a second root element is needed. For instance in Exact (the <eexact> element). Since the XML formatting of SQL queries only allows one root element, this can only be handled by using sub queries (or adding the root element manually to the output file). The use of sub queries will be explained in the following paragraph.</eexact></div>
<div>
</div>
<div>
<strong>Example 4</strong>: Creating child-elements<br />There are two ways of generating child-elements as part of your parent element (which is defined in the „PATH‟ command and applies to every record).</div>
<div>
<strong>Example 4.1</strong> Child elements with 1:1 relationship<br />The first way can only be used in case there is a 1:1 relationship between the parent element (in our example the resource records) and the child element (in the example below the title record). In this case the child-element can be generated by including the element name in the column names (in the SELECT section):</div>
<div>
</div>
<div>
SELECT<br />h.res_id as <a href="mailto:'@number'">'@number'</a>,<br />RTRIM(h.sur_name) as 'LastName',<br />RTRIM(h.first_name) as 'FirstName',<br />RTRIM(p.predcode) as <a href="mailto:'Title/@code'">'Title/@code'</a>,<br />p.aan_oms as 'Title/Description',<br />p.aanhef as 'Title/Salutation'<br />FROM humres h<br />LEFT JOIN pred p ON h.predcode = p.predcode<br />WHERE res_id > 0<br />FOR XML PATH('Resource'), ROOT('Resources')</div>
<div>
</div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxKL__jS3nIaTyVKwHHh-vMyGFghgoiw9BAjyGcuGnsDGekN6PGrSzlCsej_NbflilB-d8qmh662ndOwrN_MYPiY7bZbvfzRv7jDImiAQ4YAmReo_9opkcxvOcdq-egLqNz_8osgEoxjs/s1600/TSQL-XML4.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="161" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxKL__jS3nIaTyVKwHHh-vMyGFghgoiw9BAjyGcuGnsDGekN6PGrSzlCsej_NbflilB-d8qmh662ndOwrN_MYPiY7bZbvfzRv7jDImiAQ4YAmReo_9opkcxvOcdq-egLqNz_8osgEoxjs/s320/TSQL-XML4.PNG" width="320" /></a></div>
<div>
</div>
<div>
Explanation:</div>
<ul>
<li>A LEFT JOIN on table „pred‟ has been included to get the prefix data.</li>
<li>Correlation names „h‟ and „p‟ have been included to easily refer to the correct tables (in this case „humres‟ and „pred‟).</li>
<li>By including a forward slash (“/”) in the custom column names, a child element can be generated. The child element name needs to be defined on the left side of the forward slash.</li>
<li>Multiple forward slashed can be used in the column names to use deeper child element levels.</li>
</ul>
<div>
<strong>Example 4.2</strong> Child elements with 1:N relationship<br />In case a 1:N relationship exist, such as one customer having multiple contacts, the child elements should be generated based on a sub query which gets all matching records. In our example, using the resources table, the resource is the parent element and the roles are the child elements. The sub query should get all roles linked to the resource and generate the corresponding child elements.<br />First create the sub query with a XML mark-up to get all roles:</div>
<div>
SELECT<br />r.RoleID as <a href="mailto:'@code'">'@code'</a>,<br />r.RoleLevel as <a href="mailto:'@level'">'@level'</a>,<br />rd.Description as 'Description'<br />FROM humres h2<br />LEFT JOIN HRRoles r ON h2.res_id = r.EmpID<br />LEFT JOIN HRRoleDefs rd ON r.RoleID = rd.ID<br />FOR XML PATH('Role'), ROOT('Roles')</div>
<div>
</div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfJI-9XRGxqoPqwrDbwGycUjHnvl2-ZgZWQU1_yeKyki63BxftfbInrqv6zCa__6j1-3DM7sRX_Z-8SPF5PyStae6m4YPiusy7fzL1X14yF92M34hgxFQAbCCyMCLbQO2nFh4nRwzbBPE/s1600/TSQL-XML5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="105" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfJI-9XRGxqoPqwrDbwGycUjHnvl2-ZgZWQU1_yeKyki63BxftfbInrqv6zCa__6j1-3DM7sRX_Z-8SPF5PyStae6m4YPiusy7fzL1X14yF92M34hgxFQAbCCyMCLbQO2nFh4nRwzbBPE/s320/TSQL-XML5.PNG" width="320" /></a></div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfJI-9XRGxqoPqwrDbwGycUjHnvl2-ZgZWQU1_yeKyki63BxftfbInrqv6zCa__6j1-3DM7sRX_Z-8SPF5PyStae6m4YPiusy7fzL1X14yF92M34hgxFQAbCCyMCLbQO2nFh4nRwzbBPE/s1600/TSQL-XML5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><div style="text-align: left;">
</div>
</a><br />
<div>
Explanation:</div>
<div>
<ul>
<li>The query gets all resources (humres), linked to roles (hrroles) and the role details (hrroledefs).</li>
<li>The correlation name for humres is set to h2 since this query will become a sub query in which “h” already exists.</li>
</ul>
<div>
Next, this query needs to be part of the main query. This can be done by including it in the SELECT section of our main query and by making sure the sub query returns only the roles per specific resource.</div>
<div>
</div>
<div>
SELECT<br />h.res_id as <a href="mailto:'@number'">'@number'</a>,<br />RTRIM(h.sur_name) as 'LastName',<br />RTRIM(h.first_name) as 'FirstName',<br />RTRIM(p.predcode) as <a href="mailto:'Title/@code'">'Title/@code'</a>,<br />p.aan_oms as 'Title/Description',<br />p.aanhef as 'Title/Salutation',<br />(<br />SELECT<br />r.RoleID as <a href="mailto:'@code'">'@code'</a>,<br />r.RoleLevel as <a href="mailto:'@level'">'@level'</a>,<br />rd.Description as 'Description'<br />FROM humres h2<br />LEFT JOIN HRRoles r ON h2.res_id = r.EmpID<br />LEFT JOIN HRRoleDefs rd ON r.RoleID = rd.ID<br />WHERE h2.res_id = h.res_id<br />FOR XML PATH('Role'), ROOT('Roles'), TYPE<br />)<br />FROM humres h<br />LEFT JOIN pred p ON h.predcode = p.predcode<br />WHERE res_id > 0<br />FOR XML PATH('Resource'), ROOT('Resources')</div>
<div>
</div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUI3OXIe-F17l32cfncdOHni1_U2RACOeJsC2elvrWE3oZmqtfGJR6LLX6C9D9fJihXO6R4mTO9KN36yndL1CctgFVnIxM18YfyWON9ZvDmquFf9C-BixcxCmIU_xsQazeCEU0QXr3SIY/s1600/TSQL-XML6.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="154" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUI3OXIe-F17l32cfncdOHni1_U2RACOeJsC2elvrWE3oZmqtfGJR6LLX6C9D9fJihXO6R4mTO9KN36yndL1CctgFVnIxM18YfyWON9ZvDmquFf9C-BixcxCmIU_xsQazeCEU0QXr3SIY/s320/TSQL-XML6.PNG" width="320" /></a></div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjUI3OXIe-F17l32cfncdOHni1_U2RACOeJsC2elvrWE3oZmqtfGJR6LLX6C9D9fJihXO6R4mTO9KN36yndL1CctgFVnIxM18YfyWON9ZvDmquFf9C-BixcxCmIU_xsQazeCEU0QXr3SIY/s1600/TSQL-XML6.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><div style="text-align: left;">
</div>
</a><br />
<div>
Explanation:</div>
<div>
<ul>
<li>In the WHERE section of the sub query the filter „h2.res_id = h.res_id‟ has been added to make sure only the roles per user are taken.</li>
<li>In the FOR XML section the command „TYPE‟ has been added. If left out, the query result of the sub query will be alphanumeric (varchar) instead of XML. This means, with the „TYPE‟, the sub query result will be printed as a text/string.</li>
<li>Note that in this case it is “legal” to have multiple records and fields coming from a sub query. In standard (non-XML) SELECT queries having a sub query in the SELECT should only result in one record and field.</li>
</ul>
<div>
Example 5. XML file format</div>
<div>
When running a SELECT query with XML commands, the XML output is in „Unicode (UTF-8)‟ coding by default:</div>
<div>
</div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXFewSHzYiknNAWC8XJe-MOura9risS5U-kcxAG6iVLXjfG-PvnvnuVEs3tCTJCL1bi1HmdgOzan5DG9UEU2UVlru8fVvmcYZgNC-ZQ_np_Vc54P0mbMgi7Isreny4Iyr90-0kEukdLA4/s1600/TSQL-XML7.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="80" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXFewSHzYiknNAWC8XJe-MOura9risS5U-kcxAG6iVLXjfG-PvnvnuVEs3tCTJCL1bi1HmdgOzan5DG9UEU2UVlru8fVvmcYZgNC-ZQ_np_Vc54P0mbMgi7Isreny4Iyr90-0kEukdLA4/s320/TSQL-XML7.PNG" width="320" /></a></div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXFewSHzYiknNAWC8XJe-MOura9risS5U-kcxAG6iVLXjfG-PvnvnuVEs3tCTJCL1bi1HmdgOzan5DG9UEU2UVlru8fVvmcYZgNC-ZQ_np_Vc54P0mbMgi7Isreny4Iyr90-0kEukdLA4/s1600/TSQL-XML7.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><div style="text-align: left;">
</div>
</a><br />
<div>
When saving the output file, the file format will be using this encoding. Therefore, make sure to select the correct encoding type supported by the target application. (One of) the encoding type supported by Exact is „Western European (Windows)‟.</div>
<div>
</div>
</div>
</div>
<div>
Enjoy it to create your own XML files via TSQL.</div>
André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com6tag:blogger.com,1999:blog-8930349235661427759.post-18137055749363220142013-01-21T21:41:00.000+01:002013-01-21T21:48:13.908+01:00Design tips for My favorite dashboard.Dashboards are very popular way to display data. Other popular names are (performance) cockpits. Sometimes I see dashboards for which I think, which vision is used to build such a bad dashboard. If you start building a dashboard without a vision it will end up in a dashboard which maybe looks nice as a first impression, but is useless on a daily basis. In this blog post I will describe my vision how you should build a useful dashboard. First of all I will start with 2 examples of a dashboard. Both will show the same information. The only difference is the presentation of the data. Examples are the best way to explain.<br />
Let's start with a dashboard in which a lot of improvements can be made:<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifFCZAlmBEf7PP3y_pLhrokYkO4rFNKNUYl3dRT0QN0uYD9UCQCyek91BXDrEDE2i9XmO2kchPHo9OX_xiUKq-XPMBEDhD-PBybJmfLdwRlHOwSqY0QX0IFb7PLhcurN3Yg3UA4cCIftA/s1600/Sales+Dashboard+Wrong.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifFCZAlmBEf7PP3y_pLhrokYkO4rFNKNUYl3dRT0QN0uYD9UCQCyek91BXDrEDE2i9XmO2kchPHo9OX_xiUKq-XPMBEDhD-PBybJmfLdwRlHOwSqY0QX0IFb7PLhcurN3Yg3UA4cCIftA/s400/Sales+Dashboard+Wrong.PNG" width="338" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Now the same information in presented in a better way:</div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEljOJ7cl-dMWamjRuIzA-oTjBS1ucK1u8F8QjrHTtJZozgWqtXIl7Hk14hrnQeElqFxJRuAmDB-QCp6gxBt6TeMn5koj5zqjKUZ6OXfUGgHvpoEiX52goPavIbZ-8itz35Nu1SodWg9M/s1600/Sales+Dashboard+Better.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEljOJ7cl-dMWamjRuIzA-oTjBS1ucK1u8F8QjrHTtJZozgWqtXIl7Hk14hrnQeElqFxJRuAmDB-QCp6gxBt6TeMn5koj5zqjKUZ6OXfUGgHvpoEiX52goPavIbZ-8itz35Nu1SodWg9M/s400/Sales+Dashboard+Better.PNG" width="342" /></a></div>
<br />
<br />
I will comment on the dashboard which can be improved. In general one rule can be applied: <span style="text-align: center;"><span style="color: red;">Keep in mind. Less is better.</span></span><br />
<div style="text-align: center;">
<span style="font-size: x-large;"><br /></span></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div style="text-align: center;">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyqOZGTqOUd5qZUUEFFuBBhok2_1-UyRa90nedd-nWMPkeAO_4hrZrvAQpO2QHx7hi-qXUGjYMXBA8bFLz-s68smTDDjWpL0OfMNRTMS6e2bB1qheGaFIDId4iAnTp-kI5ey8yANtsLpE/s1600/Sales+Dashboard+Wrong2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyqOZGTqOUd5qZUUEFFuBBhok2_1-UyRa90nedd-nWMPkeAO_4hrZrvAQpO2QHx7hi-qXUGjYMXBA8bFLz-s68smTDDjWpL0OfMNRTMS6e2bB1qheGaFIDId4iAnTp-kI5ey8yANtsLpE/s400/Sales+Dashboard+Wrong2.PNG" width="343" /></a></div>
<span style="font-size: x-large;"><br /></span></div>
<br />
<ol>
<li>Report title in big font and printed Bold. Goal of the dashboard is to show attention to the figures not to the title.</li>
<li>Gray background in the report. This is visual fluff. The gray color has no function. Keep in mind. Less is better.</li>
<li>Axe values. A lot of zero's is expensive space and is difficult to read. It will make the bar chart it self smaller. We read with our brains. Is the axis value 200.000 or 2 million? </li>
<li>Red color of the bar chart. Is something wrong? Colors have a function. Red is a color to grab attention that something is wrong (STOP). In this example nothing is wrong. Revenue is better than previous year.</li>
<li>3D graph. A dashboard is not a painting. The dashboard is used in a business environment. Not in a gallery. Always use 2D graphs. They are easier to read and understand. For example: What is the value for period 2 of previous year?</li>
<li>Aqua color with gradient style center. This is visual fluff. Keep in mind. Less is better.</li>
<li>Legend takes a lot of valuable space which result in a smaller bar chart. It's not the legend but the chart which need to most space. </li>
<li>Numbers should not be center-justified in the columns. Right-justified is easier to compare when scanning up and down a column.</li>
<li>Use grid lines carefully. Keep in mind. Less is better.</li>
<li>Matrix header in bigger font, printed bold on a colored background. One way of visualization is enough to emphasize difference between the lines. Using a list level for other lines will show difference between header and sub-lines. Keep in mind. Less is better.</li>
<li>Underline the header. This is useless. Keep in mind. Less is better.</li>
<li>Repeating currency symbol. One currency symbol is enough. Is will save value space in every column.</li>
<li>Hyperlink to other report is printed in the same font color. Use a other color for hyperlinks, so the user can see that a hyperlink to another report is available.</li>
<li>Remove the border of the bar chart.</li>
</ol>
<div>
Some more remarks to take into account during the design of your dashboard.</div>
<ul>
<li>A dashboard is used on a daily basis. Display only data which needs your attention, so you can take action to improve. Report the KPI's which needs improvement. The dashboard need to help you to achieve your goal. Data content which will not change overtime is use less on a daily used dashboard.</li>
<li>We do not see with our eyes, we see with our brains. Content on the dashboard should be clear to understand. If people need to think about what they see, you need to change the visualization of your information.</li>
<li>A dashboard should fit on one screen. Avoid scrolling bars. With scrolling bars, it can happen that something that needs your attention (RED) is outside your screen.</li>
<li>Do not use shadows. It is visual fluff with no meaning.</li>
<li>Do not use logo's and pictures. It is wasting your valuable space. If needed make it small and place it somewhere out of the way.</li>
<li>What do you want to show or compare? Based on this select the best visualization for it. See : <a href="http://www.keepitsimpleandfast.com/2012/07/which-visualizations-should-i-use-in-my.html">http://www.keepitsimpleandfast.com/2012/07/which-visualizations-should-i-use-in-my.html</a> For example using a pie chart to compare 2 values can be useful However it can take a lot of useful space. Do not use is to compare multiple values especially if you do not know how many values you need to compare. A bar chart is better in this situation.</li>
<li>Display consolidated information, summaries or exceptions. Do not display details. Details are used in slice and dice reports to explain the consolidated information, summaries or exceptions.</li>
<li>Put data which is relevant to each other, close together.</li>
<li>Use a gray color for your fonts instead of black.</li>
</ul>
<div>
Enjoy it to build dashboards to deliver a good user experience for your users. </div>
André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-27565405188588492412013-01-14T19:50:00.000+01:002013-01-15T08:25:29.402+01:00Display last refresh time in Power PivotWith Power Pivot, you can start analyzing your data off-line. After importing the data you do not need a database connection anymore. A lot of people want to know the date of the last time that the Power Pivot data is refreshed. You can do this in the following way:<br />
Add a tab (LastDataRefreshTime) to the Power Pivot window and use next query:<br />
<br />
SELECT GETDATE() AS LastDataRefreshTime<br />
<br />
After adding the tab to the Power Pivot Window, one record is added in this sheet. This record can be used in you Power Pivot Sheets.<br />
<br />
Select the tab in your Excel sheet on which you want to add this LastDataRefreshTime. <br />
Add a Pivot table and select LastDataRefreshTime from the PowerPivot Field List.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5O7GhC-Ppy7onYyVOTEDkUbFM7hgvxdIYoAzX15dUpMPjT3maYqc-tFToJQD8PZ3DCbUwjAz8L4jpUTd6hrvT2l_hEEoZ0Em_tmRIb1gOlSmFIuOKZZTaHgSWVI-zBscOMp8BDS9CaiI/s1600/PowerPivot_LastRefreshDate.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5O7GhC-Ppy7onYyVOTEDkUbFM7hgvxdIYoAzX15dUpMPjT3maYqc-tFToJQD8PZ3DCbUwjAz8L4jpUTd6hrvT2l_hEEoZ0Em_tmRIb1gOlSmFIuOKZZTaHgSWVI-zBscOMp8BDS9CaiI/s400/PowerPivot_LastRefreshDate.PNG" width="400" /></a></div>
<br />
By default the measure is added as a SUM. Change this to MAX.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0eogWg7J4DZx-peEQ551TR2GLPUVJ3UI4IvzqxetlfhwBATLe7WjxL0jQW6RfXtjasbtYrsiGCwjShZJLUepv5l3-T2Hvy-tAxMDshAY8kjs-oXQPKIw7PECcJQKCPtMFcCNmp74RAFw/s1600/PowerPivot_LastRefreshDate2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="182" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0eogWg7J4DZx-peEQ551TR2GLPUVJ3UI4IvzqxetlfhwBATLe7WjxL0jQW6RfXtjasbtYrsiGCwjShZJLUepv5l3-T2Hvy-tAxMDshAY8kjs-oXQPKIw7PECcJQKCPtMFcCNmp74RAFw/s320/PowerPivot_LastRefreshDate2.PNG" width="320" /></a></div>
<br />
The last data refresh time will now be displayed in your sheet.<br />
Enjoy the Power of Power Pivot.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com1tag:blogger.com,1999:blog-8930349235661427759.post-29940554305362520182013-01-11T15:30:00.002+01:002013-01-11T15:30:40.794+01:00Exception from HRESULT: 0x800A03EC during starting Power View in Office 2013In Office 2013 you can enable Power Pivot and Power View. After opening a Power Pivot sheet you can start using Power View. Power View can be found in the Insert Ribbon of Office 2013.<br />
<br />
After pressing the Power View icon next error can occur: <br />
Exception from HRESULT: 0x800A03EC<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZ4Nnqe8aWAfIyYzkeuuIle4KwcLEmRtdRV1W_f1GtjB8hhdR9Ut8Abb1mn-5UGu0lfrli1sQlS4BSDGR2Ff8HolPGhVry08gE73kcqWoPs6dvz0UrChWGl24IptbDZOpVyYBW6y3R1iw/s1600/PowerPivotUpgrade2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="254" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhZ4Nnqe8aWAfIyYzkeuuIle4KwcLEmRtdRV1W_f1GtjB8hhdR9Ut8Abb1mn-5UGu0lfrli1sQlS4BSDGR2Ff8HolPGhVry08gE73kcqWoPs6dvz0UrChWGl24IptbDZOpVyYBW6y3R1iw/s320/PowerPivotUpgrade2.PNG" width="320" /></a></div>
<br />
<br />
This error can occur in next situation:<br />
<ul>
<li>You have created a Power Pivot sheet with Power Pivot version 11.1.3000 in Office 2010.</li>
<li>You opened this Power Pivot sheet in Office 2013.</li>
<li>You press the Power View button in the Insert Ribbon of Office 2013.</li>
</ul>
Solution: Upgrade your Power Pivot datamodel to datamodel of PowerPivot for Excel 201. To do this follow next steps:<br />
<ul>
<li>Open the Power Pivot sheet Version (11.1.3000) in Office 2013.</li>
<li>Select the Power Pivot in the Ribbon.</li>
<li>Press the Manage Data Model button in the Power Pivot Ribbon.</li>
<li>You will get next message: This workbook has a Power Pivot data model created using a previous version of the PowerPivot add-in. You'll need to upgrade this data model with PowerPivot for Excel 2013.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbPf907SzbgotA02k-Ors4CwDp6r13TCzlaaFUI0lFDmhb_W2AD3_kRUrRQy0Z4MHHI4xfKZzzQpM4edDXrs6jmnSs_WF_CopbBNDploUD9_paN7AFqU09j0s3C4npNFNNHqOcCvWr0JE/s1600/PowerPivotUpgrade1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="88" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbPf907SzbgotA02k-Ors4CwDp6r13TCzlaaFUI0lFDmhb_W2AD3_kRUrRQy0Z4MHHI4xfKZzzQpM4edDXrs6jmnSs_WF_CopbBNDploUD9_paN7AFqU09j0s3C4npNFNNHqOcCvWr0JE/s640/PowerPivotUpgrade1.PNG" width="640" /></a></div>
</li>
<li>Press OK</li>
<li>Start the upgrades of your Power Pivot model. </li>
<li>After upgrading successfully, you can press the Power View button to start using Power View.</li>
</ul>
Enjoy the Power of Power View.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com1tag:blogger.com,1999:blog-8930349235661427759.post-21406255729416582632012-12-13T18:26:00.001+01:002013-01-11T17:37:52.275+01:00SSRS reports on the IPad or IPhone has been improved since SQL 2012 SP1Almost 2 years ago I wrote a <a href="http://www.keepitsimpleandfast.com/2011/02/viewing-reporting-service-reports-ssrs.html" target="_blank">blogpost</a> about SQL Server Reporting Service (SSRS) Reports on the IPad or IPhone. I was the first time you could display SSRS reports on the IPad. Unfortunatly not everything was rendered in a correct way. In SQL Server 2012 SP1 this has been improved.<br />
<br />
Here is an example of a report in SQL 2008 R2<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2yNjn36RQXgqBJ3iSlwB1WKpa6gTXv7v8uLDw5lqDnlWxIM-NKS3bsK6N_TPzIKCtzDyjNszKEpmwdglZX3xDU-k0-OjzScgvfJuSIyX29fZ2_0d_AWuSjKzkOqzAZEXq8LT1jie4Jms/s1600/IpadSSRS2008R2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="335" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2yNjn36RQXgqBJ3iSlwB1WKpa6gTXv7v8uLDw5lqDnlWxIM-NKS3bsK6N_TPzIKCtzDyjNszKEpmwdglZX3xDU-k0-OjzScgvfJuSIyX29fZ2_0d_AWuSjKzkOqzAZEXq8LT1jie4Jms/s400/IpadSSRS2008R2.PNG" width="400" /></a></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><br />
</div> Here is an example of the same report in SQL 2012 SP1<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJSieXFIpMAJU94Go8Q_Lee4y9QdE582IU5uznqGMvwmHPYqocaQj5YjxhRV7mUCs0hIbY59YDaxCjsZklrsqF7FoY-_P9mjczAqpx-oKO0byiBvUlFkfzEoEnkvVMRsYPmYkzLQk6o7I/s1600/IpadSSRS2012.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="333" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJSieXFIpMAJU94Go8Q_Lee4y9QdE582IU5uznqGMvwmHPYqocaQj5YjxhRV7mUCs0hIbY59YDaxCjsZklrsqF7FoY-_P9mjczAqpx-oKO0byiBvUlFkfzEoEnkvVMRsYPmYkzLQk6o7I/s400/IpadSSRS2012.PNG" width="400" /></a></div>As you can see, the strange Blue Question marks are gone.<br />
<br />
Starting with SQL 2012 Service Pack 1 (SP1), Reporting Services supports viewing and basic interactivity with reports on Apple iOS devices like IPad en Iphone, with the Apple Safari browser. <br />
Viewing reports in Report Manager (<a href="http://myserver/reportserver">http://myserver/reportserver</a>) is not suppported. You need to start the reports from the report server via <a href="http://myserver.reports/">http://myserver.reports</a>. Here you can browse to the report and tape the report name to open the report. After opening the report yiu can see that the Export to PDF and TIFF file is supported. More information about the support for Apple iOS devices on SSRS can be found <a href="http://msdn.microsoft.com/en-us/library/jj659023.aspx" target="_blank">here</a>. <br />
For a video of using SSRS on your Ipad please see:<br />
<br />
<script src="http://technet.microsoft.com/en-us/videoembed/jj873792" type="text/javascript"></script><br />
<br />
<br />
André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com2tag:blogger.com,1999:blog-8930349235661427759.post-72218412731164452012012-12-03T17:46:00.000+01:002012-12-03T17:46:09.530+01:00PowerPivot fieldlist is grey and not selectable<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3mqdTn9fHn1J9H1U4JZsOt0bkBNdR-Y8u31W_i7pCBOK51Se61xz2w6sTO23R20b9Qshyphenhyphen3bx4M2JnIHvSv0CUvhkuXkKBcOZBLMkZsuRnydj2wB9K3TJrkAwhww3WGcG65DF3rY_la5s/s1600/PowerPivot_GrayedFieldlist.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="95" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3mqdTn9fHn1J9H1U4JZsOt0bkBNdR-Y8u31W_i7pCBOK51Se61xz2w6sTO23R20b9Qshyphenhyphen3bx4M2JnIHvSv0CUvhkuXkKBcOZBLMkZsuRnydj2wB9K3TJrkAwhww3WGcG65DF3rY_la5s/s400/PowerPivot_GrayedFieldlist.PNG" width="400" /></a></div>
<br />
<br />
Sometimes my PowerPivot field list is grey and can't be selected. It can happen when you are busy in an existing PowerPivot sheet. It looks like a bug because a restart of Excel solved my situation.<br />
<br />
Solution:<br />
<ul>
<li>Save your Power Pivot Sheet</li>
<li>Close all other Excel sheets</li>
<li>Close Excel</li>
<li>Check in task manager if Excel.exe is not running</li>
<li>If Excel.exe is still running, kill this task</li>
<li>Start Excel</li>
<li>Open your Power Pivot sheet</li>
<li>Click on a graph and you should be able to select the Field list.</li>
</ul>
Unfortunatly, is is not clear when and how this happens. I'm using Power Pivot version 11.0.3000.0André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-51181060098068784522012-11-20T15:02:00.000+01:002012-11-20T15:05:21.371+01:00Power Pivot: The PivotTable report will not fit on sheet.<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4_eBJwk6wZ15bWbBqMwQAdw6TZpWwgF2ElHNQkw5_2pIGafEqoF_i7oUDLfsrHbQ6pJNXxzRiCw_l-MYAX8ddj90lVs_pq6eC23_0Q6iT8z2XUVChHu-vk7_a7YEcR01R63eDSuu4I7k/s1600/Pivottable+report+will+not+fit+on+the+sheet.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="106" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4_eBJwk6wZ15bWbBqMwQAdw6TZpWwgF2ElHNQkw5_2pIGafEqoF_i7oUDLfsrHbQ6pJNXxzRiCw_l-MYAX8ddj90lVs_pq6eC23_0Q6iT8z2XUVChHu-vk7_a7YEcR01R63eDSuu4I7k/s400/Pivottable+report+will+not+fit+on+the+sheet.PNG" width="400" /></a></div>
<br />
<br />
During the refresh of data in an Excel Power Pivot next error can occur: <br />
<br />
The PivotTable report will not fit on the sheet. Do you want to show as much as possible?<br />
<br />
In the past I created an Excel Power Pivot file with multiple sheets. On every sheet one or more pivots or pivot charts are defined. Big question for me: On which sheet does this error occur? Current error message is to general. It does not tell on which sheet the error occurs. After a while I found the root cause of this problem. In the past I made a Pivot chart on some data. The datasheet of this Power Pivot Chart was hidden. Later on I deleted the sheet with the Pivot Chart. The datasheet is not automatically deleted. In this situation the datasheet was still availabel as hidden sheet. I unhide the sheet. When I looked to the data, it contains a big amount of columns. (Over 256 columns). This sheet with more than 256 column is the root cause of this error. Because the initial Pivot chart sheet was already deleled, I deleted the datasheet. After deleting the datasheet, I was able to refresh all data in my Power Pivot sheet.<br />
<br />
Solution: check all datasheets in your Power Pivot sheet for pivots with more than 256 columns.<br />
<br />
<br />André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-89430707334918523672012-10-17T21:50:00.000+02:002012-10-17T21:50:15.486+02:00The alternative for Zipping your SQL backups. Save download and restore time.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQFNYbBeUGNRQsszUPDFls6l57VdknLzANtfkhVAzYg-j9C_h4iwIbkkWbul87SBqa39_CDnkJ5qlDscRpnqSVLv8GG6bv8Mop8pmc1IuUbG_hxKfPY_fFuUwVb2bPmVKGKb7x86AsJ6U/s1600/Compress-Images.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" nea="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQFNYbBeUGNRQsszUPDFls6l57VdknLzANtfkhVAzYg-j9C_h4iwIbkkWbul87SBqa39_CDnkJ5qlDscRpnqSVLv8GG6bv8Mop8pmc1IuUbG_hxKfPY_fFuUwVb2bPmVKGKb7x86AsJ6U/s320/Compress-Images.jpg" width="255" /></a></div>
Sometimes you need a SQL database backup to analyze. To minimize the download time people compress the SQL database backup with tools like WINZIP, WINRAR, 7ZIP, ARJ etc...... This is nice but there is a more efficient way. First of all, I will explain the download and restore process of a WINZip backup. <br />
You need to uncompress the database backup file before you can start the restore process it self. Example: you receive a SQL database backup of 50 Gb which is compressed to 5 GB. To restore this database, you need much more diskspace: <br />
<ul>
<li>5 Gb for the zip file</li>
<li>50 Gb for the backup file</li>
<li>50 Gb for the restored database. (assume their is no empty space in the database)</li>
</ul>
In total 105 Gb of diskspace is needed.<br />
<br />
<br />
The more efficient way. Use the Backup compression feature. Backup time is much faster because less disk IO is needed to write the backup file. A compressed backup file can be restored without a seperate uncompress proces. This will save a lot of disk space. In the previous example 50 Gb because you do not need to uncompress the WINZIP file. You will receive a 5Gb database which you can directly restore to the 50 GB database file(s).<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsem6o_GJcTtvbCAJnvXsaQxNNyxybeiW6px-pQvhWSw8QNwYAvhsA-wRTaN-jKCC-vTEaQUciiaS_JyOuRoml3PRTKVMETsRXcaPi9tzjCuRjXNHsnggeeDn9JSKAWDxBgupIqF30udk/s1600/SQLCompressBackup.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="361" nea="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsem6o_GJcTtvbCAJnvXsaQxNNyxybeiW6px-pQvhWSw8QNwYAvhsA-wRTaN-jKCC-vTEaQUciiaS_JyOuRoml3PRTKVMETsRXcaPi9tzjCuRjXNHsnggeeDn9JSKAWDxBgupIqF30udk/s400/SQLCompressBackup.PNG" width="400" /></a></div>
<br />
In the option tab of the Backup database window you will find at the buttom the Set backup compression option. By default it is set to Use the default server setting. You can change this to Compress backup.<br />
<br />
On server level you can change the default compression setting to compressed. Retrieve the server properties of the SQL server. Select the Database Settings property. Check the Compress backup checkbox. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA80NPEvOeiG59lhTgmsLRRTmyglvWqHyqjqvkYEMlDZhMIIhMX0i1GxGvfF41GadM7jpRQ2m4WaKqNWRB6_hUJcPGiZ7izf64_m21dWy6hdAWSKNBAuszuxxult7Q1_cMR8dRLq5cJ7M/s1600/SQL+Compress+serversetting.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="277" nea="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgA80NPEvOeiG59lhTgmsLRRTmyglvWqHyqjqvkYEMlDZhMIIhMX0i1GxGvfF41GadM7jpRQ2m4WaKqNWRB6_hUJcPGiZ7izf64_m21dWy6hdAWSKNBAuszuxxult7Q1_cMR8dRLq5cJ7M/s400/SQL+Compress+serversetting.PNG" width="400" /></a></div>
As of now every SQL backup will be compressed.<br />
<br />
By default, compression significantly increases CPU usage, and the additional CPU consumed by the compression process might adversely impact concurrent operations. Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup. <br />
<br />
More information can be found <a href="http://www.keepitsimpleandfast.com/2010/04/backup-compression-in-sql-server-2008.html" target="_blank">here</a>.<br />
<br />
<br />
André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-16315573199731545192012-10-15T11:28:00.000+02:002012-10-15T11:28:09.959+02:00SQl 2008 R2 Setup fails: ExecuteStandardTimingsWorkflowI tried to install SQL 2008 R2 Enterprise Edition X64 on my laptop which has a brand new image of Windows 7. All Windows updates are installed. I started the setup of SQl 2008 R2 from the root of my installation DVD. I run the setup with the option 'Run as Administrator'. During the Setup Support Files, the setup progress hangs on: ExecuteStandardTimingsWorkflow. <br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikGTUS3Z8uKw30BNB9tbMEAMmGHISpfqhqWyfbUJWI5DHJyRkRBDBu8Fy-33Xt1F4JCTUDn8Iyr40vmtMyjaSW-2xX2LXplQDq6yO5R5TtwVbRnDKqBVNx6dU2ViT7sUnBoskyaO1sgCg/s1600/SQL1008R2setupfail1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="228" nea="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEikGTUS3Z8uKw30BNB9tbMEAMmGHISpfqhqWyfbUJWI5DHJyRkRBDBu8Fy-33Xt1F4JCTUDn8Iyr40vmtMyjaSW-2xX2LXplQDq6yO5R5TtwVbRnDKqBVNx6dU2ViT7sUnBoskyaO1sgCg/s320/SQL1008R2setupfail1.PNG" width="320" /></a></div>
After a while next error while occur: Error writing to file: X86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4027_xww_e69378d0.cat Verify that you have access to that directory.<br />
<br />
Big question: Which directory?<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6i6FdIKtQDe8D0yrqmxH7z5NQ3qK4tDt6NxZ8Q8p3XOV-DVuS4WyJsrllPkeMwvBGuBMbPUmBz3BjVdW99C4LARPsWO6iMM71aesHNjMauXXI8LNAAYpdt9AqfLXV8UkRNJwpc5zulnk/s1600/SQL1008R2setupfail2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="97" nea="true" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6i6FdIKtQDe8D0yrqmxH7z5NQ3qK4tDt6NxZ8Q8p3XOV-DVuS4WyJsrllPkeMwvBGuBMbPUmBz3BjVdW99C4LARPsWO6iMM71aesHNjMauXXI8LNAAYpdt9AqfLXV8UkRNJwpc5zulnk/s320/SQL1008R2setupfail2.PNG" width="320" /></a></div>
<br />
Solution: Your installation DVD is corrupt. Take another installation DVD to complete succesfully the setup of SQL Server 2008 R2.<br />
<br />
<br />
<br />
<br />
André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com1tag:blogger.com,1999:blog-8930349235661427759.post-62791520879818959682012-09-21T16:45:00.001+02:002012-09-21T16:45:31.655+02:00Script to generate a time dimension table to use in Power Pivot.<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyPt5TyGN_FjrTam73opeSkHsedrf1Fk3acMc9nFlGpInHKpU4aXvilu2c-MzrcLj2NClu141l88yvy00BL6PU-kU2y9FEA5HFClBtWocNsCSP5c3fDe6Ltzztn2czcdHqARaYVLYuC0Q/s1600/Clock..jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyPt5TyGN_FjrTam73opeSkHsedrf1Fk3acMc9nFlGpInHKpU4aXvilu2c-MzrcLj2NClu141l88yvy00BL6PU-kU2y9FEA5HFClBtWocNsCSP5c3fDe6Ltzztn2czcdHqARaYVLYuC0Q/s200/Clock..jpg" width="200" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
In SQL Server 2012 Power Pivot a new feature is introduced. You can Mark as Date Table. This will enable you to leverage date filtering in Excel. For instance, you can see the revenue totals grouped by different date groupings. For instance per week number, per month, week day number etc. To use this feature you need to have a Time Dimension table.<br />
<ul>
<li>Create a time dimension table. (See later in this blog how to do)</li>
<li>Mark this table as Date table. </li>
<li>Link from your Revenue table the column invoicedate to the key of the Time Dimension table.</li>
</ul>
Now you are ready to use this feature.<br />
<br />
To create a Time dimension table you can a script (GlobeBI_DimTime.SQL) which can be downloaded from<a href="https://skydrive.live.com/?cid=c5068f78fa13e055&id=C5068F78FA13E055%211982" target="_blank"> here</a>. At the end of the script you can specify the start date and end date of the Time dimension entries. <br />
<br />
<span style="font-size: x-small;"> </span><span style="color: blue;"><span style="color: blue; font-size: x-small;"></span></span><br />
<span style="color: blue;"><span style="font-family: inherit;"><span style="color: blue; font-size: x-small;">INSERT INTO <span style="color: black;">Dimtime</span></span></span></span><span style="color: blue; font-size: x-small;"><br /><span style="font-family: inherit;">
SELECT </span></span><span style="font-family: inherit;"><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">*</span></span><span style="font-size: x-small;"> </span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;">FROM</span></span><span style="font-size: x-small;"> dbo</span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">.</span></span><span style="font-size: x-small;">F_TABLE_DATE</span><span style="color: blue; font-size: x-small;"><span style="color: blue; font-size: x-small;"> </span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">(</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'20000101'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">,</span></span><span style="color: red; font-size: x-small;"><span style="color: red; font-size: x-small;">'20201231'</span></span><span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;">)</span></span></span><br />
<span style="color: grey; font-size: x-small;"><span style="color: grey; font-size: x-small;"></span></span><br />
In the script Date entries are created from 1 Januari 2000 up to 31 December 2020.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0tag:blogger.com,1999:blog-8930349235661427759.post-18808915932372009312012-08-08T09:45:00.000+02:002012-08-08T09:45:24.690+02:00How to use Dynamic data sources in your SSRS report.<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjscdQeqDNm4ywXfogGYEVW9TvbcRWZvw5zSsrnvtLvMQI61buZ-7yggCOM49U3_cBErVX5v195XurRBm7h9ClcAxx7a7RvRg0zAWSAmoVg6xacoAxD-Qcg8cWW-pj0ZIhDTCshE2UN8jM/s1600/IMG_9737.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="213" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjscdQeqDNm4ywXfogGYEVW9TvbcRWZvw5zSsrnvtLvMQI61buZ-7yggCOM49U3_cBErVX5v195XurRBm7h9ClcAxx7a7RvRg0zAWSAmoVg6xacoAxD-Qcg8cWW-pj0ZIhDTCshE2UN8jM/s320/IMG_9737.JPG" width="320" /></a></div>
<br />
You can have situations in which your report should be executed on multiple databases. The user should be able to select the desired database on which the report should run. In this blog post I will explain how you can do this.<br />
To be able to select to different database you need to make use of a dynamic data source. A data source makes use of a connection string. It is possible to pass the connection string of a data source as an expression. By using an expression, you can make use of parameter values to pass the servername and database name to the connection string. There is only one restriction of a dynamic data source. The data source should be embedded within the report. It can not be implemented with a shared data source. In this blogpost I will use a second database in which I retrieve the available SQL server\databases on which my reports should be executed.<br />
<div>
</div>
<ul>
<li>Open your report</li>
<li>Add 2 report parameters </li>
<ul>
<li>ServerName</li>
<li>DatabaseName</li>
</ul>
<li>Add datasource named: DynamicDataSource. Use the a 'hard coded' connection string. For instance:
Data Source=MySQLServer1;Initial Catalog=MyDatabase1.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJVH-GluT_TUi_HALDEk1M2RjGOcib1px-vLpgHykahzogKOuQUHvtZxwdTdLQYUYUz4sooYUjTnR5wVhI7jxWY-D7tGVNu-skl6F1znRWO4rvWWQVILLPnvsE1_9cUz5WmJHTwZ2iN9Q/s1600/SSRSDynamicDatasource2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJVH-GluT_TUi_HALDEk1M2RjGOcib1px-vLpgHykahzogKOuQUHvtZxwdTdLQYUYUz4sooYUjTnR5wVhI7jxWY-D7tGVNu-skl6F1znRWO4rvWWQVILLPnvsE1_9cUz5WmJHTwZ2iN9Q/s1600/SSRSDynamicDatasource2.JPG" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<ul>
<li>Add datasource to the database with all SQL Server\databases. In my example named: Synergy</li>
<li>Add a embedded dataset to retrieve SQL Server and Database information.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpPIVGq3BI7hgTsCjCLMFI7jsTqFq-gHDOaazynnW3JZlZny6cuHA9K82XAQsCbgXpUSzfwUwzD2qnOWGVbDVkpVMhyB5dL5BhrfGQbTiUYEjkhQdB_Z6ZM_gOULdrdn-6T-eoyobEiMc/s1600/SSRSDynamicDatasource1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="250" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpPIVGq3BI7hgTsCjCLMFI7jsTqFq-gHDOaazynnW3JZlZny6cuHA9K82XAQsCbgXpUSzfwUwzD2qnOWGVbDVkpVMhyB5dL5BhrfGQbTiUYEjkhQdB_Z6ZM_gOULdrdn-6T-eoyobEiMc/s320/SSRSDynamicDatasource1.JPG" width="320" /></a></div>
</li>
<li>Configure the available values for the report parameters: ServerName and DatabaseName.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0GEw7Bq7hAoWUUIfp18r1iUBds3RFmrmxxKLNp7dHo9Wgu-9BXEqSV7FvUmdzIyiblLNlKhho2-UJzKh1PDPJ_kYkapKUM1brrFOLCo9W_0NCjBDRxNLE0smBf4cXjIq2lAXXqwWueug/s1600/SSRSDynamicDatasource3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="232" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0GEw7Bq7hAoWUUIfp18r1iUBds3RFmrmxxKLNp7dHo9Wgu-9BXEqSV7FvUmdzIyiblLNlKhho2-UJzKh1PDPJ_kYkapKUM1brrFOLCo9W_0NCjBDRxNLE0smBf4cXjIq2lAXXqwWueug/s320/SSRSDynamicDatasource3.JPG" width="320" /></a></div>
</li>
<li>Add all datasets and report items to your report. </li>
<li>Test your report using the 'hard coded' connection string. </li>
<li>If everything works fine, change the 'hard coded' connection string with next expression<br />
<span style="font-size: x-small;">=</span><span style="color: #a31515; font-size: x-small;"><span style="color: #a31515; font-size: x-small;">"data source="</span></span><span style="font-size: x-small;"> & Parameters!ServerName.Value & </span><span style="color: #a31515; font-size: x-small;"><span style="color: #a31515; font-size: x-small;">";initial catalog="</span></span><span style="font-size: x-small;"> & Parameters!DatabaseName.Value</span><br />
<span style="font-size: x-small;"></span>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGNAsXKXDId6Bh4jsJI7HHIehyCxNYOVCowrQtxUuwfFhI93KudRFMMTuCRsAYH0McVAajLx_QhvfoNqPc-4MEZQOGAtJTOoDXCoVTJuy-7oXDxkKFsmiLLj50QqTaOvnFkSPSqlkY-nM/s1600/SSRSDynamicDatasource4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="202" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjGNAsXKXDId6Bh4jsJI7HHIehyCxNYOVCowrQtxUuwfFhI93KudRFMMTuCRsAYH0McVAajLx_QhvfoNqPc-4MEZQOGAtJTOoDXCoVTJuy-7oXDxkKFsmiLLj50QqTaOvnFkSPSqlkY-nM/s400/SSRSDynamicDatasource4.JPG" width="400" /></a></div>
</li>
<li>Run the report and select a value for the report parameters ServerName and DatabaseName
</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXwu8I6L93ChYgghXk9JJBFgUto71UHq2iWzVzmRk876kILT5HfruHBnap43jQnHOY9uACSJaYk0yGjOR2yLbRUAxqlhtZe1bPMJkXi1hGoXVByZdy6MYr67IuSgJQ8rb_T1PrB1qP558/s1600/SSRSDynamicDatasource5.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="80" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjXwu8I6L93ChYgghXk9JJBFgUto71UHq2iWzVzmRk876kILT5HfruHBnap43jQnHOY9uACSJaYk0yGjOR2yLbRUAxqlhtZe1bPMJkXi1hGoXVByZdy6MYr67IuSgJQ8rb_T1PrB1qP558/s400/SSRSDynamicDatasource5.JPG" width="400" /></a></div>
<br />
Enjoy it.André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com4tag:blogger.com,1999:blog-8930349235661427759.post-626276399218649432012-08-07T11:24:00.000+02:002012-08-07T11:24:22.126+02:00Bluescreen caused by CIPCDDDP.SYS after rebooting Windows 7 Enterprise X64<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiExm8r3xM0igz2rV_BsTjJt3EayOqbvANRhZSIraDEbA_RAkAtbx2jnB2NBjSIYKcpB6Gffc5MXu6H4B_To34gN5F7e3h2TLi1woSqXP-DmR6AHsPO7TFHfnZxV1pl42rfL0oFzhUz-YI/s1600/CiscoIPCOmmunicator.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiExm8r3xM0igz2rV_BsTjJt3EayOqbvANRhZSIraDEbA_RAkAtbx2jnB2NBjSIYKcpB6Gffc5MXu6H4B_To34gN5F7e3h2TLi1woSqXP-DmR6AHsPO7TFHfnZxV1pl42rfL0oFzhUz-YI/s1600/CiscoIPCOmmunicator.png" /></a></div>
<br />
After rebooting my laptop which runs Windows 7 Enterprise edition (64 bits) I got blue screens (BSOD) caused by driver CIPCDDDP.SYS. I could only boot in safe mode. On another computer I found that the CIPCDDDP.SYS driver is part of the Cisco IP Communicator V7. In Safe mode it was not possible to unistall this driver. This driver is loaded when network drivers are loaded. To avoid this process <br />
<ul>
<li>I undocked my laptop</li>
<li>Disabled my wireless network using the wireless network swith on my Dell Latitude E6410. </li>
<li>Now I was able to boot without a bluescreen. </li>
<li>After booting I enabled the wireless network card. </li>
<li>Update my Cisco IP Communicator client to version 8.6.2.</li>
</ul>
After booting I did not get a BSOD but after a while my laptop did not respond to any keyboard input. Therefor I disabled again my wireless network. After booting successfully, I enabled my wireless network card and started Cisco IP Communicator 8.6.2. successfully and was able to make a phone call.<br />
<br />
So everything is working again after 90 minutes. I hope this workaround will also help for you if you experience this Blue Screen of Death (BSOD).<br />
<br />André van de Graafhttp://www.blogger.com/profile/07230010757193182052noreply@blogger.com0