[转]BI 问答

http://blog.bridata.ca/?cat=16

前几天Post 了一些BI 的面试问题，感兴趣的人很多，有很多人问我答案以此来评估一下自己的知识水平。坦白地说我没有写在纸上的具体答案，事实上每个问题也没有具体和精确的答案，所谓面试就不是笔试，很多问和答全在交谈中的感觉了。也可能国内的面试和国外的不同吧，或者说找工市场不同？总之从 CSDN 上的反馈来看，大家还是挺有兴趣的。以下选择了些 Sr. 级别的问题，简单写了些答案，立此存照。

Questions I asked candidates who  are applying Sr.Developer/Tech lead position:

Basic about SQL Server

• How to use the Ranking function ?

A:  Ranking 函数不只是指Rank 一个函数，有4个Ranking 函数，按最常用排应该是：Row_Number, Rank, Dense_Rank 和 NTile,  语法是 Ranking function OVER (Partition by … Order By….).  有一次我们稍微修改了一点儿面试题目：使用T-sql, 返回一个数字列值中的第三大的值，可以用 Row_Number 实现。不过有一个家伙很聪明，他虽然没写上 Row_Number 这种方法，但是用了另一个很聪明的方法，也照样Pass 了。

• What’s policy and strategy to desion the table partition?

A:  Table partitioning (分区？) 无疑会提高性能(大部分)，但是设计Partition 应该注意一些原则，否则也会影响系统性能：只对大记录量的表分区，比如1M 记录一个分区；分区参考列的选择标准应该使一个 SELECT/UPDATE/DELETE 语句只操作一个分区的数据；另外一个值得注意的是索引和视图也应该随之而分区。  

• What’s the key point to setup the SQL Sever remote connection?

A: 这种问题通常不会问，如果问也是看候选人的知识面了。建立远程连接有两个关键点：打开相应的IP 端口和设置用户安全。

• How to detect and release the deadlock?

A:  很多方法，常用的有 SQL profiler 和 DMV，随便能说清楚一个就行。

• What’s the difference between physical ER and logical ER design?

A: Physical ER 是给技术人员看的，可以直接生成DDL 的；Logical ER 是给业务分析看的，但是在很复杂的数据环境下，Logical ER 的作用是非常明显的：它能使技术人员很清楚地明白数据表之间的业务逻辑。

• Conditions to use index view.

A: Index view 的性能是很好的，但是想用它还有很多限制，它的限制有20-30个，也就是说几乎只有简单SELECT 和JOIN 语句才能用在建立Index View 中，以下的SQL操作符不能包含在Index View中: UNION，Sub Query，OUTER　JOIN，Distinct, Exists, min, max 等。 

Reporting Services

• How to implement the reports security model?

A: 只要能回答出3层安全就可以：Reports Catalog 安全(设置访问Reports 系统数据库的权限)、Reporting Services 访问安全(访问Reporting Services的权限)、Reports DB 访问安全(访问Reports中用到的数据库的权限)；

• When connecting to SSAS cubes, how to use the default Cube aggregation? What’s the benefit to use it?

A: Aggregation() 函数，这个问题能很快知道该候选人是否真正使用了以SSAS为数据源的报表。好处就是一个字：快！

• You found that it is very slow when you browse from web browser, how can you improve the performance?

 A: 这个问题太大了，…

• How to implement reports Scale-Out deployment?

Integration Services

1. How to monitor and improve the SSIS packages performance?
2. How to implement the team development for the big SSIS project?
3. How to design and implement the Data profiling?
4. How to implement the trasanction in SSIS package?

Analysis Service

1. What’s linked Cube?
2. How to improve the Cube process performance and Cube access proformance?
3. How to implement Cube security model?
4. What’s the confirmed dimension? when/why/how to use it?
5. How to implement Data warehouse in SSAS by using Kimball and Inmon methodolegy? 

Basic about SQL Server

1. How long have you been using SQL Server 2005 or SQL Server 2008 (or Oracle/DB2 if mentioned in resume)?
2. How can you do the SQL script (T-SQL, SP…) performance tuning?
3. What’s CTE (Common Table Expression)? What’s the benifit for develpers to use it?
4. What’s difference between the SQL Server datatype  nVarchar and Varchar?
5. What’s difference between the SQL Server datatype Varchar and Char?
6. What’s difference between Union and Union All, which one is faster if same records retrieved?

Reporting Services

1. How long time have you been using SQL Server Reporting Services 2005/2008?
2. What’s difference between SSRS 2005 and SSRS 2008 (if the candidate answered yes when asking if used SSRS 2005)?
3. What’s difference between List control, Table control and Matrix control?
4. How to implement the cascading parameters in Report Designer?
5. How to deploy reports to reports server?
6. How to deploy reports to Sharepoint server? (if use Sharepoint server to host reports)

Integration Services

1. How long time have you been using DTS/SSIS 2005/2008?
2. What’s difference between Data Flow task and other control flow tasks?
3. What’s user varible? How to define it?
4. How to pass varibles to the Execute SQL task?
5. How can you handle the package level errors and Task level errors?
6. How can you design the data flaw to support the transaction?
7. How can you deploy the SSIS packages?

Analysis Services

1. How long time have you been using DTS/SSIS 2005/2008?
2. What’s Perspective in the Cube design?
3. What’s difference between Dimension tables and Cube dimension?
4. How do you create time dimension table?
5. When you process the cube, what’s differnce between Full Process, Default Process and Process Data?
6. In the relation of Fact tables and Dimension tables, what’s the Fact relationship, reference relationship mean?

Questions I asked candidates who  are applying Sr.Developer/Tech lead position:

Basic about SQL Server

1. How long have you been using SQL Server 2005 or SQL Server 2008 (or Oracle/DB2 if mentioned in resume)?
2. How to use the Ranking function ?
3. What’s policy and strategy to desion the table partition?
4. What’s the key point to setup the SQL Sever remote connection?
5. How to detect and release the deadlock?
6. What’s the difference between phisical ER and logical ER design?
7. Conditions to use index view.

Reporting Services

1. How to implement the reports security model?
2. When connecting to SSAS cubes, how to use the default Cube aggregation? What’s the benefit to use it?
3. You found that it is very slow when you browse from web browser, how can you improve the performance?
4. How to implement reports Scale-Out deployment?

Integration Services

1. How to monitor and improve the SSIS packages performance?
2. How to implement the team development for the big SSIS project?
3. How to design and implement the Data profiling?
4. How to implement the trasanction in SSIS package?

Analysis Service

1. What’s linked Cube?
2. How to improve the Cube process performance and Cube access proformance?
3. How to implement Cube security model?
4. What’s the confirmed dimension? when/why/how to use it?
5. How to implement Data warehouse in SSAS by using Kimball and Inmon methodolegy?