OLAP PivotTable Extensions (for caculated member issue)

source: http://olappivottableextend.codeplex.com/

OLAP PivotTable Extensions is an Excel 2007 and Excel 2010 add-in which extends the functionality of PivotTables on Analysis Services cubes. The Excel API has certain PivotTable functionality which is not exposed in the UI. OLAP PivotTable Extensions provides an interface for some of this functionality. It also adds some new features like searching cubes, configuring default settings, and filtering to a list in your clipboard. The add-in can be launched from the following menu option in the right-click menu for PivotTables:

Private Calculated Members

Any calculated members which are part of the Analysis Service cube on the server can be added to PivotTables. But there is no built-in UI to define your own MDX calculations. Adding extra Excel calculations in the cells surrounding the PivotTable has some limitations as they are not part of the PivotTable and can be wiped out if the dimensions of the PivotTable change, and plain Excel calculations must only operate on data visible in the PivotTable.

OLAP PivotTable Extensions let you define your own calculated measures which are private to that particular PivotTable:


Those calculations appear in the PivotTable just like any other calculations:


They appear in the Field List pane at the very top under the Values grouping:


For help with advanced calculation properties for these private PivotTable calculations, or for help with MDX expressions, refer to our Calculations Help page.

Best Practice: In order to ensure a single version of the truth, it is a best practice to define important calculations as part of the cube source code. But some calculations like simple ratios or differences may clutter the cube and may be more appropriate if defined in the PivotTable itself. In addition, certain ad hoc research or prototyping may be more appropriate to be done as calculations private to a PivotTable until they are finalized and are ready to be added to the cube source code.

Limitation: If you run "OLAP Tools... Convert to Formulas" on a PivotTable with private calculated members in Excel 2007, the private calculated members will show N/A. There is no known workaround at this point other than upgrading to Excel 2010 or having your OLAP administrator define these calculated members in the cube itself.

Calculations Library

Since all calculations you define are private to that one PivotTable, OLAP PivotTable Extensions automatically creates a Calculations Library for you which contains all the calculations you create. This allows you to pick any previous calculation you've used from a dropdown and add it to the current PivotTable:



You can also perform Calculation Library maintenance by importing, exporting, and deleting calculations:

View PivotTable MDX

If a PivotTable is performing poorly or returning incorrect numbers, it may be necessary for the Analysis Services administrator to troubleshoot the MDX query which the PivotTable is using. The MDX tab of the OLAP PivotTable Extensions dialog shows you this MDX.



The MDX is exactly what is sent to the server with one exception. Any private calculations you've created for your PivotTable are defined as session calculated members. The MDX query exposed on the MDX tab displays the formulas for these calculations as query calculated members in the WITH clause of the MDX query. This allows an administrator to copy and paste the MDX query and troubleshoot it more easily in Management Studio or MDX Studio.

Filtering PivotTable to a List

A common scenario is having a list of items you wish to research in a PivotTable. Instead of manually checking each item in the filter dropdown, you can use the Filter List feature from OLAP PivotTable Extensions:

Changing PivotTable Defaults

Certain settings must be manually changed after creating a new PivotTable. For instance, if your dimensions have calculated members you wish to see in your PivotTable, you must manually right click on the PivotTable, choose PivotTable Options, flip to the Display tab, then check "Show calculated members from OLAP server". The Defaults tab of OLAP PivotTable Extensions lets you default this setting to be on in any new PivotTables you create in the future. If checked, it also automatically sets "Refresh data when opening the file" on the connection properties:

Searching

Finding what you’re looking for in a cube can sometimes be challenging, but the Search feature of OLAP PivotTable Extensions can help. It lets you text search the items in the Field List and their descriptions. It also lets you text search the dimension members in your cube.



For more detailed information about the Search feature, see the dedicated Search page.

Distributing PivotTables

OLAP PivotTable Extensions need only be installed on computers which need to create new private PivotTable calculations. After those calculations have been defined, the PivotTable can be distributed to others without problem. If you distribute the Excel 2007 workbook to other Excel 2007 users, they will be able to continue designing and manipulating that PivotTable without problem. If published to Excel Services, the private calculations you define will still be active in the PivotTable.



The Calculation Library does not need to be distributed unless other users wish to start brand new PivotTables and reuse certain calculations you have created using OLAP PivotTable Extensions.

Using Excel 2007/2010 and OLAP PivotTable Extensions to edit an Excel 2003 format .xls workbook with a PivotTable and add a private calculated member will work. This PivotTable can be saved and distributed to users of Excel 2003 and the private calculated member will show up and work.

Installation Requirements

• Excel 2007 or Excel 2010 is required.
• Access to a cube on an Analysis Services server is required.
• .NET Framework version 2.0 is required.

Troubleshooting Installation

If OLAP PivotTable Extensions is not visible in Excel, please consult the Troubleshooting Installation page.

Network CAT-5, CAT-5e, CAT-6, CAT-7 Cables FAQ & differences

1. What is the difference between CAT-5, CAT-5e, CAT-6, CAT-7...

The Simple Answer:
CAT-5 is rated to 100M
CAT-5e is rated to 350M
CAT-6 and CAT6e is rated to 550M or 1000M depending on your source
CAT-7 is supposedly rated to 700M or presumably 1000M
Today there is no approved CAT-6 or CAT-7. While some folks are selling products they call Level 6 or 7, there aren't even specs for them, making CAT-5e the best available option. CAT-6 cable is being made with 23 guage conductor wire as opposed to the slightly smaller 24 guage for CAT-5e and also has a separator to handle crosstalk better. 

Both CAT-5 and CAT-5e have 100 ohm impedance and electrical characteristics supporting transmissions up to 100 MHz. The differences between CAT-5 and CAT-5e show in all aspects of performance: capacitance, frequency, resistance, attenuation, and NEXT. CAT-5e components were designed with high-speed gigabit Ethernet in mind. While CAT-5 components may function to some degree in a gigabit Ethernet, they perform below standard during high-data transfer scenarios. CAT-5e cables work with ATM and gigabit speed products. Simply, if you are using a 100Mbps switch, get CAT-5e cable instead of CAT-5.
CAT-5e is formally called ANSI/TIA/EIA 568A-5 or simply Cat-5e (the e stands for 'enhanced'). CAT-5e is completely backward compatible with current CAT-5 equipment. The enhanced electrical performance of CAT-5e ensures that the cable will support applications that require additional bandwidth, such as gigabit Ethernet or analog video.

2. What is the difference between the types of cable - UTP, Patch, Stranded, Solid...

UTP stands for Unshielded Twisted Pair. It is a cable type with pairs of twisted insulated copper conductors contained in a single sheath. UTP cables are the most common type of cabling used in desktop communications applications.
Stranded cable has several small gauge wires in each separate insulation sleeve. Stranded cable is more flexible, making it more suitable for shorter distances, such as patch cords.
Solid has one larger gauge wire in each sleeve. Solid cable has better electrical performance than stranded cable and is traditionally used for inside walls and through ceilings - any type of longer run of cable.
Patch Cables are made of stranded copper conductors for flexibility. This construction is great for the flexing and the frequent changes that occur at the wall outlet or patch panel. The stranded conductors do not transmit data signals as far as solid cable. The TIA/EIA 568A which is the governing standard regarding commercial cabling systems limits the length of patch cables to 10 meters in total length. Does that mean you can't use stranded cable for longer runs? Not at all, we've seen installations running stranded cable over 100 feet with no problems - it's just not recommended. This is why we don't sell patch cables over 30 feet in length.

3. What is the difference between 10BASE-T, 100BASE-T and 1000BASE-T?

10BASE-T is the IEEE standard that defines the requirement for sending information at 10 Mbps on unshielded twisted-pair cabling, and defines various aspects of running Ethernet on this cabling.
100BASE-T is the IEEE standard that defines the requirement for sending information at 100 Mbps on unshielded twisted-pair cabling, and defines various aspects of running baseband Ethernet on this cabling.
1000BASE-T (also called gigabit Ethernet) is the IEEE standard that defines the requirement for sending information at 1000 Mbps on unshielded twisted-pair cabling, and defines various aspects of running baseband Ethernet on this cabling

Channel Performance Characteristics

Parameter	CAT-5 (ISO Class D)100 MHz	CAT-5 100 MHz	CAT-5e 100 MHz
Attenuation	24.0dB	24.0dB	24.0dB
NEXT	27.1dB	27.1dB	30.1dB
PSNEXT	24.0dB	N/A	27.1dB
ELFEXT	17.0dB	17.0dB	17.4dB
PSELFEXT	14.4dB	14.4dB	14.4dB
ACR (derived)	3.1dB	3.1dB	6.1dB
PSACR (derived)	N/A	N/A	3.1dB
Return Loss	10.0dB	8.0dB	10.0dB

4. What is a cross-over cable?

A cross-over cable is a segment of cable that crosses over pins 1&2 and 3&6. This cable is normally used to connect two PCs without the use of a hub, or can be used to cascade two hubs without using an uplink port. Some DSL modems require a crossover calbe to the PC or hub they are connected to.

5. What are the maximum lengths for cables?

For Solid UTP:
Fast Ethernet 100baseT 100 Meters (328 feet)
Twisted Pair Ethernet 10baseT 100 Meters (328 feet)
Recommended maximum lengths for Patch Cables made from stranded cable:
Fast Ethernet 100baseT 10 Meters (33 feet)
Twisted Pair Ethernet 10baseT 10 Meters (33 feet)

6. Is CAT-5e backwards compatible?

Yes it works with any 10BaseT or 100BaseT network cards and hubs.
CAT-5 is also upwardly compatible with CAT-5e, however your network throughput will only be as fast as the slowest part.

7. Can I run CAT-5e ethernet cable outside?

CAT-5e cable is not rated for outdoor use, however it can generally be used without a problem. If possible, run the cable through some kind of conduit to prevent moisture or an attractive site for lightning to strike. You should be able to find gray PVC conduit suitable for cable at any hardware store. Remember, 100 Meters is your max distance, without some kind of hub, bridge or amplification.

8. What is the operating temp for CAT-5e cable?

Operating Temp for CAT-5e cable: -10C to 60C

9. What is the difference between T568A and T568B wiring?

T568A and T568B are the 2 wiring patterns for 8 position RJ45 modular plug, both permitted under the TIA/EIA 568A wiring standards document. The only difference between the two pattern is that the pairs 2 (orange) and 3 (green) are interchanged.
All our cables use T568B standards.

10. What are the UL levels of cable?

There are three levels. General Purpose: UL1581; Riser: UL1666; and Plenum UL910. These numbers are all fire and safety rated.

11. What is EMI and how can you prevent it?

EMI stands for Electro-Magnetic Interference. It is potentially harmful to your communications system because it can lead to signal loss and degrade the overall performance of high-speed, CAT-5e cabling. EMI is interference in signal transmission or reception and is caused by the radiation of electrical or magnetic fields which are present near power cables, heavy machinery, or fluorescent lighting.
Avoiding EMI is as simple as not laying your network cable within 12" of electrical cable, or if needed switching from UTP to more expensive shielded cable.

12. Just tell me what cable I need...

If you need patch cords up to 25 feet, use CAT-5e stranded cable for ease of flexibility.

If you are making linger runs or going through walls or ceilings, I'd go ahead and use the CAT-6 UTP (solid) cables - if that seems a bit expensive, then go with the CAT-5e UTP.

XBMC: Add / Removing a Video Source from list

search sources.xml under C:

Just use Notepad or something to edit the file as needed (remove, add, etc)

SQL join multiple tables (SQL join多个表)

NNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
INNER JOIN 连接四个数据表的用法：
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号
INNER JOIN 连接五个数据表的用法：
SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号
连接六个数据表的用法：略，与上述联接方法类似，大家举一反三吧：）

注意事项：
在输入字母过程中，一定要用英文半角标点符号，单词之间留一半角空格；
在建立数据表时，如果一个表与多个表联接，那么这一个表中的字段必须是“数字”数据类型，而多个表中的相同字段必须是主键，而且是“自动编号”数据类型。否则，很难联接成功。
代 码嵌套快速方法：如，想连接五个表，则只要在连接四个表的代码上加一个前后括号（前括号加在FROM的后面，后括号加在代码的末尾即可），然后在后括号后 面继续添加“INNER JOIN 表名X ON 表1.字段号=表X.字段号”代码即可，这样就可以无限联接数据表了：）

How to monitor / check the current connections to iis per site

@ Web Service \ Current Connections perfmon counter -- get either _Total or per site.

Remove the IE warning - ‘Do you want to view only the webpage content that was delivered securely?’

Internet Explorer interrupts the download and displays a confirmation dialog whenever it detects the use of mixed content on a secure page.
In IE 7 and ealier, this dialog would cause annoyance to users but generally didn’t cause any other significant problems. This was because it was worded in such a way that most users would click on the Yes button and allow non-secure content to be downloaded.
However, the wording in the IE 8 version of this dialog has changed:




here's what you need to do if you don't want to see that dialog:

tools>internet options>security>custom level>display mixed content: enable

Note that if the site you are visiting is NOT on the internet zone, you would have to make the changes to the appropiate zone:

here's how you tell which "zone" you are on, on the bottom right hand corner of the browser you should see something like this:


If you double click that, you can make changes to the other zones (Local Intranet, Trusted sites, restricted sites)



Just click the zone you want, and it will bring up the first dialog (from this post) where you can make the change.

Add an Output Caching Rule (IIS 7)

Applies To: Windows 7, Windows Server 2008, Windows Server 2008 R2, Windows Vista
Output cache rules are helpful when you must cache requests for a certain file type differently than for other file types on your server, site, or application. For example, you might want to keep ASP.NET pages in the cache longer than other file types in your application because the ASP.NET pages require more resources for processing than would the other file types.

Prerequisites

For information about the levels at which you can perform this procedure, and the modules, handlers, and permissions that are required to perform this procedure, see Output Caching Feature Requirements (IIS 7) [ http://technet.microsoft.com/en-us/library/cc754226(WS.10).aspx ] .
Exceptions to feature requirements

• None
  

To create an output cache rule

You can perform this procedure using the user interface (UI) or a command line.
To use the UI

1. Open IIS Manager and navigate to the level you want to manage. For information about opening IIS Manager, see Open IIS Manager (IIS 7) [ http://technet.microsoft.com/en-us/library/cc770472(WS.10).aspx ] . For information about navigating to locations in the UI, see Navigation in IIS Manager (IIS 7) [ http://technet.microsoft.com/en-us/library/cc732920(WS.10).aspx ] .
   
2. In Features View, double-click Output Caching.
   
3. Click Add in the Actions pane.
   
4. In the Add Cache Rule dialog box, in the File name extension box, enter the file name extension, such as .aspx, for which you want to create a cache rule.
   
5. Select the User -mode caching check box to enable output caching, and select one of the following options:
   
   • Using file change notifications: if you want an item to be removed from the cache when it is updated.
     
   • At time intervals (hh:mm:ss): if you want an item to be removed from the cache after a certain time elapses. Then type a time in the format of hh:mm:ss (where h is hours, m is minutes, and s is seconds) in the corresponding box.
     
     

     Note
     If you configure different values for this property in the User-mode caching and Kernel-mode caching sections, the value in the Kernel-mode caching section will be used.

   • Prevent all caching: to prevent all caching of the designated file type.
     
6. Select the Kernel-mode caching check box if you want to enable kernel caching in addition to output caching, and select one of the following options:
   
   • Using file change notifications: if you want an item to be removed from the cache when it is updated.
     
   • At time intervals (hh:mm:ss): if you want an item to be removed from the cache after a certain time elapses. Then type a time in the format of hh:mm:ss (where h is hours, m is minutes, and s is seconds) in the corresponding box.
     
   • Prevent all caching: to prevent all caching of the designated file type.
     
7. Click OK.
   

Configuration

The procedure in this topic affects the following configuration elements:
collection under the element
For more information about IIS 7 configuration, see IIS 7.0: IIS Settings Schema [ http://go.microsoft.com/fwlink/?LinkId=88551 ] on MSDN.

source: http://technet.microsoft.com/en-us/library/cc770547%28WS.10,printer%29.aspx