Cognos+FrameWork+Transfer要点----面试经典理论.docx

Cognos BI 提供了报表（reporting），分析（analysis），记分卡（scorecarding），仪表盘（dashboarding），业务事件管理（business event management）以及数据集成（data integration）等功能。从java etl 自动过数，手动过数，将有错的发信息到电子邮件。Cognos framework manager建模，按公司、模块 创建主题，再创建shortcut 以适应不同模式的报表查询。Transformer 创建，抽取，过滤，加载，用全量，增量的混合模式加载数据。Java IQC，申购04年的客户dts方案建立模型的步聚：制作IQD文件，建立多维模型，运行模型生成CUBE。模型打抱发布。建立客户端到数据库的连接-导入数据对象-建立查询主题-模型打包发布一congnos configuration（配制iis7或tomcate5）1.cognos 配制，dataaccess 删除旧的，新建连接数据库。2.连接oracle需utf-8/utf-16字符集，oracle jdbc驱动拷贝到cognos安装目录下，db2的unicode字符集，与db2 jdbc 驱动拷贝到cognos安装目录下3.配置名字空间AD相配，电子邮件。4.分配内存，有小型配制，中型配制，大型配置。（右键删除，再右键添加）5.加载语言包，中文，越南文6.framework manager建模，一个模型，名字空间，数据源，参数映射，包，多语言设定。模型-可以发布到ReportNetServer的元数据集名字空间-对象的容器，提供一个唯一名称文件夹-用于组织对象查询主题-映射到数据源（如：表）中相应对象并使用描述如何检索数据（如：列）的SQL语句。查询项-包含在一个查询主题中，映射到数据源中的一个列。关系-说明一个查询主题中数据与另一个查询主题中数据之前的关系。参数映射-在报表运行中，用参数创建允许替换的条件查询主题。包-包含查询主题的一个字集（或一个完整集）和项目中定义的其它对象；可发布为一个模型供报表开发者使用。7.项目作为一个文件夹出现在文件系统中，该文件夹含有一个项目文件.CPF和.xml文件。Cognos7.0,6.0都是.imr格式每张报表独立的文件。Model.xml用户创建的实际模型数据。Preferences.xml-framework Manager项目的参数。Customdata.xml存储的图表信息，例如：图表的布局，符号，字体，颜色。Repositiory.xml 添加到储库中的每个项目或片断的版本历史信息。FM的对象，有一个标识符，可以具有相同的名称，但是必须使用一个名字空间来唯一标识，标识符可以由一，二或三级组成。例如：ductcode名字空间，函数，名字空间快捷方式和文件夹快捷方式都有一个一级标识符例如：goSales其它对象都有一个两级标识符例如，查询主题：duct定义函数集在project下选择project function list就可以查询主题数据源查询主题是一个底层查询数据源视图的SQL查询，根据导入对象创建默认数据源查询主题。维表进行关联的字段就要设置成 Identifier，若没有关联而起到说明作用的则要将其定义为 Attribute 。如果是事实表的度量 , 就将其设置成 Fact模型查询主题含用基于模型中其它现有对象的查询项存储过程查询主题含有基于一个数据库存储过程返回列表的查询项目查询间关系-在查询主题上右键新建查询间关系relationship,查询间关联关系，来自数据源的查询可以直接在SQl上写where条件，来自模型的查询主题需要添加过滤器，可以加入宏参数，如数据特定权限，月结单，销售员只可以看自已的客户。宏参数也可以直接写在SQL的where条件里。创建多维模型Framework manager也能创建多维模型，该模型有别于transformer创建的多维数据集，这里的模型不包含数据，只是组织数据的一种方式，对于前端固定报表，如需向下钻取，可在framework manager中创建维度实现。1. 新建维度（regular dimension）每个层次都定义它的 BusinessKey 和 MemberCaption，它们可以是同一字段，也可以是不同的字段。需要注意到 MemberCaption 对应的字段必须是字符性，如果不是就必须进行转换，如进行当前年转换的例子。按照这样的方法建立好时间维对应的季、月和日的层次，然后把要用到的所有的维度都按照这样的方法建立起来。2. 创建度量。3. 将度量赋到维度层次上（右键点击度量ID选择“set scope”）。4. 在package中右键点击创建包。5. 发布数据包。Framework manager开发流程建立客户端到数据库的连接-导入数据对象-建立查询主题-模型打包发布其它常用选项设置三种SQL类型Cognos sql 模型默认的类型，当数据库表从源引入时默认。Cognos sql 支持多数据源，它不是当前数据库的标准sql ,但能与cognos良好结合，它对于不同的数据库是透明的。一般情况下不用改变这种模式。Native sql 是当前数据库sql , cognos 直接将sql 发送到当前数据库中运算，native sql 不支持多数据源，但运行效率较高。Pass-though 不通过任何sql 验证。一般比较少这样设置。生成IQD文件生成的IQD文件可以为transformer生成cube立方体模型提供数据源。Transformer 生成power cube的三种重要部分1. 源文件：源文件是查询，可以是结构（文本）或事物（数字）2. 维度图：一个维度图是一个显示模型中所有维度和他们包含层的表3. 指标:一个指标是用作企业主要性能指标的值。维度 表示数据的最高层，可以典型的说明谁，什么，什么时间，什么地方一类问题。在模型中的有效维度决定了应用能够回答问题的类型。层 表示维度数据逻辑上的等级关系（相当于数据的定位），每个维度层的等级规则确定了cube的钻取路径。类别 是位于一个维度中层上独立的数据元素（层中数据的具体取值）。最底层类别表示每个维度中最详细的数据元素。2.powerplayTransformer功能简介PPTF是cognos8中一个重要的组件，也是一个OLAP服务器，主要用来创建模型，组织数据，将不同数据源的数据整合到powerCube当中，形成多维数据源，而根据生成好的power Cube模型 ，analysis Studio便可在此基础上各种报，比较数据和发现业务发展趋势。TF模型 能够帮助将数据从各种数据源组织进多维的power Cube中，创建能比较数据和发现趋势的报表。在用TF建模前，首先要评测数据，以及确定如何构造它们，以使它们能够支持业务分析的类型；然后用TF构造创建数据的模型；最后生成一个POWERCUBE，让用户通过analysis studio直接查看和应用其中的数据。数据源(DataSource)窗格数据源窗格的作用是将数据集成到模弄中来，根据模型设计文档，从能够支持应用的数据仓库及数据库中确定要查询哪些数据，并将它们加载进数据源窗格。然后再将数据源窗格的数据用于建立维度图，并创建POWERCUBE。等在数据源窗格中可以有一个或多个数据源，每个数据源来自于一个数据源文件（如：.IQD等），每个数据源都有自已的数据源名，每个数据源由构成它的若干列（Columns）组成。维度图（dimension Map）窗格维度图窗格是一个用于建立多维立方体结构的工作区，根据数据源中的文本数据，各个维度以及每个维度下层的设定在此组织（维度和层），最底层要对应数据源窗格中的一个列。度量（measures）窗格度量窗格用于建立和显示模型所需要的度量，度量值是在powerPlay报表单元中可视的数据，根据模型设计文档，定义应用中需要的度量。模型中至少要用一个度量，度量与维度的最底一层关联。每个标准度量要在数据源中对应有一个列（计算度量除外）。PowerCube窗格PowerCube窗格显示用模型创建的PowerCube.2.1建立模型设计的方法建立模型设计时，应首先选择度量，然后选择能够支持度量的维度和层。方法步聚：1. 选好要说明的业务领域（尽量保持生成维度中的层数不超过5层）。2. 选择的维度与度量要匹配（尽量保持在5-7个，因为较少的维度可以使得应用更直观和使用）。3. 在生成power cube前，transformer模型中必须完成：a.集成数据源数据b.建立维度图c.定义需求的度量。4. 建立模型的步聚：制作IQD文件，建立多维模型，运行模型生成CUBE。模型打抱发布。.制IQD文件，IQD文件用于在多维模型中组织数据库，实际的作用就是将所需要的维表和事实表从数据库中读出来，成为生成cube的数据来源。IQD文件可以手写较为复杂的SQL语句，但如果是事实表IQD不建议这样做，IQD文件中最好只有简单的语句，不影响性能。也可以通过一个IQD模版手工编写IQD文件：a.第1、2行是固定语句b.第3行指明了到数据库的逻辑连接名c.第4行实际是表名d.第5行到第8行是IQD中的查询语句主体e.从第9行到结束是对查询出来的每个字段的说明。（以上为固定格式）也可通过FrameWork manager生成IQD文件，避免手写的劳动，由软件自动完成，可以避免出错的可能性。1.首先把查询对象的属性修改成IQD，2.然后发布模型时将是否生成IQD的选项的勾打上，并且选好存放IQD的路径。2.5建立多维模型2.5.1.定义数据据源连接，使用IQD作为多维模型的数据源首先要在cognos安装路径下的cognos.ini文件中正确定义到数据库的连接，连接字符串可以从cognos的portal上获得。打开cognos.ini文件，在文件内容后面添加逻辑数据源名称及“=”符号，然后把连接字符串拷入。2.5.2.添加数据源，添加IQD文件作为多维模型的数据源：点击菜单上的NEW按钮新建一个模型，输入模型名称和数据源名称后，在data source type中选择“impromptu query definition”,即IQD类型。接着输入IQD文件目录和名称。所添加的IQD分为维表IQD和事实表IQD。 维表IQD实际上就是对维表的查询语句，用来建立多维模型的维度，实际上就是搭出多维模型的架。 事实表IQD给出的就是多维模型中最细粒度的数据，实际上就是给多维模型中的measures提供数值。目前已经在实践中证明最不易出问题的做法就是：在维表中进行逐级分层，在事实表中给出维表中最细一层的明细数据，以事实表自动关联维表的最底层来生成cube。此中开发，生成cube的效率瓶颈主要会出现在事实表数据的生成阶段，因为事实表要满足多维模型的话就必须给出模型中所有维度的最明细数据，这需进行一系列数据抽取以及转换的过程，生成事实表所消耗的时间取决于数据量的大小和进行数据抽取转换运算的复杂程度。然后点击下一步，在弹出框中要求输入数据库的登录用户名和密码。最后完成。2.5.3.添加measure度量直接将事实表IQD中的度量拖到“measure”窗格。多维模型中的measure就来源于事实表IQD中的numeric型column。（维表和事实表在多维模型中能够自动正确关联的保证：维表的最低层字段名和事实表的维度字段名一致，事实表IQD放在所有维表IQD之后。）2.5.4建立层次维导入数据源和建立好度量后，建立模型的层次维：将数据源窗格中维表的字段拖到维度窗格，以机构为例，先将一级编码拖到维度窗格的阴影部分，再将二级编码拖到一级编码的后面，如此类推，从高层次到低层次往下拖。最后便生成一个层次结构的维。（对于日期维，把事实表中的日期字段直接拖入维度图窗格，transfer会自动生成一个层次结构的维。再右键点击自动生成的日期维）2.6模型的一些属性说明和设置2.6.1.numeric型column的设置2.6.2.rollup设置Regular rollup:用于定义在普通维度上进行聚合的运算。Time state rollup:用于定义在时间维度上的聚合运算，如果选择none(regular)则在时间维度上是汇总。2.6.3.设置数值的格式对数据显示格式进行设置。2.6.4添加cube文件右键点击PowerCube窗格，选择“insert PowerCube”.添加cube文件后的主要设置：选择cube文件在本地保存的路径，如果是本地生成cube,要在processing里面选择“locally”;如果cube是要在服务器上生成的话，就要选择“on the server”。（增理更新cube需的额外设置：在插入cube后，要在cubegroup里面配置dimension及level,进行自动关联所需要的配置-unique）2.6.5维度内容显示配置选择always include表示在生成cube的时候，事实表和维表里面的纬度数据都会存生，选择when needed表示只有事实表里纬度存在的数据在生成cube的时候才会存在。2.7运行模型生成cube在windows客户端上生成cube2.8 cube的权限设置2.8.1导入角色1.点击powerplayer transformer的file 后选择model properties2.8.2 权限设计1.选中维度窗口后，右键。2. 选择show diagram。3.选择user classes，并且展现角色，然后选 择use custom view.4.选择use custom view 后就可以对该维表进行权限设置。选择你要去掉的机构（该角色的用户不应该看到的机构）后，选择差X，就可以啦。2.9 cube增量更新1.全量加载：即在一定过滤条件下一次性加载所有数据，该方式加载速度较慢，但最终形成cube的访问效率较高。2.增量加载：即在一定过滤条件下读取新数据，并将数据堆积在原有立方体上。该方式加载速度较快，但最终形成的cube的访问效率会逐渐变低。a.增量加载的两种基本方式（cube group）：在模型中添加一个新的cube时设置cube group 选项，使得最终的cube按照日期（也可以是其它维度，选择日期较为常见）进行分区。该方式将会按cube group 设置时所选择的维度生成一批子cube，cognos 将这些子cube用.vcd文件映射到最终的大cube上面，我们的报表的数据源虽然是这个大cube，但是实际上最终访问的是那些独立存在的以所选维度为名称的子cube。优点便于进行cube增量加载异常的后续处理。如按日期，发现增量加载了从年初到5月1日的数据，但5月2日发现4月20-25号的数据有问题，那么这时就要进行异常处理。一般方式是将20110420.mdc20110425.mdc这些子cube删除，然后手工修改iqd文件（添加where条件将时间限制在4月20日到25日），执行增量mdl模型或者脚本，重新加载这段数据的时间。该方式下生成的每个子cube都是独立的，而且都是可以实际访问的，从另一方面来讲，它们都是可以独立生成和处理的。缺点是：占用空间会越来越大，而且会比普通方式大很多倍，该方式下，由一个mdl模型生成的最终文件包括：一个大的cube文件，一个vcd文件（含有大cube和所有子cube的映射信息）、一个含用所有子cube的文件夹。这些文件会越来越庞大，要注意空间问题。b. Enable time-based partitioning(基于时间的分区)方式：此时可以使用普通的cube，然后在cube的属性对话框中的processing选项里面选中“this cube is incrementally updated”,即可按照增量更新。但需要注意的是，transformer 本身不能分辨哪些是增量数据，因此每次加载前必须提供增量部分数据给transformer（有两种方式：iqd访问的表中含用增量数据；或者iqd中的sql时面人为的通过where条件数据来源）优点是：方法简捷，最终生成的结果性文件占用空间小，而且中间处理的步骤相对简单。缺点：异常处理只能通过全量加载的方式进行，效率较低。如果出错，只能将原来的cube删除，来一次全量加载。MIS系统cube增量加载方案基于对处理方便性、效率、空间等各方便的考虑，MIS系统cube增量加载采用enable time based partitioning(基于时间的分区)的方式，基主要思想如下：1. IQD文件：对于同一个查询主题来讲，其事实表对应的IQD文件应分为两个，一个是支持增量加载的，另一个是支持全量加载的。支持增量加载的IQD文件中包含了用于提取增量数据的where 条件语句，而支持全量加载的IQD文件则不含上述语句。2. MDL文件：对于同一个查询主题来讲，多维分析MDL文件也应分为两个，一个是支持增量加载的，另一个是支持全量加载的。支持增量加载的MDL包含支持增量加载的事实表IQD，而且其中的cube属性里面的processing 选项卡中要选上“this cube is incrementally updated”;支持全量加载的MDL包含支持全量加载的事实表IQD，并且保证cube属性里面的processing选项卡中的“this cube is incrementally updated”未被选上。3. 全量加载方式到增量加载方式的过渡：首先用支持全量加载的MDL生成MDC文件；保存该MDL；将支持全量加载的MDL另存一份；将上述新另存的MDL中支持全量加载的事实表IQD换为支持增量加载的事实表IQD；将上述新另存的MDL中CUBE属性里面的processing选项卡中的”this cube is incrementally updated ”选上；保存该MDL；以后用新另存的MDL在原有MDC基础上加载增量数据生成新的MDC文件即可。4. 异常处理：当有一天发现cube加载出现异常（数据质量问题，或者由于其它原因导致的cube加载失败等），我们需要进行异常处理，基本步骤为：将原来的cube删除；然后直接进行全量加载；之后的步骤同3.5. 注意事项：增量加载过程中切不可改变上次增量加载已经形成的mdl，包括手工或者命令方式生成新的维度或者categories， 不然会出现不明异常，最终导致增量加载失败。总结和建议1. 现有的cube对基础数据实行全量加载，数据量大用增量方式加载。另外由于增量加载形成的cube的访问效率会逐渐降低，因此，在增量加载到一定长的时间（比如说一年）之后，要进行一次调整，即进行一次全量加载，然后再在此基础上进行增量加载。这样会提高cube的访问效率。就是所谓的增量加载一段时间之后进行一次全量加载的混合型加载方式。2.10模型抱包发布使用Framework发布cube模型。发布cube前必须先建立到cube数据源的连接。在建立数据源连接时我们选择“cognos powercube”类型的数据源，接着输入cube立方体文件的存放路径及访问密码。最后测试 测试成功能点击确定，成功建立好cube数据源连接后在framework建立模型时选择该连接，在选择”run metadata wizard”时看到cube数据源。然后打包发布。常见问题处理1. cube 是否正常定时更新？看计划任务显示是否显示正常执行：c:cognossourcecube发布存放的文件夹，看文件的时间是否更新。2. cube的数据源能否正常连接？如果数据源没配置正确，无法连接，会导到致cube无法更新。3. 怎么进一步查错？c:cognossourcelogs和c:cognossourcetrnsfrmrlog文件夹为多维分析的日志，如果哪个分析没有正常更新或者其它问题，查看日志文件。1. So, tell me a little about yourself.You dont need to explain everything from birth to present day. Relevant facts about education, your career and your current life situation are fine.2. Why are you looking (or why did you leave you last job)?This should be a straightforward question to answer, but it can trip you up. Presumably you are looking for a new job (or any job) because you want to advance your career and get a position that allows you to grow as a person and an employee. Its not a good idea to mention money here, it can make you sound mercenary. And if you are in the unfortunate situation of having been downsized, stay positive and be a*rief as pos*le about it. If you were fired, youll need a good explanation. But once again, stay positive.3. Tell me what you know about this company.Do your homework before you go to any interview. Whether it*eing the VP of marketing or the mailroom clerk, you should know about the company or business youre going to work for. Has this company been in the news lately? Who are the people in the company you should know about? Do the background work, it will make you stand out as someone who comes prepared, and is genuinely interested in the company and the job.4. Why do you want to work at X Company?This should be directly related to the last question. Any research youve done on the company should have led you to the conclusion that youd want to work there. After all, youre at the interview, right? Put some thought into this answer before you have your interview, mention your career goals and highlight forward-thinking goals and career plans.5. What relevant experience do you have?Hopefully if youre applying for this position you have bags of related experience, and if thats the case you should mention it all. But if youre switching careers or trying something a little different, your experience may initially not look like its matching up. Thats when you need a little honest creativity to match the experiences required with the ones you have. People skills are people skills after all, you just need to show how customer service skills can apply to internal management positions, and so on.6. If your previous co-workers were here, what would they say about you?Ok, this is not the time for full disclosure. If some people from your past are going to say youre a boring A-hole, you dont need to bring that up. Stay positive, always, and maybe have a few specific quotes in mind. “Theyd say I was a hard worker” or even better “John Doe has always said I was the most reliable, creative problem-solver hed ever met.”7. Have you done anything to further your experience?This could include anything from night classes to hobbies and sports. If its related, its worth mentioning. Obviously anything to do with further education is great, but maybe youre spending time on a home improvement project to work on skills such as self-sufficiency, time management and motivation.8. Where else have you applied? This is a good way to hint that youre in demand, without sounding like youre whoring yourself all over town. So, be honest and mention a few other companie*ut dont go into detail. The fact that youre seriously looking and keeping your options open is what the interviewer is driving at.9. How are you when youre working under pressure?Once again, there are a few ways to answer thi*ut they should all be positive. You may work well under pressure, you may thrive under pressure, and you may actually PREFER working under pressure. If you say you crumble like aged blue cheese, this is not going to help you get your foot in the door.10. What motivates you to do a good job?The answer to this one is not money, even if it is. You should be motivated by lifes noble pursuits. You want recognition for a job well done. You want to become better at your job. You want to help others or be a leader in your field.11. Whats your greatest strength?This is your chance to shine. Youre being asked to explain why you are a great employee, so dont hold back and stay do stay positive. You could be someone who thrives under pressure, a great motivator, an amazing problem solver or someone with extraordinary attention to detail. If your greatest strength, however, is to drink anyone under the table or get a top score on Mario Kart, keep it to yourself. The interviewer is looking for work-related strengths.12. Whats your biggest weakness?If youre completely honest, you may be kicking yourself in the butt. If you say you dont have one, youre obviously lying. This is a horrible question and one that politicians have become masters at answering. They say things like “Im perhaps too committed to my work and dont spend enough time with my family.” Oh, theres a fireable offense. Ive even heard “I think Im too good at my job, it can often make people jealous.” Please, lets keep our feet on the ground. If youre asked this question, give a small, work-related flaw that youre working hard to improve. Example: “Ive been told I occasionally focus on details and miss the bigger picture, so Ive been spending time laying out the complete project every day to see my overall progress.”13. Lets talk about salary. What are you looking for?Run for cover! This is one tricky game to play in an interview. Even if you know the salary range for the job, if you answer first youre already showing all your cards. You want as much as pos*le, the employer wants you for as little as youre willing to take. Before you apply, take a look at for a good idea of what someone with your specific experience should be paid. You may want to say, “well, thats something Ive thought long and hard about and I think someone with my experience should get between X & Y.” Or, you could be sly and say, “right now, Im more interested in talking more about what the position can offer my career.” That could at least buy you a little time to scope out the situation. But if you do have a specific figure in mind and you are confident that you can get it, Id say go for it. I have on many occasions, and every time I got very close to that figure (both below and sometimes above).14. Are you good at working in a team?Unless you have the I.Q. of a houseplant, youll always answer YES to this one. Its the only answer. How can anyone function inside an organization if they are a loner? You may want to mention what part you like to play in a team though; its a great chance to explain that youre a natural leader.15. Tell me a suggestion you have made that was implemented.Its important here to focus on the word “implemented.” Theres nothing wrong with having a thousand great ideas, but if the only place they live is on your notepad whats the point? Better still, you need a good ending. If your previous company took your advice and ended up going bankrupt, thats not such a great example either. Be prepared with a story about an idea of yours that was taken from idea to implementation, and considered successful.16. Has anything ever irritated you about people youve worked with?Of course, you have a list as long as your arm. But you cant say that, it shows you a*eing negative and difficult to work with. The best way to answer this one is to think for a while and then say something like “Ive always got on just fine with my co-workers actually.”Use this question as a chance to show that you are a team player: “The only people I have trouble with are those who arent team players, who just dont perform, who complain constantly, and who fail to respond to any efforts to motivate them.” The interviewer is expecting a response focused on personality and personal dislikes. Surprise her by delivering an answer that reflects company values17. Is there anyone you just could not work with?No. Well, unless youre talking about murderers, racists, rapists, thieves or other dastardly characters, you can work with anyone. Otherwise you could be flagged as someone whos picky and difficult if you say, “I cant work with anyone whos a Broncos fan. Sorry.”18. Tell me about any issues youve had with a previou*oss.Arrgh! If you fall for this one you shouldnt be hired anyway. The interviewer is testing you to see if youll speak badly about your previous supervisor. Simply answer this question with exteme tact, diplomacy and if necessary, a big fat loss of memory. In short, youve never had any issues.The answer to 18 is completely wrong. I am a director at a major media companys interactive division. Our company is expanding and I am almost in a constant state of hiring. I ask a variation of this question in every single interview and if a candidate has never had one issue or disagreement with anyone, (I stated a variation: I ask if it has happened with anyone in the workplace) I peg them as a liar and reject them immediately.I went well with my previou*oss. If there is an conflict, I will be open mind