[转]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
- How to monitor and improve the SSIS packages performance?
- How to implement the team development for the big SSIS project?
- How to design and implement the Data profiling?
- How to implement the trasanction in SSIS package?
Analysis Service
- What’s linked Cube?
- How to improve the Cube process performance and Cube access proformance?
- How to implement Cube security model?
- What’s the confirmed dimension? when/why/how to use it?
- How to implement Data warehouse in SSAS by using Kimball and Inmon methodolegy?
Basic about SQL Server
- How long have you been using SQL Server 2005 or SQL Server 2008 (or Oracle/DB2 if mentioned in resume)?
- How can you do the SQL script (T-SQL, SP…) performance tuning?
- What’s CTE (Common Table Expression)? What’s the benifit for develpers to use it?
- What’s difference between the SQL Server datatype nVarchar and Varchar?
- What’s difference between the SQL Server datatype Varchar and Char?
- What’s difference between Union and Union All, which one is faster if same records retrieved?
Reporting Services
- How long time have you been using SQL Server Reporting Services 2005/2008?
- What’s difference between SSRS 2005 and SSRS 2008 (if the candidate answered yes when asking if used SSRS 2005)?
- What’s difference between List control, Table control and Matrix control?
- How to implement the cascading parameters in Report Designer?
- How to deploy reports to reports server?
- How to deploy reports to Sharepoint server? (if use Sharepoint server to host reports)
Integration Services
- How long time have you been using DTS/SSIS 2005/2008?
- What’s difference between Data Flow task and other control flow tasks?
- What’s user varible? How to define it?
- How to pass varibles to the Execute SQL task?
- How can you handle the package level errors and Task level errors?
- How can you design the data flaw to support the transaction?
- How can you deploy the SSIS packages?
Analysis Services
- How long time have you been using DTS/SSIS 2005/2008?
- What’s Perspective in the Cube design?
- What’s difference between Dimension tables and Cube dimension?
- How do you create time dimension table?
- When you process the cube, what’s differnce between Full Process, Default Process and Process Data?
- 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
- How long have you been using SQL Server 2005 or SQL Server 2008 (or Oracle/DB2 if mentioned in resume)?
- How to use the Ranking function ?
- What’s policy and strategy to desion the table partition?
- What’s the key point to setup the SQL Sever remote connection?
- How to detect and release the deadlock?
- What’s the difference between phisical ER and logical ER design?
- Conditions to use index view.
Reporting Services
- How to implement the reports security model?
- When connecting to SSAS cubes, how to use the default Cube aggregation? What’s the benefit to use it?
- You found that it is very slow when you browse from web browser, how can you improve the performance?
- How to implement reports Scale-Out deployment?
Integration Services
- How to monitor and improve the SSIS packages performance?
- How to implement the team development for the big SSIS project?
- How to design and implement the Data profiling?
- How to implement the trasanction in SSIS package?
Analysis Service
- What’s linked Cube?
- How to improve the Cube process performance and Cube access proformance?
- How to implement Cube security model?
- What’s the confirmed dimension? when/why/how to use it?
- How to implement Data warehouse in SSAS by using Kimball and Inmon methodolegy?
