Wednesday, September 7, 2016

SSAS 2012 Tabular Cube:Attributes(columns) not visible

Issue is where your table in the model diagram has both Measure and Attributes and you add a new column to the table in model diagram.
After Cube deployment it only shows the Measures in the Client tool like Excel.
It did not show the new attribute which was added to the model table later.

Solution is to set the "Hidden" property on the properties windows of the newly added Attribute(Column) to "True" and click save and then change it to "False" again.

And now if you deploy then the new columns will show up as dimensions.
There is some bug so it doesnot take up the correct way when column added .



Friday, May 27, 2016

Convert Views to Tables

Recently I wanted to create a StoredProcedure to convert all the views which read millions of records to tables in a database.

The Reason being when we process a cube its always better if it reads from a simple view or a table.
So the ETL(SSIS) job will execute the view which is complicated and has many joins and look ups to a table which can be easily converted to a table and it can be read into the cube.

Here is code to convert all views ending with "_ToTable" to table with same name.
Example : dbo.SalesOrder_ToTable view will be converted to dbo.SalesOrder so having the same schema name (.dbo) as the view .

GO
DECLARE @View_Name varchar(255)

DECLARE MY_CURSOR CURSOR
  LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT SCHEMA_NAME(schema_id) +'.'+name AS view_name
FROM sys.views where name like '%_ToTable%'

OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @View_Name
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Tablename_Name varchar(255)
Set @Tablename_Name = Replace(@View_Name,'_ToTable','')
IF OBJECT_ID(@Tablename_Name, 'U') IS NOT NULL  Exec('DROP TABLE '+@Tablename_Name)
Exec('Select * into '+@Tablename_Name+' from '+@View_Name )
    FETCH NEXT FROM MY_CURSOR INTO @View_Name
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

Monday, February 29, 2016

SSAS: Dimension Parent Child Hierarchy recursive

In the last post I showed you know to build a Parent-Child Hierarchy. This time we implement the same Parent-Child Hierarchy in a different way. Building a Parent Child Hierarchy looks complicated but is easy if all the rules are followed correctly. I will here list down few steps which will help us building it better.

Scenario:
As you see below we have a simple table which you can build using SSIS and map the corresponding Parent and Child values.
Portfolio:
Primary Column With unique IDs.
ParentPortfolio:
Portfolio Id of the corresponding Parent.
RollUp:
Operator used while aggregrating values(Auto Sum) from Child to Parent node.
You can read more on RollUps here
https://msdn.microsoft.com/en-us/library/ms175417.aspx

Example: P0120 has Parent P0200 and P0200 has Parent as P0500


Step1:
 You can create a New SSAS Project and create the DataSourceView.
 After importing the table in the DataSource View we need to set the primary key.
Here Portfolio is set as Primary key.
 After that a link is created between Portfolio and ParentPortfolio as shown below.



Step2:
Right Click "Dimension" folder in the Project in Visual Studio and Click "New Dimension".
The Dimesnion wizard opens up as shown below.
Select Portfolio Column as Key Column and for display we show the ID+Name combination which is already  there in the Description Column.


Step3:
Select Portfolio and ParentPortfolio columns. You can add more columns like Name later on.


Step4:
Name it "Portfolio".


Step5:
Process the Dimension. The output is shown below.


Step6:
Now we need to get rid of the Unknown member and set Rollup operator. For the Portfolio Dimension set the following Properties:


Step7:
For Parent Portfolio Attribute set the following Properties:


Step 8:
Process the Dimension:
The Unknown Level will disappear and all the Levels will have the Unary Rollup sign.


Step 9:
Rename "Portfolio" attribute to "Portfolio Flat" and "Parent Portfolio" to Portfolio.
The reason we do is because I want to name my hierarchy attribute as "Portfolio".
Its helps a lot and is very user friendly for the EndUser.


This is how it should look like:

Thursday, February 11, 2016

SSAS Dimension: Parent Child Hierarchy

Building a Parent Child Hierarchy looks complicated but is easy if all the rules are followed correctly. I will here list down few steps which will help us building it better.

Scenario:
As you see below we have a simple table which you can build using SSIS and map the corresponding Parent and Child values.

Account:
Primary Column With unique IDs.
ParentAccount:
Account Id of the corresponding Parent.
RollUp: Operator used while aggregrating values(Auto Sum) from Child to Parent node.
You can read more on RollUps here
https://msdn.microsoft.com/en-us/library/ms175417.aspx

Example: 101010 has Parent 109999 and 109999 has Parent as 139999



 Step1:
 You can create a New SSAS Project and create the DataSourceView.
 After importing the table in the DataSource View we start creating the Dimension.
Right Click "Dimension" folder in the Project in Visual Studio and Click "New Dimension".
The Dimesnion wizard opens up as shown below.

Select Account Column as Key Column and for display we show the ID+Name combination which is already  there in the Description Column.


Step2:
Select Account and ParentAccount columns. You can add more columns like Name later on.


Step3:
Name it "Accounts".


Step4:
Rename "Account" attribute to "Account Flat" and "Parent Account" to Account.
So going forward Account attribute is "Parent Account".
The reason we do is because I want to name my hierarchy attribute as "Account".
Its a lot User friendly for the EndUser.


This is how it should look like:


Step5:
For the Account(orginally which was Parent Account) attribute set the following Properties:
usage:Parent.


Step6:
In the attribute relationships Windows delete the first relation shown below.


This is how it should look like:


Step7:
Set the "Account Flat" attribute as Key Attribute.


Step7:
Process the Dimension so we see how the hierarchy looks like now.


Step8:
Set following Properties:
MembersWithData:NonLeafDataHidden.
UnaryOperatorColumn:Account.Rollup


Step9:
Process the Dimension again and you would see the following structure which you can use it in your BI reports.


Thursday, June 11, 2015

Bug: SQL 2014 SP1 truncates decimal values.

Hi ,
I noticed a wierd thing with SQL Server ServicePack 1 SQLServer2014SP1-KB3058865-x64-ENU.exe from
https://www.microsoft.com/en-us/download/details.aspx?id=46694

After installation when I executed a SQL query against a Linked Oracle Server I got the decimal values truncated and it only returned the value before the decimal seperator..it was not rounding off either. So a value like -2035.7654 was returned as -2035













After uninstalling the Service Pack1  I re-ran the same SQL Query and it started returning numbers with decimal seperator -2035.7654.

I hope Microsoft fixes it soon.

Tuesday, November 27, 2012

UnExpected System Error:New DataDource in DashBoad Designer

Hi,
You might see this Error when you try to create your First Business Intelligence DashBoard using DashBoard Designer.























So how to resolve this issue.
Check all these Steps if you have performed correctly
  1. Do the following Central Administration >> Application Management >> Manage Service Application, and after that you will need to double click Secure Store Application followed by Generate New Key.
  2. Also do the following Central Administration >> Application Management >> Manage Service Application >> PerformancePoint Service Application >> Manage, and then choose Update the PerformancePoint Unattended Service Account which is the first option.
  3. This step has to be successful, and in the event that it fails you will have to get rid of the PerformancePoint Service Application and after that you will have recreate it.Do the following:Central Administration >> Application Management | Manage Service Application >> PerformancePoint Service Application >> Administrator, and then Add the Unattended Service Account as well as Mark Full Control.
  4. PerformancePoint Services Application.Check if you see the performance point service application listed in the secure store service screen.
  5. Ensure that the account running performance point services in Step3 has access to the site collection where you are trying to deploy the dashboards.
  6. The credentials that you use to connect to the site using dashboard designer needs to be a site collection admin on the site collection.
  7. The Account in Step3 has access to the Content Database of the BI Site Collection.


Wednesday, November 21, 2012

Installing Oracle 11g Client on Windows 2008 R2 64 bit to use in SSIS

Step 1: Download the 32 Bit and 64 Bit Client from the following location. http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html




Step 2: Install 64Bit Version of the Client first and repeat the same Steps for 32 Bit Client.
Here I am only showing screenshots for 32 Bit installation.Select Custom as shown in the ScreenShot below.















Step 3: Select the language you can to use in the Client tools.















Step 4: Specify the path where you want to Install the Oracle Client. Here I have selected D:\Oracle . You should use same path for 64 Bit Client Installation also. I have shown a summary for 64 Bit Client in Step 7 so its easy to understand on how to configure 64 Bit Client.















Step 5: Select all componenets expect Oracle Scheduler Agent.















Step 6: Port No by default is 2030. Keep it the default one. I selected 2031 because I had installed wrong version of Oracle Client before which used up the Port 2030 :) .















Step 7: Here is the Summary of 64 Bit Client Installation.















Step 8: If you have already installed 64 Bit Client it will give an Error/Warning saying the Service already exist. Just Click Continue because this service is already created by the 64 Bit Client Installation.














Step 9: For Listener just click Perform typical configuration and click Next.

Step 10: Finally it will finish and just Click End/Finish and the Oracle Client is Installed successfully.



Step 11: To complete either the 32-bit or 64-bit Oracle client installation processes make the following registry changes:
1. For both 32-bit and 64-bit installations, open up REGEDIT and make the following registry changes:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC\MTxOCI
OracleOciLib contains the value oci.dll
OracleSqlLib contains the value orasql11.dll (old value is SQLLib80.dll)
OracleXaLib contains the value oraclient11.dll (old value is xa80.dll)
2. For 64-bit installations:
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\MSDTC\MTxOCI
OracleOciLib contains the value oci.dll
OracleSqlLib contains the value orasql11.dll (old value is SQLLib80.dll)
OracleXaLib contains the value oraclient11.dll (old value is xa80.dll)













Step 12: Configure the Oracle clients (both 32 and 64 bits) by editing  the files tnsnames.ora and sqlnet.ora

Both files should be placed in the following Folders
D:\oracle\product\11.1.0\client_64\network\admin
D:\oracle\product\11.1.0\client_32\network\admin

Tnsnames.ora file should contain similar connection string as follow
AB10P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.12)(PORT = 1527))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = AB10P .test.com)
    )
  )

Step 13: Use the "Oracle provider for OLE DB" from SSIS, don't use the "Microsoft Provider for Oracle" because a 64 bit version of it does not exist.

Sample Connectionstring : 
Provider=OraOLEDB.Oracle;Password=testPassword;Persist Security Info=True;User ID=User1;Data Source=AB10P
Tableprefix: Test1"."
Provider: Oracle Provider for  OLE DB