busines inventory

Important Calculated members

Date Range- This is a good one WITH SET [MyRange] AS '{[Time].[2009].&[5].[22].[26] : [Time].[2009].&[6].[26].[24]}' MEMBER [Time].[Range] AS 'Aggregate([MyRange],[Measures].CurrentMember)' select {[Measures].[Sends],[Measures].[Views], [Measures].[Clicks]} on columns, {[Zone Id].&[142]} on rows from [All Communication Analysis] where ([Time].[Range]) This is an OR query. I got this query from HERE SELECT [Measures].[Internet Sales Amount] ON 0 FROM [Adventure Works] WHERE( {([Date].[Calendar Year].&[2003],[Customer].[Country].[All Customers]) , ([Date].[Calendar Year].[All Periods],[Customer].[Country].&[United States])}) WITH SET [Top5Camps] AS ' TopCount({Descendants( [Zone Id].&[14],3)},200,([Measures].[Sends])) ' select {[Measures].[Sends],[Measures].[Views],[Measures].[Clicks],[Measures].[Clicks Text],[Measures].[Clicks Html],[Measures].[Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count]} on Columns, [Top5Camps] DIMENSION PROPERTIES [Zone].[uniq views],[Zone].[uniq soft bounces], [Zone].[uniq hard bounces],[Zone].[uniq clicks html], [Zone].[uniq clicks text],[Zone].[uniq clicks],[Zone].[uniq bounces], [Zone].[Campaign Launch Date],[Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY on rows from [Communication] --Some of these below use custom code in reporting services. The queries are formed in visual basic. Maybe someday I'll take the time to make these real queries. ="select {[RunningTotalSubs]} on Columns, CrossJoin(TopCount( { [DMA].children}, 5000, ([RunningTotalSubs])), {[Time].[2004].&[1].[1].[1], [Time].[2005].&[1].[1].[1],[Time]}) on rows from [consumers] where ( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "])" WITH MEMBER [Measures].[Variant Percentage] AS '(( [Time].[2004].&[1].[1].[1], [RunningTotalSubs] ) -( [Time].[2005].&[1].[1].[1], [RunningTotalSubs] ))/( [Time].[All Time], [RunningTotalSubs] ) ' select {[RunningTotalSubs],[Measures].[Variant Percentage] } on Columns, CrossJoin(TopCount( { [DMA].children}, 5000, ([RunningTotalSubs])), {[Time].[2004].&[1].[1].[1], [Time].[2005].&[1].[1].[1],[Time]}) on rows from [consumers] where ( [Zone Id].&[14]) WITH SET [Top5Camps] AS ' Descendants([Zone Id].&[14].&[74],2) ' SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} ="select { { ([Age Group].[All Age Group], [Gender].[All Gender]) },{ ([Age Group].[All Age Group], [Gender].[Male]) },{ ([Age Group].[All Age Group], [Gender].[Female]) },{ ([Age Group].[All Age Group], [Gender].[Unknown]) },{ ([Age Group].[13 and 17], [Gender].[All Gender]) },{ ([Age Group].[18 and 24], [Gender].[All Gender]) },{ ([Age Group].[25 and 34], [Gender].[All Gender]) } ,{ ([Age Group].[35 and 44], [Gender].[All Gender]) },{ ([Age Group].[45 and 54], [Gender].[All Gender]) },{ ([Age Group].[over 55], [Gender].[All Gender]) },{ ([Age Group].[Unknown], [Gender].[All Gender]) } } on Columns, NON EMPTY { TopCount( { [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].children}, 10, ([RunningTotalSubs])) } on Rows from [consumers] where ("& iif(Parameters!dt.Value="d",Code.getCubeDate(Parameters!p1.Value, Parameters!dt.Value),"[Time]") &",[RunningTotalSubs])" ="select { { ([Age Group].[All Age Group], [Gender].[All Gender]) },{ ([Age Group].[All Age Group], [Gender].[Male]) },{ ([Age Group].[All Age Group], [Gender].[Female]) },{ ([Age Group].[All Age Group], [Gender].[Unknown]) },{ ([Age Group].[13 and 17], [Gender].[All Gender]) },{ ([Age Group].[18 and 24], [Gender].[All Gender]) },{ ([Age Group].[25 and 34], [Gender].[All Gender]) } ,{ ([Age Group].[35 and 44], [Gender].[All Gender]) },{ ([Age Group].[45 and 54], [Gender].[All Gender]) },{ ([Age Group].[over 55], [Gender].[All Gender]) },{ ([Age Group].[Unknown], [Gender].[All Gender]) } } on Columns, NON EMPTY { TopCount( { [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].children}, 10, ([RunningTotalSubs])) } DIMENSION PROPERTIES [Zone Id].[Campaign Type Id].[Key], [Zone Id].[Campaign Type Id].[Name] on Rows from [consumers] where ("& iif(Parameters!dt.Value="d",Code.getCubeDate(Parameters!p1.Value, Parameters!dt.Value),"[Time]") &",[RunningTotalSubs])" ON COLUMNS, Order([Top5Camps], [Top5Camps].currentmember.properties("Campaign Launch Date") ,desc) DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY ON ROWS from [All communication analysis] WITH SET [Top5Camps] AS ' Descendants([Zone Id].&[14].&[74],2) ' SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY ON ROWS from [All communication analysis] ="WITH SET [Top5Camps] AS ' STRTOSET(IIF(ISERROR(STRTOVALUE(""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "]"")), ""[Zone Id].&[3].&[45].&[4311].Children"", ""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].Children"")) ' ="select {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} on Columns, [Top5Camps] on rows from [All Communication Analysis]" ="WITH SET [Top5Camps] AS ' STRTOSET(IIF(ISERROR(STRTOVALUE(""[Zone Id].), ""[Zone Id].&[3].&[45].&[4311].Children"", ""[Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].Children"")) ' ="select {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} on Columns, [Top5Camps] on rows from [All Communication Analysis]" ="WITH SET Top5Camps AS ' Descendants( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "], 3)' SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], MEMBER_CAPTION, MEMBER_KEY ON ROWS from [All communication analysis]" type Exception report message description The server encountered an internal error () that prevented it from fulfilling this request. exception ="WITH SET Top5Camps AS ' Descendants( [Zone Id].&[" & Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) & "].&[" & Code.EDecrypt(Parameters!PId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "].&[" & Code.EDecrypt(Parameters!CTId.Value,Code.EDecrypt(Parameters!ZoneId.Value,Convert.ToString(20)) ) & "], 1)' SELECT {[Measures].[Sends],[Measures].[S Sends],[Measures].[Views],[Measures].[Uniq Views],[Measures].[Clicks],[Measures].[Uniq Clicks],[Measures].[Clicks Text],[Measures].[Uniq Clicks Text],[Measures].[Clicks Html],[Measures].[Uniq Clicks Html],[Measures].[Bounces],[Measures].[Uniq Bounces],[Measures].[Soft Bounces],[Measures].[Hard Bounces],[Measures].[Cliks To Buy],[Measures].[Url Count],[Measures].[F2f Count]} ON COLUMNS, [Top5Camps] DIMENSION PROPERTIES [Zone].[Campaign Launch Date], [Zone].[Url Count], MEMBER_CAPTION, MEMBER_KEY ON ROWS from [communication ]" One link I came accross when using deminsion properties. Try not to make queries with properties because they are slow. But if you have to: http://www.sqlservercentral.com/blogs/mysqllearnings/archive/2009/04/15/key-column-of-a-dimension-in-ssas.aspx Each product has a weight associated with it. The requirement is to filter all the products whose weight is 5. To achieve this, weight should be the member property of the product. This time, it cannot be made as a key column. But, again instead of using ".properties" function, functions like "EXISTS" can be used. A general way of writing this would be Filter ([Prod Dim].[Prod Hier].[SKU Lvl].members,[Prod Dim].[Prod Hier].[SKU Lvl].currentmember.properties("Weight") = 5) In this case, it has to filter each member by getting the property of each member and checking whether the weight is less than 5. Instead of using the ".properties" function, it can be written using "EXISTS" function. Exists ([Prod Dim].[Prod Hier].[SKU Lvl].members,filter([Prod Dim].[Weight].[Weight].members,[Prod Dim].[Weight].currentmember is [Prod Dim].[Weight].[5])) Also, it is better to use the function "IS" instead of "=" because IS function eliminates the conversion of the given string to a member. Because of doing this, there was a considerable improvement in the query performance. These were the scenarios where I felt natural key was useful when specified as the key column.

Important Calculated members

Date Range- This is a good one WITH SET [MyRange] AS '{[Time].[2009].&[5].[22].[26] : [Time].[2009].&[6].[26].[24]}' MEMBER ...

3:14 AM

MDX Calculations

WITH MEMBER [Measures].[SumOfAG] AS '( Sum({[Time]}, [Measures].[RunningTotalSubs]))' MEMBER [Measures].[SuperSum] AS ' (Sum({[Age Group].[13 and 17],[Age Group].[18 and 24],[Age Group].[25 and 34],[Age Group].[35 and 44],[Age Group].[45 and 54],[Age Group].[over 55],[Age Group].[less then 13],[Age Group].[UNKNOWN] },[Measures].[SumOfAG] ))' MEMBER [Measures].[GMale] AS '( Sum({([Time],[Gender].[Male])}, [Measures].[RunningTotalSubs]))' MEMBER [Measures].[GMalePer] AS ' ([Measures].[GMale] / [Measures].[SuperSum])' MEMBER [Measures].[GFemale] AS '( Sum({([Time],[Gender].[Female])}, [Measures].[RunningTotalSubs]))' MEMBER [Measures].[GFemalePer] AS ' ([Measures].[GFemale] / [Measures].[SuperSum])' MEMBER [Measures].[GUnknown] AS '( Sum({([Time],[Gender].[Unknown])}, [Measures].[RunningTotalSubs]))' MEMBER [Measures].[GUnknownPer] AS ' ([Measures].[GUnknown] / [Measures].[SuperSum])' select {[Measures].[SumOfAG], [Measures].[GMale], [Measures].[GMalePer], [Measures].[GFemale], [Measures].[GFemalePer], [Measures].[GUnknown], [Measures].[GUnknownPer] } on Columns, { ([Age Group].children) } on Rows from [consumers] where ([Zone Id].&[501]) WITH MEMBER [Measures].[p1] AS '( [Time].[2008].&[7].[31].[30], [Subs Count] ) ' MEMBER [Measures].[p2] AS '( [Time].[2008].&[6].[27].[30], [Net Subs] ) ' MEMBER [Measures].[Variant Percentage] AS '(( [Time].[2008].&[7].[31].[30], [Net Subs] ) -( [Time].[2008].&[6].[27].[30], [Net Subs] ))/( [Time].[2008].&[6].[27].[30], [Net Subs] ) ' MEMBER [Measures].[SubsDiff] AS '(( [Time].[2008].&[7].[31].[30], [Net Subs] ) -( [Time].[2008].&[6].[27].[30], [Net Subs] )) ' select {[Subs Count], [Net Subs],[RunningTotalSubs],[Measures].[p1], [Measures].[p2], [Measures].[SubsDiff], [Measures].[Variant Percentage] } on columns, TopCount(NonEmptyCrossJoin({Descendants([Source].Currentmember, 3)},{Descendants([Zone Id].&[1178],2)}) ,150, ([Net Subs])) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_KEY on rows FROM [consumers]

MDX Calculations

WITH MEMBER [Measures].[SumOfAG] AS '( Sum({[Time]}, [Measures].[RunningTotalSubs]))' MEMBER [Measures].[SuperSum] AS ' (Sum({[A...

3:05 AM

BIusiness Intelligence over Microsoft Office Excel

Microsoft Office Excel As one of the most widely used Microsoft Office products, the Excel spreadsheet program is designed to organize, analyze, and visualize data. Excel is one of the most powerful desktop applications in the Microsoft BI arsenal. An analysis tool for everyone Excel is such a popular data tool that most of the client organizations I visit use it to run some critical portion of their business. One good reason is that Excel can be installed on a local computer with no need for administrators and servers. The Data Mining Add-in Microsoft creates Add-ins (new sets of capabilities) as a way to expand what its products can do; the Data Mining Add-in allows the Excel program running on your local computer to serve as a data-mining resource for SQL Server Analysis Services. You can run SSAS Data Mining algorithms using data that resides in Excel cells to yield important information about your business. Microsoft Office Visio The general idea behind Visio is to create flow charts — and to publish these documents to the Web as interactive diagrams with drill-down capabilities (users can click their way down to specific data). Microsoft offers a Data Mining Add-In for Visio that allows users to create interactive documents with real inlaid data. For example, a decision tree can be published to the Web with actual business data built in. When users go to the Web site containing that document, they can click a decision to view its results.

BIusiness Intelligence over Microsoft Office Excel

Microsoft Office Excel As one of the most widely used Microsoft Office products, the Excel spreadsheet program is designed to organize, anal...

6:27 AM

Data mining

Data mining

Computers can be programmed to sort through enormous amounts of data looking for patterns. It’s an exciting new frontier that goes by many d...

1:27 AM

Analyzing data

As you can imagine, the amount of data contained in a modern business is enormous. If the data were very small, you could simply use Microsoft Excel and perform all of the ad-hoc analysis you need with a Pivot Table. However, when the rows of data reach into the billions, Excel is not capable of handling the analysis on its own. For these massive databases, a concept called OnLine Analytical Process (OLAP) is required. Microsoft’s implementation of OLAP is called SQL Server Analysis Services (SSAS), which I cover in detail in Chapter 8. If you’ve used Excel Pivot Tables before, think of OLAP as essentially a massive Pivot Table with hundreds of possible pivot points and billions of rows of data. A Pivot Table allows you to re-order and sum your data based on different criteria. For example, you may want to see your sales broken down by region, product, and sales rep one minute and then quickly re-order the groupings to include product category, state, and store. In Excel 2010 there is a new featured called PowerPivot that brings OLAP to your desktop. PowerPivot allows you to pull in millions of rows of data and work with it just like you would a smaller set of data. After you get your Excel sheet how you want it, you can upload it to a SharePoint 2010 site and share it with the rest of your organization. With PowerPivot you are building your own Cubes right on your desktop using Excel. If you use PowerPivot, you can brag to your friends and family that you are an OLAP developer. Just don’t tell them you are simply using Excel and Microsoft did some magic under the covers. When you need a predefined and structured Cube that is already built for you, then you turn to your IT department.

Analyzing data

As you can imagine, the amount of data contained in a modern business is enormous. If the data were very small, you could simply use Microso...

1:14 AM

Integrating data from many sources

Integrating data from many sources

The many different systems and processes that make up an organization create data in all shapes and forms. This data usually ends up stored ...

1:11 AM

Reporting on data

Reporting on data

When you have a Data Warehouse, you likely don’t want to look at rows and rows of data; instead, you want to visualize the data and give it ...

1:08 AM

Data warehousing and data marts

Although computer systems help solve many problems in business, they use so many different kinds of programs that they can’t always communicate easily with each other. A tremendous number of systems make up a modern organization — payroll, accounting, expenses, time, inventory, sales, customer relations, software licensing, and so on. Many of these systems have their own databases and ways of storing data. Combining data from the tangle of systems — let alone doing something useful with the combined data — becomes extremely difficult. Business intelligence creates a “big picture” by storing and organizing data from many disparate systems in one usable format. The idea is to make the data readily accessible for reporting, analysis, and planning. A data warehouse is a central database created for just that purpose: making the data from all those sources useful and accessible for the organization. The idea is to give decision-makers the information they need for making critical business decisions. A data mart is a more specialized tool with a similar purpose; it’s a functional database that pulls particular information out of the overall Data Warehouse (or even directly from source systems depending on who you ask) to answer specific queries. For example, a manufacturing location may need to compile some specialized data unique to the process used to make a particular product. The overall data warehouse is too big and complex do that job (or to modify effectively to handle it), so a smaller version — in BI lingo, a data mart — can be created for this one manufacturing location. The Microsoft SQL Server Database Engine manages not only data warehouses, but also data marts — and both types of data storage can become massive. Fortunately, SQL Server addresses this problem by storing one database across a cluster of many different servers. This approach accommodates the enterprise as it grows in scale.

Data warehousing and data marts

Although computer systems help solve many problems in business, they use so many different kinds of programs that they can’t always communic...

1:03 AM

Subscribe to: Posts ( Atom )